Blog de Eduardo Sojo

Se pueden crear paquetes de SQL Server Integration Services (SSIS) programáticamente con .NET, haciendo uso de las librerías que incluye SQL Server 2008. En este post se explica cómo crear Execute Package Task y Execute Process Task.

Serie Creando paquetes de SSIS con .NET

• Agregando elementos básicos
• Creando data flow Task y elementos internos
• Creando SQL Task y File System Task
• Creando Execute Package Task y Execute Process Task

Continuando esta serie y partiendo del post inicial donde agregamos los elementos basicos para crear un paquete de SSIS con C#/.NET (puedes guiarte con el primer post de esta serie – Agregando elementos básicos), vamos a agregar un Execute Package Task y un Execute Process Task.

Execute Package Task

Este componente nos permite ejecutar un paquete de SSIS (.dtsx) desde otro paquete. Este tipo de componente nos puede ayudar para orquestar la ejecución de muchas paquetes y combinarlo con otras operaciones.

Para crear este componente utilizaremos el siguiente código:

//Para este componente se necesita un file connection, en este ejemplo
//se parte de que el componente ya está creado y se llama dtsx.Origen
//En posts anteriores de esta serie se explica cómo crearlo por código
Executable ExePKGexe = pkg.Executables.Add("STOCK:ExecutePackageTask");
TaskHost exePKG = ExePKGexe as TaskHost;
exePKG.Name = "Ejecutar un DTSX";
exePKG.Properties["Connection"].SetValue(exePKG, "dtsx.Origen");

El código es bastante corto ya que sólo debemos definir la conexión que usará este componente debido a que el objeto de conexión es el que contiene la información del paquete a ejecutar.

Execute Process Task

Este componente nos permite ejecutar algún proceso con o sin parámetros desde un paquete de SSIS. Para este ejemplo vamos a ejecutar Powershell.exe con algunos parámetros. Si lo construimos gráficamente sería algo como:

Para crear este componente utilizaremos el siguiente código:

Executable exeProTaskexe = pkg.Executables.Add("STOCK:ExecuteProcessTask");
TaskHost exeProTask = exeProTaskexe as TaskHost;
exeProTask.Name = "Ejecutar Powershell con parámetros";
exeProTask.Properties["Executable"].SetValue(exeProTask, @"C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe");
exeProTask.Properties["Arguments"].SetValue(exeProTask, "-command \"get-process;return 0;\"");
exeProTask.Properties["SuccessValue"].SetValue(exeProTask, 0);
exeProTask.Properties["WindowStyle"].SetValue(exeProTask, 0);

Seguiré indagando en cómo crear más componentes para un paquete SSIS desde C#/.NET.

Nos leemos,

Se pueden crear paquetes de SQL Server Integration Services (SSIS) programáticamente con .NET, haciendo uso de las librerías que incluye SQL Server 2008. En este post se explica cómo crear SQL Task y File System Task.

Serie Creando paquetes de SSIS con .NET

• Agregando elementos básicos
• Creando data flow Task y elementos internos
• Creando SQL Task y File System Task
• Creando Execute Package Task y Execute Process Task

Continuando esta serie y partiendo del post inicial donde agregamos los elementos basicos para crear un paquete de SSIS con C#/.NET (puedes guiarte con el primer post de esta serie – Agregando elementos básicos), vamos a agregar un SQL Task y un File System Task.

SQL Task

Este componente nos permite ejecutar sentencias SQL con ciertas caracteristicas y realizar algunas acciones con el resultado. Cuando lo creamos de forma grafica, vemos un formulario como el siguiente:

Para crear este componente utilizaremos el siguiente codigo:

Executable SQLTaskExe = pkg.Executables.Add("STOCK:SQLTask");

TaskHost SQLTaskHost = SQLTaskExe as TaskHost;
SQLTaskHost.Name = "Vaciado de tabla";
SQLTaskHost.Properties["Connection"].SetValue(SQLTaskHost, pkg.Connections["SQL.Destino"].ID);
SQLTaskHost.Properties["SqlStatementSource"].SetValue(SQLTaskHost, "truncate table MyTable");

File System Task

Este componente nos permite realizar operaciones con archivos y directorios desde SSIS. Con este componente podemos crear, copiar y mover archivos/directorios. Si configuramos el componente en el entorno gráfico veríamos algo como esto:

Para crear este componente utilizaremos el siguiente codigo:

Executable FSTexe = pkg.Executables.Add("STOCK:FileSystemTask");
TaskHost FST = FSTexe as TaskHost;
FST.Name = "Mover archivo XLSX";
FST.Properties["Destination"].SetValue(FST, "xlsx.Destino");
FST.Properties["Source"].SetValue(FST, "xlsx.Origen");
FST.Properties["Description"].SetValue(FST, "Componente para copiar archivo");
FST.Properties["OperationName"].SetValue(FST, "CopyFile");
FST.Properties["OverwriteDestinationFile"].SetValue(FST, true);

