SSIS - Paso a paso como ejecutar un paquete SSIS desde Reporting Services

Queridos colegas les comparto mi experiencia para ayudar a hacer del mundo un lugar mejor; tenía un reporte (Reporting services) que funcionaba bien, el usuario ingresaba y ejecutaba el reporte, luego lo exportaba a excel; pero después de un año por la cantidad de datos en la base de datos el reporte se demoraba demasiado; entonces el usuario lo ejecutaba desde el internet explorer y se iba a hacer otras tareas cuando regresaba y trataba de exportar a excel el reporte ya generado, la ventana de internet explorer le decia Error de expiración de sesión y no se podía exportar.

Mi solución:  Ejecutar de forma asincrona el reporte y que luego de generado, informe al usuario; técnicamente debo dividir el problema en 2: una parte ejecutada de forma sincrona (la parte que visualiza el usuario al ingresar los parámetros) y otra parte de forma asincrona (la que crea el reporte y lo exporta). 

Tengo un reporte que solo sirve para pedir los parámetros al usuario (llamado "parametros_ventas_x_producto"), y que manda a ejecutar un paquete SSIS, luego saca un mensaje en un textbox informando que apenas termine de generarse el reporte, se enviará un correo al usuario. El primer paquete SSIS  (llamado "SSRS_executeSSIS") lo que hace es llamar otro paquete (llamado "SSIS_executeReporte") por medio del componente "Execute Process Task" y luego con un componente "DataReader Destination" le informa al primer reporte de parametros que todo esta OK.

El segundo paquete SSIS ("SSIS_executeReporte") ejecuta a través de código Visual Basic el reporte original (llamado "ventas_x_producto") que es muy demorado y lo guardar como un archivo de excel en el disco duro; por último envía el correo al usuario informando que ya se generó el reporte y la ubicación de donde lo puede tomar.

Para complicar un poco las cosas, yo quiero hacer todo este proceso de forma genérica es decir, que los dos paquetes SSIS me sirvan para ejecutar cualquier reporte, sin necesidad de cambiar nada en su codigo; asi que por favor presten especial atención al manejo que le doy a los parámetros del reporte.

Estoy trabajando con la version 2008 de los productos Microsoft, y todo está instalado en el mismo server (esto es importante pues al ejecutar el paquete SSIS se debe colocar la ruta en el DD, por lo que no podría ser una ruta en Red).

:: Aquí les dejo el paso a paso :::


1. Configura tu reporting services para que acepte SSIS como fuente de datos. 

Esta parte la expliqué en un post anterior en el punto 1, SSIS - Paso a paso package como datasource para Reporting Services

2. Creemos el reporte que pide los parámetros al usuario.

Mi reporte original demorado "ventas_x_producto" pide 2 parámetros: tipo_reporte, fecha_ini y fecha_final así que en este reporte de parámetros también debo crearlos, del mismo tipo, con los mismos valores por defecto; adicionalmente crearé los demás parámetros que muestra la imagen:

Explicaré brevemente estos parámetros adicionales, que son ocultos y no son mostrados al usuario:
- SSRS_excel_name : nombre del archivo excel que se creará, en mi caso vtasXPdto
- SSRS_reportFolder: nombre de la carpeta que contiene el reporte original en el reportServer, para mi es /desarrollo/ debe tener los slash al inicio y final.
- SSRS_reportName: nombre del reporte demorado a ejecutar, el mio es ventas_por_producto
- SSRS_reportServer: url del servidor de reportes; en mi caso es http://reporteador.miempresa.com/ReportServer/
- SSRS_secureWS_dominio: el dominio para configurar el web services que se ejecutará: MIEMPRESA
- SSRS_secureWS_user: la cuenta del dominio para configurar el web services: miUsuario
- SSRS_secureWS_password: el password de la cuenta del dominio para configurar el web services: Pa$$word
- SSRS_webServices: url del webService  http://reporteador.miempresa.com/ReportServer/ReportService.asmx
- SSRS_ nombre_ETL: nombre del paquete SSIS a ejecutar, en mi caso SSRS_executeSSIS.dtsx observa que el paquete está guardado en el disco duro del servidor como un archivo .dtsx; es necesario que sea de este tipo.

Ahora configuremos la magia de este reporte; crea un datasource y un dataset como se explica en el paso 2 del post SSIS - Paso a paso package como datasource para Reporting Services Mi datasource lo llamé SSIS_DGBISERVER y el dataset DataSet1, el cual tiene un único campo llamado Resultado, el cual es tomado del DataReaderDestination del paquete SSIS configurado en el connection string del datasource.

Como dije antes, quiero que todo este trabajo me quede genérico para ejecutar cualquier reporte, asi que los parametros propios del reporte, aquellos que no empiezan por SSRS_ los voy a concatenar y pasar como parámetro para la variable SSRS_parametros_etl del paquete SSIS llamado en el datasource, observa la imagen:

