generacion de paquetes ssis programaticamente parte 1

5/14/2018 Generacion de Paquetes SSIS Programaticamente Parte 1 - slidepdf.com

http://slidepdf.com/reader/full/generacion-de-paquetes-ssis-programaticamente-parte-1

thesolidqjournalbusiness intelligence 16

The SolidQ™ Journal, Abril 2011 – www.solidq.com/sqj

Generación de paquetes SSISprogramáticamente (Parte I)

Introducción

S egún la tendencia que hemos detectado por el

tipo de proyectos que llevamos a cabo y que

queda contrastado en periódicos digitales como Finan-

zas.com, una de las profesiones más demandadas

tanto actualmente como en un futuro inmediato es la

consultoría avanzada en inteligencia de negocio.

Parece claro por tanto, que disponer de herramientas

potentes, sencillas y fácilmente amoldables a nue-

stros requerimientos cambiantes va a propiciar un

auge en la calidad de nuestros servicios. Como

cualquier lector sabe, Solid Quality Mentors es el líder

de soluciones global en tecnología Microsoft de la

plataforma de datos, plataforma de datos que nos

provee de entre otras herramientas y tecnologías, las

que vamos a discutir en este artículo: SQL Server Inte-gration Services (a partir de ahora nos referiremos a él

como SSIS).

Como sabemos, todo proyecto de inteligencia de

negocio lleva consigo irremediablemente la consolida-

ción de información eficiente. Dicho proceso de con-

solidación se consigue con SSIS y su diseño es tan

personalizado como potencialmente “similar” en

todos los escenarios en los que hemos trabajado, que

es una buena idea el automatizar su creación.

Desde SolidQ nos hemos percatado que en muchas

ocasiones el cliente duplica bases de datos con la

misma estructura para proporcionar lo que ellos deno-

minan “particiones”, que no es más que un tipo de

“Sharding” manual, o directamente tablas renombrán-

dolas para tratar de conseguir particionado. Todo esto,

no hace más que añadir complejidad al proceso de cre-

ación del Datawarehouse, que es donde debe residir la

información unificada y “limpia” para poder explotarla

correctamente.

Además de dicha complejidad añadida, en la ejecu-

ción de un proyecto de inteligencia de negocios en la

que se involucran la creación de paquetes SSIS, se da la

circunstancia que por tratarse de proyectos “vivos” y

típicamente de media duración, los objetos fuente

(tablas) en ocasiones son modificados para añadir

columnas, cambiar tipos de datos, eliminar columnas,…con

lo que en ocasiones obliga a rehacer trabajo en SSIS quecomo sabemos es fuertemente tipado.

En este artículo sentaremos las bases para poder

entender cómo crear paquetes SSIS con los que con-

seguir que fácilmente podamos unificar toda la infor-

mación a base prácticamente de 2 clics de ratónJ.

Además, gracias a que utilizando dichas librerías no

realizaremos intervención humana directa en la crea-

ción del paquete SSIS, un cambio en los objetos fuente

o destino de nuestros paquetes, solo requerirán darle

de nuevo al botón “generar” de nuestro generador J.

Por mi experiencia, si nuestro sistema pasa de las 50 o

Existe un viejo adagio que dice que cuando necesites escribir código, deberías considerar

en su lugar escribir código que generase código. Los usuarios experimentados de SSIS

generan paquetes repetitivos programáticamente y de esa forma, aprenden a regenerar-los rápidamente cuando surge la necesidad de un cambio. Este artículo es la primera par-

te de una corta serie que le mostrará cómo crear paquetes SSIS mediante programación.

Por Enrique Catala


http://slidepdf.com/reader/full/generacion-de-paquetes-ssis-programaticamente-parte-1The SolidQ™ Journal, Abril 2011 – www.solidq.com/sqj

60 tablas ya se ahorra tiempo haciéndolo de esta forma