En la siguiente entrega estaré mostrando un poco cómo crear Execute Package Task y Execute Process Task.

Nos leemos,

Se pueden crear paquetes de SQL Server Integration Services (SSIS) programáticamente con .NET, haciendo uso de las librerías que incluye SQL Server 2008. En este post se explica cómo crear data flow task y sus elementos internos.

Serie Creando paquetes de SSIS con .NET

• Agregando elementos básicos
• Creando data flow Task y elementos internos
• Creando SQL Task y File System Task
• Creando Execute Package Task y Execute Process Task

Una vez que agregamos los elementos básicos como creación del paquete y los objetos de conexión, podemos seguir agregando nuevos elementos al paquete que estamos creando usando C# y .NET (puedes guiarte con el primer post de esta serie - Agregando elementos básicos)

Tomaremos como referencia los elementos creados en el primer post de esta serie, para seguir agregándole objetos al paquete que se quiere crear. Para este post crearemos un data flow y algunos elementos internos, tomando como ejemplo un escenario en donde tomaremos data de un archivo de texto y la pasaremos a una tabla en SQL Server.

Creando Dataflow Task

El dataflow task es el objeto que contendrá todo los flujos de trabajo para trabajar con los datos. Para crear el objeto Dataflow Task utilizaremos el siguiente código:

Executable DataFlowExe = pkg.Executables.Add("STOCK:PipelineTask");
TaskHost thMainPipe = DataFlowExe as TaskHost;
thMainPipe.Name = "Carga de tabla";
MainPipe dfTask = thMainPipe.InnerObject as MainPipe;
PrecedenceConstraint constraint = pkg.PrecedenceConstraints.Add(SQLTaskExe, DataFlowExe);

Ya teniendo creado el dataflow task, podemos agregar los objetos internos que conforman el flujo de trabajo a construir.

Creando Flat File Source

Este objeto es que nos permite conectarnos con el origen de los datos para extraer los registros, este origen de datos debe ser un objeto de conexión de tipo Flat File en el cual se define el comportamiento de la información dentro del archivo.

Para crear este tipo de objetos usamos el siguiente código:

IDTSComponentMetaData100 ffSource = dfTask.ComponentMetaDataCollection.New();
ffSource.ComponentClassID = "DTSAdapter.FlatFileSource.2";
ffSource.Name = "FlatFileSource";
ffSource.Description = "Flat file source";
CManagedComponentWrapper ffSrcComponent = ffSource.Instantiate();
ffSrcComponent.ProvideComponentProperties();
ffSrcComponent.SetComponentProperty("RetainNulls", true);

//Asociamos al objeto de conexión
if (ffSource.RuntimeConnectionCollection.Count > 0)
{
ffSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(pkg.Connections["TXT.Archivo"]);
ffSource.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["TXT.Archivo"].ID;
}
ffSrcComponent.AcquireConnections(null);
ffSrcComponent.ReinitializeMetaData();
ffSrcComponent.ReleaseConnections();

//Opcional, podemos definir el comportamiento de los campos al momento de ocurrir un error o un truncamiento de los datos
for (int i = 0; i < ffSource.OutputCollection[0].OutputColumnCollection.Count; i++)
{
ffSource.OutputCollection[0].OutputColumnCollection[i].ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow;
ffSource.OutputCollection[0].OutputColumnCollection[i].TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow;
}

Creando SQL Destination

El siguiente paso es crear el destino de los datos, para este caso es una tabla de SQL Server. Para crear este objeto y definir su comportamiento usamos el siguiente código:

IDTSComponentMetaData100 SQLDestination = dfTask.ComponentMetaDataCollection.New();
SQLDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.2";
SQLDestination.Name = "OLEDBDestination";
SQLDestination.Description = "SQL destination";
CManagedComponentWrapper SQLDestComponent = SQLDestination.Instantiate();
SQLDestComponent.ProvideComponentProperties();

//Asociamos con el objeto de conexión de SQL Server
if (SQLDestination.RuntimeConnectionCollection.Count > 0)
{
SQLDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(pkg.Connections["OLEDB"]);
SQLDestination.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["OLEDB"].ID;
}

//Configuramos algunas propiedades del objeto
SQLDestComponent.SetComponentProperty("AccessMode", 3); //Equivale a: Table or view - fast load
SQLDestComponent.SetComponentProperty("OpenRowset", "NombreTabla");
SQLDestComponent.SetComponentProperty("FastLoadOptions", "TABLOCK");
SQLDestComponent.AcquireConnections(null);

//Reinicializamos la metadata del objeto
SQLDestComponent.ReinitializeMetaData();