Esta variable (SSRS_parametros_etl) es donde iran concatenados todos los parámetros propios del reporte, con su tipo de datos, y su valor; observa que debe ir entre comillas dobles y de la forma:

parametro1:int:valor1|parametro2:varchar:valor2

Cada parametro es separado por | (pipe) y cada valor es separado por : (dos puntos).  Por ultimo concatena los otros parámetros SSRS_ que tambien tienen una variable correspondiente en el paquete.

Para terminar crea en este reporte un textbox con el mensaje: "Su reporte está siendo generado, apenas termine se le enviará un correo informando." y guarda el reporte como "parametros_ventas_x_producto".

3.  Creemos el paquete SSIS que ejecuta de forma asincrona otro paquete
Crea un paquete con las siguientes variables:

Observa que son las mismas del reporte en el punto 2, ahora vamos a crear un componente "Execute Process Task" llamado Process_ejecutar_SSIS, unido a un "Data Flow Task".

Vamos a configurar el Execute Process Task, así: como ejecutable le voy a colocar el comando cmd y una vez estando en el shell, es que voy a ejecutar la utilidad dtexec para ejecutar el paquete SSIS que me creará el reporte.  Mi línea de argumentos es:
/c dtexec /F C:\IS_desarrollo\IS_desarrollo\SSIS_execSSRS.dtsx /Set \Package.Variables[User::V_PARAMETROS].Properties[Value];"fecha_ini:date:6/1/2010

fecha_fin:date:6/1/2010
tipo_reporte:int:1" > C:\SSRS_reportes_usuarios\SSRS_ejecucion_resultado.txt 
 















La opción /c es para decirle al comando cmd que ejecute el comando siguiente (el dtexec), y no cierre el proceso antes.


dtexec es la forma de ejecutar paquetes SSIS desde la linea de comandos, puedes leer al respecto en el sitio de microsoft, con la opcion /F le indico la ruta y paquete.

Luego seteo cada una de las variables que me interesan con /Set y por ultimo le indico que la salida del proceso la deje en la ubicacion y archivo SSRS_ejecucion_resultado.txt

Pero como los argumentos dependen de las variables modificadas en tiempo de ejecución, toca usar una expression para ellos; no entraré en detalle, queda algo como:

"/c dtexec /F "+ @[User::SSRS_nombre_ETL] +" /Set \\Package.Variables[User::V_PARAMETROS].Properties[Value];"+ @[User::SSRS_parametros_etl] +" > C:\\SSRS_reportes_usuarios\\SSRS_ejecucion_resultado.txt"

Ahora debemos agregar un Data Flow Task y configurarlo como se explica en el paso 2 de SSIS - Paso a paso package como datasource para Reporting Services

:: NOTA :: lo que me permite este paquete es ejecutar otro paquete de forma asincrona; es decir este paquete termina con exito aun cuando el segundo paquete que consume el web services, y devuelve el control al reporte de parametros, de esta forma no hay que esperar hasta que el reporte demorado termina de generarse.

4. Creemos el paquete SSIS que crea el reporte demorado de forma síncrona.


Crea las variables, igual que en el paquete del paso 3

Crea dos componentes "Script Task" y un "Send Mail Task"

El primer componente no lo explicaré es simplemente setear variables para el envio de correo; el segundo componente es donde se consume el Web Service a traves de codigo Visual Basic.

 Public Sub Main()

'
Try
'Los parametros de cada reporte se encuentan en un string de la forma
'param1:tipo1:valor1| param2:tipo2:valor2| param3:tipo3:valor3

'Parametros del reporte
Dim parameters() As ParameterValue
Dim strTmp() As String = Split(Dts.Variables("V_PARAMETROS").Value.ToString, "| ")


For i As Integer = 0 To strTmp.Length - 1
     Dim strTmp2() As String = Split(strTmp(i), ":")


     For j As Integer = 0 To strTmp2.Length - 1
          'MsgBox("i " & i.ToString & " j " & j.ToString)
          ReDim Preserve parameters(i)
          parameters(i) = New ParameterValue
          parameters(i).Name = strTmp2(0)
          parameters(i).Value = strTmp2(2)
        
     Next
Next 

'Creo el componente de reporting
Dim rs As New ReportingService()

rs.Credentials = System.Net.CredentialCache.DefaultCredentials


' The first argument tells Render where to find the report, the second
' argument tells it what extension to tuse to render the report.


Dim reportPath As String = Dts.Variables("SSRS_reportFolder").Value.ToString + Dts.Variables("SSRS_reportName").Value.ToString


Dim reportFormat As String = "XLTemplate"

' call method below...>

Call ReportingServices_ExportReport(Dts.Variables("SSRS_reportName").Value.ToString, parameters, "EXCEL", Dts.Variables("SSRS_excelName").Value.ToString)


