Alternativas para ejecutar paquetes de Integration Services desde nuestras aplicaciones..

Comparto un pequeño análisis realizado para uno de nuestros proyectos en el cual se requiere ejecutar paquete de SQL Server Integration Services 2005 desde la aplicación (.net 2.0). El requerimiento más fuerte viene por el lado de poder lanzar paquetes de forma asincrónica y poder cancelar su ejecución en cualquier momento.

 

A continuación el análisis:

 

REQUERIMIENTOS

1. Ejecutar paquetes SSIS asincronicamente
   
2. Conocer el porcentaje de avance de un paquete (sino, alternativa, consultar la BD directamente para saber cuanto proceso)
   
3. Cancelar ejecucion de paquetes
   
4. Evitar ejecucion serial (workaround: se validaria desde la logica de negocios)
   

 

 
 

ALTERNATIVAS

Para ello, las alternativas que se evaluaron como mecanismos de ejecución fueron:

• APIs de SSIS [SSIS Object Model]
  
• Utilitario DTEXEC.EXE [línea de comando]
  
• SQLAgent Job
  

 

CONCLUSIONES

 Luego de investigar, refrescar información y realizar algunas POCs, concretamente y en resumen, las conclusiones fueron las siguientes:

 

Dados los requerimientos fuertes de lanzar la ejecución de paquetes de forma asincrónica y poder detener en cualquier momento su ejecución, la opción que permite cubrir estos requerimientos es el uso de SQL Agent Jobs.

 

Esto además trae aparejado el beneficio que solo una instancia del job puede estar corriendo al mismo tiempo [ejecución serial], se aprovecha toda la infraestructura del SQL y la ejecución de los paquetes se realiza out-of-process de la aplicación. Además se le pueden setear credenciales especificas y se pueden setear variables/datasources desde su misma definición.

 

También facilita el mantenimiento, backup, etc.

 

El requerimiento aquí es que el SQLServer engine y el Integration Services estén en el mismo servidor, lo cual, es un escenario tradicional, aunque recomiendo volver a verificarlo con el cliente y argumentarlo.

 

La única desventaja [no invalidante para este escenario] es para obtener la data de progreso del job, que si bien hay un SP que la devuelve [sp_help_job], el mismo devuelve información genérica, del estilo "está corriendo o no", pero NO un porcentaje de avance ni ninguna data relacionada al job.

Tampoco veo que la solución venga por Logging de SSIS, al menos como viene por default, ya que la información que loguea es básica y NO reporta un porcentaje de avance sino cambios de estado [ej."validating, pre-execute, post-execute"]

 
 

En contrapartida, las otras dos alternativas son utilizar línea de comando [que requiere tener SSIS instalado en el mismo equipo] o directamente usar las APIs. Descartando la opción de línea de comando [DTEXEC.exe] dado que ni es prolija ni aplica y analizando la opción de lanzar paquetes a través de las APIs de SSIS, NO la considero apropiada para este escenario por lo siguiente:

• La forma de ejecución seria sincrónica [no encontré forma de hacerlo asincrónico]
  
• Obligaría que el mismo server de WebServices tenga instalado Integration Services y además, los recursos se consumirían desde el mismo server.
  
• Si bien me puedo suscribir a eventos y en particular a un evento que se dispara mientras avanza el progreso [OnProgress] y en teoría en un parámetro tiene el porcentaje de avance, la verdad que a mí NO me funciono bien, o me daba 0% o 100% pero en el medio no me registro nada.
  

Por lo tanto, NO considero que usar las APIs sea el camino.

 

ANALIS DETALLADO

A continuación, un drill-down en el análisis de los puntos que hacen a esta conclusión:

• Alternativas
  
  • SSIS Object Model
    
    • Ventajas
      
      • Se pueden capturar eventos durante la ejecucion
        
      • Se puede cancelar el proceso seteando CancelEvent = true
        
      • El modelo permite de forma facil setear variables
        
    • Desventajas
      
      • Donde se corra tiene que tener SSIS instalado
        
      • Solo .Net 2.0
        
      • Corre en el mismo proceso, levanta memoria del AppPool
        
      • No se pasa contexto de seguridad en caso de tener impersonation.
        

    Mas info:
    

    http://msdn2.microsoft.com/en-us/library/ms403355.aspx
    

     
     

  • DTEXEC.EXE [linea de comando]
    
    • Ventajas
      
      • Ejecucion out of process
        
      • .Net 1.x/2.0/3.x
        
      • Los parámetros se le pueden pasar en la misma linea de comando
        
    • Desventajas
      
      • Donde se corra tiene que tener SSIS instalado
        
      • Dificil obtener informacion del progreso del paquete [salvo utilizando SSIS Logging]
        
      • Requiere la llamada a un proceso de linea de comando
        
      • No se pasa contexto de seguridad en caso de tener impersonation. Hay que pasarle credenciales al Process.Start
        

         
         

  • SQLAgent Job
    
    • Ventajas
      
      • .Net 1.x/2.0/3.x
        
      • Ejecucion out of process
        
      • Ejecucion asincronica, el sp_start_job devuelve si se lanzo o no el job, NO espera a que finalice
        
      • Se le puede configurar la cuenta proxy con la que corre
        
      • La ejecucion es serial (no permite q corra un nuevo job hasta q no termine el anterior)
        
      • Aprovecha la infraestructura de SQLServer
        
      • Se puede detener el job llamando a sp_stop_job
        
    • Desventajas
      
      • Para pasarle parametros hay q modificar la definicion del job en la BD
        
      • El progreso del paquete como tal no se puede saber , salvo utilizando SSIS logging.
        
        • sp_help_job  solo brinda info generica, no del paquete
          
        • sp_help_jobhistory retorna informacion generica del job una vez finalizado
          

      Mas info:
      

      http://doc.ddart.net/mssql/sql70/sp_help_27.htm
      

      http://msdn2.microsoft.com/en-us/library/ms188025.aspx
      

  Mas info:
  

  http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx
  

   
   

• Analisis de requerimientos y alternativas
  

Requerimientos	SSIS Object Model	DTEXEC.EXE	SQLAgent Job
Ejecutar paquetes SSIS asincronicamente	NO	?	OK
Conocer el porcentaje de avance de un paquete	OK [no funciona bien]	NO	NO
Cancelar ejecucion de paquetes	OK	NO	OK

 
 

 

CODIGO DE EJEMPLO

Tengo una solución con código fuente que incluye:

• Un paquete de SSIS de ejemplo [toma datos de la Northwind y los baja a un separado por comas en disco + provoca un delay de xx segundos para simular actividad + envía un mail al final]. Para el subject de este mail como para los segundos del delay, utiliza variables que se le pueden setear luego desde afuera, configurables.
  
• Una aplicación Winform que muestra [en forma rustica, solo a fines de la POC]
  
  • Como lanzar un paquete a través de las APIs + suscribirse a los eventos
    
  • Como lanzar un paquete a través de un job
    
  • Como conocer el status del progreso del job [información genérica]
    
  • Como stopear un job
    
  • Como conocer el historial de resultados de un job [información genérica]
    

 

Esta solución la hice en 2008 [perdón pero no tengo cambio, no tengo versiones anteriores ] por lo cual se les puede complicar para abrirla como tal. De todas formas si fuera el caso, igualmente van a poder ver las clases y el paquete como tal no tiene misterio.

El que quiera este código simplemente me lo pide.

Nuevamente insisto, el código es a fines de ejemplo, le falta refinamiento [ej. Manejo de excepciones]

 

A continuación los puntos del código fuente que entiendo aportan más valor, que usaríamos:

 

• Paquete:
  

  
  

  [Nota] En la ScriptTask se encuentra el Delay, en un script en c#.
  

  
   

  DataFlow:
  

  
  

• Aplicación Winform
  

  
  

  
   

• Codigo para lanzar el Job:
  

        public
int LaunchJob(string jobName)

        {

              SqlConnection jobConnection;

              SqlCommand jobCommand;

              SqlParameter jobReturnValue;

              SqlParameter jobParameter;

              int jobResult;

 

              jobConnection = new
SqlConnection("Data Source=(local);Initial Catalog=msdb;Integrated Security=SSPI");

              jobCommand = new
SqlCommand("sp_start_job", jobConnection);

              jobCommand.CommandType = CommandType.StoredProcedure;

 

              jobReturnValue = new
SqlParameter("@RETURN_VALUE", SqlDbType.Int);

              jobReturnValue.Direction = ParameterDirection.ReturnValue;

              jobCommand.Parameters.Add(jobReturnValue);

 

              jobParameter = new
SqlParameter("@job_name", SqlDbType.VarChar);

              jobParameter.Direction = ParameterDirection.Input;

              jobCommand.Parameters.Add(jobParameter);

              jobParameter.Value = jobName;

 

              jobConnection.Open();

              jobCommand.ExecuteNonQuery();

              jobResult = (Int32)jobCommand.Parameters["@RETURN_VALUE"].Value;

              jobConnection.Close();

 

              switch (jobResult)

              {

                case 0:

                  textBox1.Text = "SQL Server Agent job, RunSISSPackage, started successfully.";

                  break;

                default:

                  textBox1.Text = "SQL Server Agent job, RunSISSPackage, failed to start.";

                  break;

              }

              return jobResult;

        }

 

• Codigo para Stopear el Job:
  

        public