si tenemos que repetir el proceso solamente una vezporque al final nos damos cuenta de un detalle (pequeño

o no) con la mitad de las tablas ya habríamos ahorrado

tiempo escribiendo este código.

En resumen lo que vamos a ver es como crear una

aplicación de Consola C# que va a generar paquetes de

SSIS sin intervención humana (obviamente en algun

momento le tendremos que indicar el origen y el des-

tino, pero nada más).

Requisitos para reproducir el artículo

Para la reproducción del artículo se recomienda dispo-

ner de los siguientes requisitos:

• Visual Studio 2010: La versión Express debería

ser suficiente

• SQL Server 2008 R2 (como mínimo será necesa-

rio versión standard para disponer de SSIS y pro-

bar nuestros paquetes generados)

• Conocimientos de SSIS: Se parte de la base de

que el lector conoce como crear paquetes SSIS,

desplegarlos y utilizarlos.

• Conocimientos altos en programac ión C# y

manejo de Visual Studio 2010

• Se parte de la base de que el lector conoce

metodologías de programación orientadas a

objetos y posee conocimientos en el manejo de

Visual Studio 2010

Adicionalmente, en el site de solidq, puedes

encontrar el código de la solución para descarga.

Pincha aquí.

El paquete dtsx

Antes de nada, hay que tener claro que el proceso degeneración por código de paquetes SSIS no está bien

documentado, por lo que mucho de lo que aquí vas a leer

proviene tanto de prueba-error, como de ingeniería inversa

analizando el comportamiento de objetos y el xml resul-

tante. Con ello no quiero decir que haya violado ningún

copyright, simplemente estoy diciendo que para saber

cómo funciona correctamente un método, en ocasiones

he tenido que ver qué generaba y compararlo con uno que

había generado mediante la interfaz de Visual Studio.

Recuerda además, que un paquete de integration ser-

vices, no es más que un XML que contiene la meta infor-

mación necesaria para que el proceso dtexec.exe sea

capaz de realizar lo que hemos indicado en él.

Para nuestro ejemplo usaremos la BBDD Adventu-

reWorks y lo que haremos será mover información a

una BBDD nueva a la que llamaremos Staging.

Figura 1

Figura 0

Figura 2





¿Qué pretendemos obtener?

Lo primero que recomiendo al lector, es que tenga

claro lo que pretende obtener, y la mejor manera de

hacerlo es realizando el paquete mediante la herra-

mienta destinada para ello, Visual Studio. Una vez se

tenga claro lo que se desea obtener, podremos enton-

ces plantear darle una solución por código.

Se sobreentiende por tanto, que el lector plantea

realizar “n” paquetes idénticos en los que cambie

alguna particularidad. Por experiencia, es un escenario

muy común.

Para este primer artículo, vamos a empezar con uncódigo de lo más sencillo, para ir entrando las bases de

lo que implica codificar SSIS programáticamente. Por

tanto, el ejemplo más sencillo que se me ha ocurrido

es simplemente mover datos de una tabla origen a una

tabla destino.

Para conseguir esto, si estuviéramos utilizando el

modelador de SSIS, deberemos añadir un componente

DataFlow tal y como se puede apreciar en la Imagen 1,

página 17, y dentro de ella, dos componentes OleDb-

Source y OleDbDestination.

Pero previamente a ello, lo normal es que añada-

mos nuestros objetos conexión, tal y como podemosver en la Imagen 0, página 17.

En la configuración del OleDbSource, indicaríamos

qué objeto queremos como fuente (Imagen 3) y en

OleDbDestination indicaríamos qué objeto queremos

como destino (Imagen 4)

En resumen, mover los datos de la tabla HumanRe-

sources.Employee de la BBDD Adventureworks, a la

tabla dbo.employee de la BBDD Staging.

NOTA: El único requisito de momento es que la

tabla destino, a la que nosotros referenciamos como“employee”, debe existir.