Conexión de ambos objetos

Finalmente debemos unir ambos objetos, o mejor dicho, trazar por código la conexión de los objetos mapeando la salida de uno con la entrada del otro.

Para realizar esta actividad aplicamos el siguiente código:

//Agregamos el conector
IDTSPath100 path = dfTask.PathCollection.New();
path.AttachPathAndPropagateNotifications(ffSource.OutputCollection[0], SQLDestination.InputCollection[0]);
// Obtenemos el input por defecto del objeto.
IDTSInput100 input = SQLDestination.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();

foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
// Llamamos al método SetUsageType del objeto destino para agregar cada virtual input column como un input column.
SQLDestComponent.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
}
foreach (IDTSInputColumn100 inColumn in SQLDestination.InputCollection[0].InputColumnCollection)
{
// creamos el mapeo
IDTSExternalMetadataColumn100 exColumn = SQLDestination.InputCollection[0].ExternalMetadataColumnCollection[inColumn.Name];
exColumn.Name = inColumn.Name;
SQLDestComponent.MapInputColumn(SQLDestination.InputCollection[0].ID, inColumn.ID, exColumn.ID);
}
SQLDestComponent.ReleaseConnections();

De esta forma completamos el flujo de trabajo que procesaría los registros contenidos en un archivo de texto para enviarlos a una tabla de SQL Server. En la siguiente entrega estaré mostrando un poco cómo crear SQL Task y File System Task.

Nos leemos,

Se pueden crear paquetes de SQL Server Integration Services (SSIS) programáticamente con .NET, haciendo uso de las librerías que incluye SQL Server 2008. En este post se explica cómo crear los paquetes y agregar los elementos básicos.

Serie Creando paquetes de SSIS con .NET

• Agregando elementos básicos
• Creando data flow Task y elementos internos
• Creando SQL Task y File System Task
• Creando Execute Package Task y Execute Process Task

SQL Server Integration Services (SSIS) es un componente presente a partir de SQL Server 2005 como evolución de los DTS. Éstos nos permiten manejar la integración de datos basados en flujos de trabajos. Actualmente, para la versión de SQL Server 2008 (R2), los SSIS se desarrollan usando un IDE llamado SQL Server Business Intelligence Development Studio, el cual es una versión de Visual Studio 2008 con las plantillas y componentes necesarios para crear visualmente este tipo de paquetes (los DTSX).

Cuando instalamos SQL Server 2008 podemos tener acceso a una serie de librerias que nos permitirán tener acceso a funcionalidades de SQL Server vía programación con .NET, funcionalidades que van desde objetos para la administración de servidor hasta el desarrollo programáticamente de objetos y paquetes de base de datos como lo son los DTSX resultantes de SQL Server Integration Services.

A efectos de esta serie de artículos, estaré comentado cómo crear vía programación paquetes de Integration Services usando el .NET Framework 4 y C# como lenguaje de programación.

Agregando referencias

Lo primero que debemos hacer es crear un proyecto en Visual Studio, del tipo que nos convenga y agregar las referencias de las librerías de SQL Server que utilizaremos para tener acceso a las funciones de creación de paquetes. Las librerías a agregar son (con su respectiva ruta donde se encuentran [varía según la instalación]):

• C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.DTSRuntimeWrap.dll
• C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.DTSPipelineWrap.dll
• C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll

Creando el paquete

Para generar el paquete y guardarlo utilizaremos el siguiente código inicial:

using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;

namespace ETLGenDEMO
{
    class Program
    {
        static void Main(string[] args)
        {
            Package pkg = new Package();
            pkg.Name = "NombrePaquete";

            //Acá toda la lógica de contenido del paquete

            new Application().SaveToXml(String.Format(@"C:\Temp\{0}.dtsx", pkg.Name), pkg, null);
            pkg.Dispose();
        }
    }
}

Con este código creamos el paquete y lo guardamos con una extensión .dtsx, como el contenido del paquete está basado en una estructura XML, usamos la función SaveToXml para vaciarlo en un archivo. En la línea comentada iría toda la lógica que querramos agregarle, a efectos de este post veremos cómo crear variables y conexiones.

UPDATE [03/01/2012] Los nombres de los objetos al momento de su creación son estándares, puedes consultar los nombres que corresponden a cada uno en el artículo CreationName for SSIS 2008 and adding components programmatically

Creando variables

Las variables nos permiten pasarle parametros al paquete de SSIS y a su vez darle dinamismo a los objetos del flujo de trabajo que estemos manejando. El siguiente código me permite crear las variables que necesite para mi paquete (recuerda colocar este código en la zona donde está la línea comentada):

//pkg.Variables.Add(NombreVariable, SoloLectura, nameSpace, Valor);
pkg.Variables.Add("LocalDB", false, "", "MiBaseDeDatos");
pkg.Variables.Add("ArchivoDatos",false,"",@"C:\RutaDelArchivo\Archivo.txt");