Dts.TaskResult = ScriptResults.Success


Catch ex As Exception


     MsgBox("Error " + ex.ToString)


Finally
    GC.Collect()
End Try

Dts.TaskResult = ScriptResults.Success
End Sub

'--------------------------------------------------------------------------------------

' call to reporting services - and output report as PDF / EXCEL
'--------------------------------------------------------------------------------------


Public Function ReportingServices_ExportReport(ByVal i_strReportName As String, _
                                                                                                  ByVal i_arrParamValues As ParameterValue(), _
                                                                                                  ByVal i_strOutputFormat As String, _
                                                                                                  ByVal i_strOutputFileName As String) As Boolean

Dim objReportingService As ReportingService
Dim objCredential As Net.NetworkCredential
Dim results As Byte()
Dim fs As System.IO.FileStream

'REQUIRED for method call - but not used
Dim strDeviceInfo As String = Nothing 'XML-string - extra info about FORMAT
Dim strHistoryId As String = Nothing
Dim ShowHideToggle As String = Nothing
Dim RSCredentials As DataSourceCredentials() = Nothing

'OUTPUT variables
Dim o_RSParamsUsed As ParameterValue()
Dim o_RSWarnings As Warning()
Dim o_strEncoding As String
Dim o_strMimeType As String
Dim o_arrStreamIds As String()
Dim strReportingServicesUser As String
Dim strReportingServicesWS As String
Dim strReportingServicesPassword As String
Dim strReportingServicesReports As String
Dim strReportingServicesReportPath As String

Try
'-------------------------------------------------------------------
'location of the reporting services "web-service"
strReportingServicesWS = Dts.Variables("SSRS_webServices").Value.ToString


objReportingService = New ReportingService ' << add reference to ReportingService
objReportingService.Url = strReportingServicesWS
objReportingService.Timeout = 14400000 'allow reports to run up-to 14400 seconds = 240 mins (4 hours)


'-------------------------------------------------------------------
'establish security to call the web service method
objCredential = New Net.NetworkCredential(Dts.Variables("SSRS_secureWS_user").Value.ToString, Dts.Variables("SSRS_secureWS_password").Value.ToString, Dts.Variables("SSRS_secureWS_dominio").Value.ToString)


objReportingService.Credentials = objCredential
'get the location of "Reports" - ie. folder for reports
strReportingServicesReports = Dts.Variables("SSRS_reportServer").Value.ToString
trReportingServicesReportPath = Dts.Variables("SSRS_reportFolder").Value.ToString

results = objReportingService.Render(strReportingServicesReportPath & i_strReportName, _
                                                                              i_strOutputFormat, _
                                                                              strHistoryId, _
                                                                              strDeviceInfo, _
                                                                              i_arrParamValues, _
                                                                             RSCredentials, _
                                                                            ShowHideToggle, _
                                                                            o_strEncoding, _
                                                                            o_strMimeType, _
                                                                            o_RSParamsUsed, _
                                                                            o_RSWarnings, _
                                                                            o_arrStreamIds)


'-------------------------------------------------------------------
'now use the byte array returned - to reconstruct the file
'If Dir(i_strOutputFileName) <> constBlank Then Kill(i_strOutputFileName)


If Dir(i_strOutputFileName) <> "" Then Kill(i_strOutputFileName)
fs = System.IO.File.OpenWrite(i_strOutputFileName)
fs.Write(results, 0, results.Length)
fs.Close()


'-------------------------------------------------------------------
objReportingService.Dispose()
objReportingService = Nothing
GC.Collect()
Return True

Catch ex As Exception
Throw ex
Finally
     objReportingService = Nothing
     objCredential = Nothing
     results = Nothing
     fs = Nothing
     GC.Collect()


End Try
End Function

Hay una clase llamada ReportService que hace el resto.  Aqui les dejo el archivo .vb para que lo descarguen

Debes agregar una referencia a esta clase en el Script, como muestra la imagen. Si tienes problemas para hacerlo lee mi post No puedo agregar una referencia en BIDS 2008

Para finalizar configura el componente de envio de correos y listo !!!!


Yo se que para un novato todo esto parece muy enredado y confuso por la cantidad de teoría de SSRS y SSIS que debe conocerse de antemano, pero creanme vale la pena leer al respecto. Si por el contrario a ti como a mi a ti tambien te sirvió bastante, deja un mensaje ;) me dá ánimo para seguir escribiendo posts.

Posts que me sirvieron de ayuda:
http://www.mssqltips.com/tip.asp?tip=2005


Junio 23 del 2010

:(  Tenia todo esto programado en 2005 y ahora que lo estoy migrando a 2008 me doy cuenta que el web services ha cambiado y que hay un problema cuando se hace deploy al server de produccion cuando encuentre soluciones las estare posteando :(