Creación del paquete programáticamente

Para la construcción de nuestro generador de SSIS,

vamos a optar por utilizar Visual Studio 2010. Esto igual

te llama la atención, ya que la edición Visual Studio

2008 es la edición habilitada para crear paquetes SSIS.

No obstante, en nuestro caso lo que vamos a hacer es

utilizar las librerías que finalmente utiliza Visual Studio

2008, por lo que es indiferente el entorno de desarrollo

utilizado, puesto que nosotros vamos a construirnos

nuestro propio generador de SSIS.

Lo primero que debemos hacer es crearnos una

solución que constará de:

Figura 3

Figura 4

Figura 5



• Proyecto de librería (EnriqueCatalaSSISGenera-

tor ): Contendrá el código del generador de SSIS.

• Proyecto de consola (DemoSSISGenerator ): Se

encargará de utilizar el proyecto de librería para

crear el paquete.

Lo siguiente será añadir las librerías necesarias

para la elaboración de la librería del generador de SSIS.

Dichas librerías se encuentran dentro de la carpeta

SDK de Microsoft SQL Server y son las que se pueden

apreciar en la imagen 6.

• C:\Program Files (x86)\Microsoft SQL Ser-

ver\100\SDK\Assemblies:

Una vez hecho esto, nos pondremos manos a la

obra para la codificación de los métodos necesarios

para nuestro Generador de código SSIS.

NOTA: Recuerda que puedes descargar la solución

de aqui.

Librería de generación de código SSISVisto el tipo de paquete SSIS que queremos modelar,

nos debemos poner manos a la obra para la codifica-

ción de los métodos necesarios, que deben cubrir los

siguientes escenarios:

• Añadir objetos conexión OLEDB

• Añadir componentes Data Flow

• Añadir componentes OleDbSource

• Añadir componentes OleDbDestination

Método principal de generación de paquete que

utilice los anteriores

Añadir objetos conexión OLEDB

Para añadir una conexión OleDb a un paquete SSIS,

lo único que debemos hacer es crear un objeto de la

clase “ConnectionManager”, instanciando sus propie-

dades ConnectionString y Name.

Mientras que ConnectionString identificará una

cadena de conexión válida, la propiedad Name, identifi-

cará el nombre de la conexión tal y como la vemos en

el paquete al editarlo.


Figura 6



El código necesario para añadir conexiones OleDb

a nuestros paquetes por código será este:

Añadir componentes DataFlow

Añadir componentes dataflow requiere añadir al

conjunto de Executables del objeto Package, un tipo de

ejecutable denominado “STOCK:PipelineTask”.

Añadir componentes OleDbSource

Si recordamos, en el escenario que queremos

modelar, estamos tomando datos directamente desde

una tabla, identificándola únicamente por su nombre(no hemos especificado ninguna select).

Lo primero que debemos hacer es crear un objeto

estándar IDTSComponentMetaData100 que identifi-

cará nuestro objeto conexión (al origen, recuerda).

Luego, ese componente, como queremos que sea un

OleDbSource, lo identificaremos en su propiedad Com-

ponentClassID indicando el valor “DTSAdapter.OLEDB-

Source”.

Una vez hecho eso, lanzaremos el método Instan-

tiate() y ProvideComponentProperties() para que el

objeto adquiera sus propiedades de OleDBSource.

Finalmente, debemos indicar en sus propiedades

que será “AccessMode = 0” y que el tipo “OpenRow-

set” debe apuntar al nombre de tabla con esquema