int StopJob(string jobName)

        {

            SqlConnection jobConnection;

            SqlCommand jobCommand;

            SqlParameter jobReturnValue;

            SqlParameter jobParameter;

            int jobResult;

 

            jobConnection = new
SqlConnection("Data Source=(local);Initial Catalog=msdb;Integrated Security=SSPI");

            jobCommand = new
SqlCommand("sp_stop_job", jobConnection);

            jobCommand.CommandType = CommandType.StoredProcedure;

 

            jobReturnValue = new
SqlParameter("@RETURN_VALUE", SqlDbType.Int);

            jobReturnValue.Direction = ParameterDirection.ReturnValue;

            jobCommand.Parameters.Add(jobReturnValue);

 

            jobParameter = new
SqlParameter("@job_name", SqlDbType.VarChar);

            jobParameter.Direction = ParameterDirection.Input;

            jobCommand.Parameters.Add(jobParameter);

            jobParameter.Value = jobName;

 

            jobConnection.Open();

            jobCommand.ExecuteNonQuery();

            jobResult = (Int32)jobCommand.Parameters["@RETURN_VALUE"].Value;

            jobConnection.Close();

 

            switch (jobResult)

            {

                case 0:

                    textBox1.Text = "SQL Server Agent job, RunSISSPackage, stopped successfully.";

                    break;

                default:

                    textBox1.Text = "SQL Server Agent job, RunSISSPackage, failed to stop.";

                    break;

            }

            return jobResult;

        }

 

• Codigo para saber el status:
  

        public
string GetJobStatus(string jobName)

        {

            SqlConnection jobConnection;

            SqlCommand jobCommand;

            SqlParameter jobReturnValue;

            SqlParameter jobParameter;

            int jobResult;

 

            jobConnection = new
SqlConnection("Data Source=(local);Initial Catalog=msdb;Integrated Security=SSPI");

            jobCommand = new
SqlCommand("sp_help_job", jobConnection);

            jobCommand.CommandType = CommandType.StoredProcedure;

 

            jobReturnValue = new
SqlParameter("@RETURN_VALUE", SqlDbType.Int);

            jobReturnValue.Direction = ParameterDirection.ReturnValue;

            jobCommand.Parameters.Add(jobReturnValue);

 

            jobParameter = new
SqlParameter("@job_name", SqlDbType.VarChar);

            jobParameter.Direction = ParameterDirection.Input;

            jobCommand.Parameters.Add(jobParameter);

            jobParameter.Value = jobName;

 

            jobConnection.Open();

            SqlDataAdapter da = new
SqlDataAdapter(jobCommand);

            DataSet dsResult = new
DataSet();

            da.Fill(dsResult);

            jobResult = (Int32)jobCommand.Parameters["@RETURN_VALUE"].Value;

            jobConnection.Close();

 

            switch (jobResult)

            {

                case 0:

                    textBox1.Text = "SQL Server Agent job, RunSISSPackage, started successfully.";

                    break;

                default:

                    textBox1.Text = "SQL Server Agent job, RunSISSPackage, failed to start.";

                    break;

            }

            return dsResult.GetXml();

        }

 

• Codigo para obtener la historia:
  

        public
string GetJobResult(string jobName)

        {

            SqlConnection jobConnection;

            SqlCommand jobCommand;

            SqlParameter jobReturnValue;

            SqlParameter jobParameter;

            int jobResult;

 

            jobConnection = new
SqlConnection("Data Source=(local);Initial Catalog=msdb;Integrated Security=SSPI");

            jobCommand = new
SqlCommand("sp_help_jobhistory", jobConnection);

            jobCommand.CommandType = CommandType.StoredProcedure;

 

            jobReturnValue = new
SqlParameter("@RETURN_VALUE", SqlDbType.Int);

            jobReturnValue.Direction = ParameterDirection.ReturnValue;

            jobCommand.Parameters.Add(jobReturnValue);

 

            jobParameter = new
SqlParameter("@job_name", SqlDbType.VarChar);

            jobParameter.Direction = ParameterDirection.Input;

            jobCommand.Parameters.Add(jobParameter);

            jobParameter.Value = jobName;

 

            jobConnection.Open();

            SqlDataAdapter da = new
SqlDataAdapter(jobCommand);

            DataSet dsResult = new
DataSet();

            da.Fill(dsResult);

            jobResult = (Int32)jobCommand.Parameters["@RETURN_VALUE"].Value;

            jobConnection.Close();

 

            switch (jobResult)

            {

                case 0:

                    textBox1.Text = "SQL Server Agent job, RunSISSPackage, started successfully.";

                    break;

                default:

                    textBox1.Text = "SQL Server Agent job, RunSISSPackage, failed to start.";

                    break;

            }

            return dsResult.GetXml();

        }

 

 

Bueno espero haber contribuido y desde ya el que pueda complementar y mejorar estas conclusiones, notable!.

 

Gracias.

PP