Con este código estamos creando 2 variables las cuales utilizaremos más adelante en la creación de las conexiones con valores dinámicos.

Creando conexiones

Todos los paquetes cuentan con un manejador de conexiones, en el cual están todos los objetos que contienen los valores necesarios para que el paquete pueda interactuar con un repositorio de datos. Podemos crear los siguientes tipos de conexiones:

• OLE DB
• Flat file
• ADO.NET
• Analysis Services
• File
• Data Source

A efectos de ejemplo, crearemos 2 conexiones, una hacia una base de datos SQL Server y otra hacia un archivo de TXT.

// Conexion SQL OLE-DB
ConnectionManager connectionManagerOleDb = pkg.Connections.Add("OLEDB");
connectionManagerOleDb.Name = "OLEDB";
connectionManagerOleDb.ConnectionString = "Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=MiBaseDeDatos;Integrated Security=SSPI;";

// Conexion Flat File
Microsoft.SqlServer.Dts.Runtime.ConnectionManager srcConMan = pkg.Connections.Add("FLATFILE");
srcConMan.ConnectionString = @"C:\RutaDelArchivo\Archivo.txt";
srcConMan.Properties["LocaleID"].SetValue(srcConMan, "8202");
srcConMan.Properties["CodePage"].SetValue(srcConMan, "1252");
srcConMan.Properties["Format"].SetValue(srcConMan, "Delimited");
srcConMan.Properties["DataRowsToSkip"].SetValue(srcConMan, 0);
srcConMan.Properties["ColumnNamesInFirstDataRow"].SetValue(srcConMan, false);
srcConMan.Properties["Name"].SetValue(srcConMan, "TXT.Archivo");
srcConMan.Properties["RowDelimiter"].SetValue(srcConMan, "\r\n");
srcConMan.Properties["TextQualifier"].SetValue(srcConMan, "");
//Mapear columnas del archivo
ffSrcCM = srcConMan.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile100;

RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 colu = ffSrcCM.Columns.Add();
RuntimeWrapper.IDTSName100 colN = (Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSName100)colu;

colN.Name = "Columna1";
colu.DataType = RuntimeWrapper.DataType.DT_STR;
colu.ColumnWidth = colu.MaximumWidth = 1024;
colu.ColumnDelimiter = ",";
colu.TextQualified = true;
colu.ColumnType = "Delimited";
colu.DataPrecision = 0;
colu.DataScale = 0;

colu = ffSrcCM.Columns.Add();
colN = (Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSName100)colu;
colN.Name = "Columna2";
colu.DataType = RuntimeWrapper.DataType.DT_STR;
colu.ColumnWidth = colu.MaximumWidth = 1024;
colu.ColumnDelimiter = ",";
colu.TextQualified = true;
colu.ColumnType = "Delimited";
colu.DataPrecision = 0;
colu.DataScale = 0;

colu = ffSrcCM.Columns.Add();
colN = (Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSName100)colu;
colN.Name = "Columna 3";
colu.DataType = RuntimeWrapper.DataType.DT_STR;
colu.ColumnWidth = colu.MaximumWidth = 1024;
colu.ColumnDelimiter = "\r\n";
colu.TextQualified = true;
colu.ColumnType = "Delimited";
colu.DataPrecision = 0;
colu.DataScale = 0;

Con este código creamos 2 objetos de conexión, uno hacia una base de datos y otro hacia un archivo de texto. Para el caso de archivos, como la data tiene una estructura para definir cada columna, se debe mapear las columnas a usar.

Valores dinámicos con variables

Como comenté anteriormente, podemos usar las variables de los paquetes para darle dinamismo a los objetos del flujo de trabajo. En este ejemplo, asignaremos el nombre de la base de datos en la conexión de forma dinámica, de tal manera que si nosotros cambiamos el valor de la variable, alteraríamos la cadena de conexión que estamos armando con la variable.

//Sustituir la siguiente línea
//connectionManagerOleDb.ConnectionString = "Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=MiBaseDeDatos;Integrated Security=SSPI;";
//por la siguiente linea
connectionManagerOleDb.SetExpression("ConnectionString", "\"Data Source=(local);Initial Catalog=\"+@MiBaseDeDatos+\";Provider=SQLNCLI10.1;Integrated Security=SSPI;\"");

De esta forma estamos construyendo una cadena de conexión donde estaríamos indicándole que el nombre de la base de datos proviene de una variable definida en el paquete de SSIS. Usando SetExpression podemos realizar este tipo de asignación de valores dinámicos.

En la siguiente entrega estaré mostrando un poco cómo agregar elementos al paquete y darle forma al flujo de trabajo interno.

Nos leemos,