Date post: | 17-Jul-2015 |
Category: |
Documents |
Upload: | ricardoulloadiaz |
View: | 316 times |
Download: | 0 times |
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
5/14/2018 Generacion de Paquetes SSIS Programaticamente Parte 1 - slidepdf.com
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
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 18
The SolidQ™ Journal, Abril 2011 – www.solidq.com/sqj
¿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
5/14/2018 Generacion de Paquetes SSIS Programaticamente Parte 1 - slidepdf.com
http://slidepdf.com/reader/full/generacion-de-paquetes-ssis-programaticamente-parte-1
• 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.
The SolidQ™ Journal, Abril 2011 – www.solidq.com/sqj
Figura 6
5/14/2018 Generacion de Paquetes SSIS Programaticamente Parte 1 - slidepdf.com
http://slidepdf.com/reader/full/generacion-de-paquetes-ssis-programaticamente-parte-1
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.
thesolidqjournalbusiness intelligence 20
The SolidQ™ Journal, Abril 2011 – www.solidq.com/sqj
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
5/14/2018 Generacion de Paquetes SSIS Programaticamente Parte 1 - slidepdf.com
http://slidepdf.com/reader/full/generacion-de-paquetes-ssis-programaticamente-parte-1The SolidQ™ Journal, Abril 2011 – www.solidq.com/sqj
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
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 22
The SolidQ™ Journal, Abril 2011 – www.solidq.com/sqj
Listado 4
5/14/2018 Generacion de Paquetes SSIS Programaticamente Parte 1 - slidepdf.com
http://slidepdf.com/reader/full/generacion-de-paquetes-ssis-programaticamente-parte-1The SolidQ™ Journal, Abril 2011 – www.solidq.com/sqj
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
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 24
The SolidQ™ Journal, Abril 2011 – www.solidq.com/sqj
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