que le hayamos pasado (recuerda, en nuestro ejemplo

será HumanResources.Employee.

Finalmente, inicializaremos sus metadatos (ver

código try). Esta última parte, abrirá conexión contra

SQL Server e intentará inicializarlos, si por lo que sea

hemos proporcionado mal el objeto “connection”, nos

dará error aquí. Ver listado 3, página 21.

Añadir componentes OleDbDestination

En este momento, nos queda proporcionar el código

que identifique al componente OleDbDestination.

A diferencia del anterior método, en este caso

no solo necesitaremos proporcionar un objeto cone-

xión hacia destino y un nombre de tabla…en este

caso, necesitaremos proporcionarle un componente

origen (recuerda que en este caso, vamos a conec-

tar directamente el componente OleDbSource al

OleDbDestination.



protected ConnectionManager AddOleDbConnection(Package pck, string nameConexion, string CadenaConexion)

{

ConnectionManager cm;

cm = pck.Connections.Add(“OLEDB”);

cm.ConnectionString = CadenaConexion;

cm.Name = nameConexion;

return (cm);}

Listado 1

protected virtual TaskHost AddDataFlow(Package pck, string dataFlowName)

{

Executable e = pck.Executables.Add(“STOCK:PipelineTask”);

TaskHost thMainPipe = (TaskHost)e;

thMainPipe.Name = dataFlowName;

return (thMainPipe);

}

Listado 2



Al igual que antes, el código de inicialización es

muy similar, y solo va a cambiar:

• ComponentClassID valdrá “DTSAdapter.OleDb-

Destination” puesto que queremos un objeto de

tipo destino

• Ahora, las propiedades asignadas serán:

– AccessMode = 3 (carga identificando un nom-

bre de objeto)

– FastLoadOptions = “TABLOCK, CHECK_CONS-TRAINTS” (queremos carga rápida)

– OpenRowset = nombre de objeto destino (en

este caso dbo.employee).

• Una vez indicadas las propiedades, en el bucle

foreach que se aprecia en el código, mapeare-

mos las columnas del componente OleDbSource

a este componente recientemente creado. Ver

listaddo 4, página 22.

protected virtual IDTSComponentMetaData100 AddSourceOleDbFromTable(PWrap.MainPipe flujo, string nombreComponente, string no

mbreTablaConEsquema, ConnectionManager connection)

{

PWrap.IDTSComponentMetaData100 conexionAOrigen = flujo.ComponentMetaDataCollection.New();

conexionAOrigen.Name = nombreComponente;

conexionAOrigen.ComponentClassID = “DTSAdapter.OLEDBSource”;

PWrap.CManagedComponentWrapper instance = conexionAOrigen.Instantiate();

instance.ProvideComponentProperties();

conexionAOrigen.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connection);

conexionAOrigen.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ID;

conexionAOrigen.Name = nombreComponente;instance.SetComponentProperty(“AccessMode”, 0);

instance.SetComponentProperty(“OpenRowset”, nombreTablaConEsquema);

// Reinit metadata

try{

instance.AcquireConnections(null);instance.ReinitializeMetaData();instance.ReleaseConnections();

}catch (Exception e){

throw ;}return (conexionAOrigen);

}

Listado 3





Listado 4



Generación del paquete

Una vez dispuestos del código anterior, única-

mente debemos utilizar los métodos creados anterior-

mente para generar nuestro paquete. Esto es tan fácil

como lo que veis en el código siguiente. Ver listado 5.

Proyecto de consola

Ya por último, en nuestra aplicación de consola,

solo nos queda instanciar un objeto de nuestra clase

generadora de código y proporcionarle los valores que

queramos. Ver listado 6, página 24.El resultado de su ejecución puede verse aquí (des-

pués de abierto y lanzado el paquete.dtsx)

public void GeneratePackage(string sourceConnectionString,string destinationConnectionString){

/// Open connection to source///OleDbConnection cn = new OleDbConnection(sourceConnectionString);

cn.Open();

/// Package creationPackage Mipk = new Package();

///We can specify a package name (optional)Mipk.Name = “PackageName” ;

Application app = new Application();

///Source OleDb connectionConnectionManager connOrigen = AddOleDbConnection(Mipk, “ORIGEN”, sourceConnectionString);///Destination OleDb connectionConnectionManager connDestino = AddOleDbConnection(Mipk, “DESTINO”, destinationConnectionString);

// Add DataFlowPWrap.MainPipe df = AddDataFlow(Mipk, “SolidQJ Dataflow”).InnerObject as MainPipe;

/// Add Source oledb connectorIDTSComponentMetaData100 source = AddSourceOleDbFromTable(df,“Source component”, “humanresources.employee ”, con-

nOrigen);

/// Add destination oledb connectorAddOleDbDestinationTable(df,connDestino,source,“dbo.Employee”);

///Save the file to folderapp.SaveToXml(@”c:\bbdd\package.dtsx”,Mipk,null);

}

Lis tado 5





ConclusiónComo se ha podido ver, el proceso de generación

mediante código de SSIS es una tarea relativamente

sencilla si se conocen los aspectos básicos de la

misma. No obstante, su codificación entraña la necesi-

dad de conocer por dentro las clases de generación de

componentes y objetos, que por otro lado no hemos

entrado en mucho detalle por tratarse de un primer

artículo y no quiero asustar al lector con detalles dema-

siado profundos.

La idea final es acabar generando paquetes más

complejos, como los que se pueden ver en la siguiente

imagen, que poseen flujos variables y componentes

más complejos con operadores “Merge Join” o el

temido “Conditional Split” que tantas horas nos hacen

pasar delante a la hora de su configuraciónJ.

Gracias a una librería como la que disponemos en

SolidQ y que queremos dar una idea de su construc-

ción al lector, desde Solid Quality Mentors, podemos

generar miles de paquetes que realicen tareas real-

mente complejas en tan solo unos segundos, y lo quees mas importante…un cambio en los objetos inheren-

tes al paquete (cambios de tipos de datos, nombres de

columnas, de objetos…no tienen ningún coste alguno

puesto que regenerar el paquete es cuestión de “dos

clics de ratón”J.

En siguientes artículos, veremos como seremos

capaces de que incluso estos operadores sean capa-

ces de inferir columnas y tipos de datos complejos sin

intervención humana.

Aquí tenemos unas muestras de los tipos de

paquete que genera el generador de SSIS de solidq sin

intervención humana. Ver Figura 7.

Acerca del Autor

Enrique Catalá Bañuls pertenece

al área relacional de SolidQ. Es MCT,

MCITP, MCTS y ha sid o nombrado

MAP 2010 (Microsoft Active Profes-

sional). Es arquitecto y programador

de la solución HealthCheck, SCODA,SolidQDataCollector y el SolidQ SSIS Generator. En los

pasados 5 años mientras se centraba en las bases de

datos SQL Server aportaba soluciones a problemas rela-

cionados con el rendimiento, la escalabilidad, las migra-

ciones y la alta disponibilidad. Además de impartir cursos

oficiales de Microsoft, ha tomado parte como ponente en

el lanzamiento Microsoft SQL Server 2008) en el 24h SQL

PASS Conference, y como miembro del nuevo SQL PASS

Spain, también ha impartido charlas a grupos de usuarios

de Microsoft GuseNET. También es ponente habitual en

SolidQ Summit Madrid.

static void Main(string[] args){

EnriqueCatalaSSISGenerator.EnriqueCatalaSSIS packageGenerator = new EnriqueCatalaSSISGenerator.EnriqueCa-talaSSIS();

String destinationCS = @”Data Source=(local)\sql2008r2;Initial Catalog=Staging;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-Package;Auto Translate=False;”;

String sourceCS = @”Data Source=(local)\sql2008r2;Initial Catalog=AdventureWorks;Provider=SQLNCLI10.1;In-tegrated Security=SSPI;Application Name=SSIS-Package;Auto Translate=False;”;

packageGenerator.GeneratePackage(sourceCS,destinationCS);}

Listado 6

Figura 7

generacion de paquetes ssis programaticamente parte 1

Documents