SSIS – Asignación dinámica de path (Directory) a un Foreach

Buenas, es posible configurar dinámicamente el path (Directory) de un componente For Each. De este modo nuestros procesos pueden recorrer diferentes directorios configurados dinámicamente con solo modificar el fichero de configuración utilizado como “Package Configuration”.
Si ya contamos con un fichero de package configuration debemos editarlo, caso contrario, crearemos uno para poder anexar la funcionalidad que pretendemos (Si tienes dudas de cómo crear y/o editar ficheros de configuración puedes leer mis posts anteriores donde se explica claramente).
Una vez abierto el fichero de configuración en el paso donde debemos seleccionar las propiedades a exportar, debemos buscar el componente Foreach Loop Container dentro del Package y tildar el check “Directory” y dar Next y finalizar en todos los restantes pasos del asistente.



Luego abrimos manualmente el fichero de configuración en el path que se encuentre y allí entre los tags del Directory debemos poner el path donde se encontrarán los ficheros que deseamos que recorra el Foreach



Espero les hay sido de utilidad este nuevo post, saludos a todos!

SSIS – Asignación dinámica de nombres a ficheros de destino de Excel

Buenos días a todos, en este post voy a explicitar las acciones requeridas para asignar dinámicamente los nombres a los ficheros de destino de Excel (ficheros XLS). Este caso es típico cuando se pretende hacer una exportación diaria de información por ejemplo. Los pasos a seguir son:
1. Crear una variable a nivel de Package de tipo String, la cual va a contener el nombre del fichero de destino.
2. Contar con un Script component en el Data Flow, en el que debemos armar el nombre del fichero concatenando fechas u otras cadenas según sea el nombre que deseamos formar, y posteriormente asignarle dicho nombre a la variable que creamos en el punto 1. (Para ver cómo trabajar con variables en un Script haga click aqui)
3. En la Excel Connection de destino debemos dirigirnos a sus propiedades, Expressions y a la propiedad ConnectionString asignarle la variable creada en el punto 1.


Si son tus primeros pasos en SSIS seguramente no comprenderán demasiado como armar todo el package, si eso sucede les recomiendo que lean los primeros posts en donde explico más detalladamente este tema.
Espero les sirva mi post, y como siempre espero sus comentarios… Saludos!

SSIS – Usar Data Viewers

Hola a todos… he regresado con un post “sumamente interesante”… Voy a mostrarles como usar Data Viewers en los flujos de datos. Seguramente se preguntarán para que nos sirven los Data Viewers… nos sirven principalmente para poder observar que datos se están pasando de un componente a otro a través de un flujo de datos y de esta manera poder llevar a cabo un seguimiento más preciso de nuestro proceso.
Veamos cómo se agregan y utilizan los Data Viewers…
Utilizando el mismo ejercicio que expliqué con anterioridad (http://mciacci.blogspot.com/2008/04/ssis-uso-de-condicionales-en.html ) o cualquier otro ejercicio que tengo al menos 2 componentes, debemos hacer lo siguiente:
1. Click derecho sobre la flecha verde que une los componentes, y seleccionamos le opción Edit…

2. Hacemos click en lo opción Data Viewers de la ventana Data Flow Path Editor y posteriormente en el botón Add… de la parte inferior.



3. En la ventana Configure Data Viewer dejamos seleccionado Grid como está por defecto y damos Ok.



4. Damos click en Ok de la ventana Data Flow Path Editor



5. Veremos cómo se agrega un icono de una grilla al costado de la flecha verde, lo que indica que el Data Viewer ha sido agregado.



6. Al ejecutar el proceso veremos que se abre una grilla con los datos que están circulando de un componente a otro. Esto nos servirá para analizar los datos en busca de errores.



Espero les haya servido de ayuda y ante cualquier duda escriban sus comentario

SSIS – Unpivot con un Script

Muchas veces necesitamos invertir la posición de algunas filas y columnas de entrada para guardarlas en destino. Aclararé un poco más este asunto con un ejemplo, supongamos que tenemos los datos con la siguiente estructura:



Supongamos que por algún motivo necesitamos que esa estructura cambie y adopte la siguiente:



Lo que hemos hecho en la segunda tabla es crear una fila (Row) por cada columna de la primer tabla (excepto la columna ID), entonces por ejemplo, para la primer fila de la primer tabla tenemos 3 filas en la segunda tabla, una para cada columna. Las columnas de la segunda tabla representan lo siguiente:
ID: Código de Cliente, equivalente a la columna ID de la primera tabla.
Columna: indica el número de columna del dato puesto en la columna “Valor” en la primera tabla.
Valor: indica el valor de una determinada columna en la primera tabla.

Ahora…. Como podemos hacer esto en una package de SSIS???
Lo que debemos hacer es agregar en el Data Flow un componente Script de tipo Transformación, posteriormente vincularlo con el componente que le proporcione los datos de entrada y crear los Outputs que necesitamos de salida. Para comprender mejor el asunto, voy a aclarar que lo los Inputs que necesitamos son los campos listados en la primera tabla, mientras que los Outputs son los correspondientes a la segunda tabla. Posteriormente colocaremos el siguiente código dentro del Script:

'Fila para la Columna 1 = Columna Cliente
With Output0Buffer
.AddRow()
'ID es el OUTPUT para el Campo ID y Row.ID es el Input
.ID = Row.ID
.COLUMNA = 1
.VALOR = Row.Cliente
End With
'Fila para la Columna 2 = Columna Categoria
With Output0Buffer
.AddRow()
'ID es el OUTPUT para el Campo ID y Row.ID es el Input
.ID = Row.ID
.COLUMNA = 2
.VALOR = Row.Categoria
End With
'Fila para la Columna 3 = Columna Deuda
With Output0Buffer
.AddRow()
'ID es el OUTPUT para el Campo ID y Row.ID es el Input
.ID = Row.ID
.COLUMNA = 3
.VALOR = Row.Deuda
End With

Damos Ok en el script y lo unimos al destino que corresponda para que se produzca la importaci[on de los datos al destino deseado.
Eso es todo, es más simple de lo que parece… y ya saben, si tienen dudas solo escriban comentarios. Ciao!

SSIS –Deploying Soluciones

Una vez concluidos y testeados los procesos y demás componentes de una solución, seguramente se necesitará redistribuirlos en otros servidores. Para ello podremos realizar el Deploy de la solución a través del Deploy Utility del SQL Server Business Intelligence Development Studio. Los pasos a seguir son los siguientes:
1. Click botón derecho sobre la Solución, escoger Propiedades.
2. Debemos situarnos en Deployment Utility, y setear la propiedad CreateDeploymentUtility en True. También podemos cambiar el destino de ficheros resultantes modificando la propiedad DeploymentOutputPath.



3. Damos OK.
4. Hacemos click derecho sobre la Solución nuevamente, y escogemos la opción Build. Se compilará la Solución y en caso de no existir errores se deberán crear los ficheros de Deploy en el path especificado en la propiedad DeploymentOutputPathantes mencionada.



5. Si el proceso de Deploy fue exitoso deberá existir en el directorio de Output un fichero con extensión SSISDeploymentManifest. Ejecutando ese Fichero nos aparecerá un asistente para la instalación de los diferentes paquetes, ficheros de configuración y demás componentes de la Solución.

SSIS – Error en Deploy con Ficheros de Configuración Compartidos

Hace unas semanas, cuando intentaba hacer el deploy de mi solución para redistribuirla me surgió un error muy curioso. El mismo se produce al intentar hacer el deploy de una solución en la que 2 o más packages comparten un mismo fichero de configuración.
Si no conocen que es un fichero de configuración, brevemente les puedo decir que son ficheros por lo general en formato XML que sirven para poder insertar o setear las diferentes propiedades de objetos dentro del Package, por ejemplo se puede definir la ConnectionString de una conexión a BD, el valor de una Variable, entre otros.
El mensaje de error es el siguiente:

System.ApplicationException: Could not copy file "C:\...\xxx.dtsConfig" to the deployment utility output directory "C:\...\xxx\bin\Deployment". ---> System.IO.IOException: The file 'C:\...\...\xxx.dtsConfig' already exists. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) --- End of inner exception stack trace --- at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CreateDeploymentUtility(IOutputWindow outputWindow)

Investigando en diferentes sitios, libros y materiales pude encontrar una solución “manual” (sí, escucharon bien!!). Dicha solución consiste en crear un archivo de texto, renombrarlo con la extensión .SSISDeploymentManifest e insertar código con estructura XML que enumere todos los packages, ficheros de configuración y demás elementos a redistribuir. Por ejemplo:



Al ejecutar éste archivo, aparecerá el asistente de instalación como normalmente debería suceder.
He leído por algunos foros que el SP2 ya soluciona este bug, aún no he podido comprobarlo, pero ni bien lo haga lo agregaré a mi Blog.
Espero haber sido de ayuda, saludos… nos vemos en la próxima publicación.

SSIS – Usar Package Configuration para parametrizar los paquetes

SQL Server Integration Services (SSIS) cuenta con una opción sumamente útil a la hora de poner en producción nuestros packages. Dicha funcionalidad es Package Configuration y sirve, básicamente para parametrizar externamente algunos componentes tales como variables, ConnectionStrings para conexiones a Bases de Datos o archivos planos, etc. Esto se lleva a cabo desde archivos externos, comúnmente en formato XML.
Tal vez uno de los usos más frecuentes sea el parametrizar las ConnectionString de las conexiones a las BD tanto de origen como de destino, para de esta manera permitir que el package pueda trabajar con diferentes BD sin necesidad de que éste tenga que ser modificado. Simplemente modificando la cadena de conexión en el archivo de configuración podemos permitir trabajar con diferentes BD.
Los pasos a seguir para utilizar archivos de configuración de packages son los siguientes:
1. Ir al Menú SSIS, elegir la opción Package Configurations…
2. Se abrirá la Ventana de Package Configurations Organizer, en la cual debemos marcar el check que dice Enabled Package Configurations.


3. Hacer clic en el botón Add… tras lo cual se abre una nueva ventana de asistente que nos guiará en el proceso de creación del archivo de configuración. Si hacemos clic en el botón NEXT> ingresamos al primer paso de la creación. Aquí escogeremos el tipo o formato de archivo que deseamos configurar (normalmente lo dejaremos en XML) y luego debemos ingresar el path y el nombre que deseamos que tenga el archivo. Hacemos clic en NEXT> para ir al siguiente paso.


4. En este paso debemos seleccionar las propiedades que deseamos exportar al archivo de configuración. En nuestro caso, suponiendo que tenemos una conexión a una Base de Datos, seleccionaremos la propiedad “connectionstring” de dicha conexión. Damos clic en NEXT> para dirigirnos al último paso.


5. En éste último paso debemos colocar el nombre de configuración y damos click en Finish para culminar este proceso. De esta manera el archivo de configuración ya esta creado y podremos acceder a él para editar la cadena de conexión si lo creemos necesario.


6. Si nos dirigimos al path donde almacenamos el archivo de configuración y lo abrimos podremos ver que se trata de un simple archivo XML. Podemos modificar la connectionString realizando cambios en el lugar adecuado como puede observarse en la siguiente imagen.

SSIS – Uso de Condicionales en componentes Derived Columns

Quizás se han preguntado alguna vez si es posible el uso de condicionales dentro de un componente de un Data Flow para de esta manera setear el valor de un campo de destino si se cumple determinada condición en un dato de origen. Por ejemplo, si el campo Sexo es igual a 1 (sexo = 1) en el destino debe almacenarse el Valor “Mascuilno”, caso contrario, debe almacenarse el valor “Femenino”. La sintaxis sería la siguiente:
([sexo] == ”1”) ? “Masculino” : “Femenino”
Esta sintaxis puede aplicarse en un componente Derived Column como se puede ver en el siguiente ejemplo. Lo primero que haremos es crear un fichero como el siguiente:


Luego crearemos un nuevo proyecto de Integration Services en SQL Server Business Intelligence Development Studio con el nombre de “Conditional”.
Crearemos las conexiones de origen y destino respectivas como se ven en las siguientes imágenes.

Conexión de Origen – Flat File


Conexión de Destino – Flat File

Insertamos en el Control Flow un Data Flow Task la que llamaremos “Conditional Data Flow task” y dentro de este último insertaremos un Flat File Source, un Derived Column y en Flat File Destination y los relacionaremos como se muestra en la figura siguiente:


En el componente Derived Column pondremos el siguiente código que permitirá realizar la determinación del Sexo del Cliente que se está procesando.


Posteriormente, en el Flat File Destination debemos cambiar el mapeo de campos como se ve en la siguiente figura, para que de esta manera la nueva columna derivada que hemos creado se vincule con la columna “sexo” del fichero de destino.


El resultado del proceso será el siguiente un archivo llamado Cliente_Destino.txt con el siguiente contenido:

SSIS - Uso de Precedence Constraint para direccionar el Flujo de datos

En éste, mi 3er Post sobre Integration Services voy a mostrarles un ejemplo de cómo desviar el flujo de datos que queremos importar según se cumplan ciertas condiciones. El ejemplo que veremos a continuación, si bien es muy simple, nos servirá para ilustrar una forma de razonamiento muy útil para diversas situaciones.
Supongamos que tenemos una empresa en la que los clientes pueden ser Nacionales e Internacionales. El proceso que debemos realizar debe tomar los datos desde 2 ficheros “.txt” los cuales poseen nombres que los diferencian si se tratan de clientes Nacionales o Extranjeros. El formato del nombre de los ficheros para clientes nacionales es ddmmaaaaNac.txt y para los Internacionales es ddmmaaaaInt.txt y estructuralmente la diferencia es que éstos últimos poseen una columna más denominada “Pais” y por éste motivo deben procesarse por separado, de lo contrario se produciría un error.

Los pasos a seguir en la construcción del ejemplo serán:

1. Creación de Ficheros Origen y Tabla de destino de Datos
2. Creación del Proceso SSIS
3. Ejecución del proceso y análisis de resultados

1. Creación de Ficheros Origen y Tabla de destino de Datos
Para comenzar nuestro ejemplo, debemos crear 4 ficheros “.txt” (ver imagen) en los cuales el separador de columnas es el “tab” y el de filas el “Enter” ({CR}{LF})



Una vez creados estos 4 ficheros con sus respectivos nombres, estructuras y contenidos pasemos a la creación de la tabla destino en SQL Server.
El nombre de la tabla será TablaClientes en la BD Examples y su estructura será la siguiente:



2. Creación del Proceso SSIS
Abriremos el SQL Server Business Intelligence Development Studio donde de crearemos un nuevo proyecto de Integration Services, como nombre le pondremos “Condicional”.
Una vez situados en el Control Flow del Package crearemos 2 variables, la primera se llamará varFileName que contendrá el nombre del fichero que se está procesando en un momento determinado y la variable varFilenationality, la cual albergará los valores “Nac” o “Int” según sea la terminación del archivo procesado. Ambas variables serán de tipo String.



Posteriormente agregaremos las conexiones con los ficheros de Origen y la BD de destino de Datos. Tendremos 2 Flat File Connection, una para los Clientes nacionales y otra para los clientes internacionales, y una OLE DB Connection que se conectará a la BD Examples.
Para las Flat Connections deberemos seleccionar el primer fichero “txt” que creamos con anterioridad según se trate de clientes Nacionales o internacionales, es decir, para la conexión “Clientes Nacionales – Origen” debemos seleccionar como File Name 15102007Nac.txt y para “Clientes Internacionales – Origen” debemos seleccionar como File Name 15102007Int.txt



Además, para estas Flat Connections debemos modificar su propiedad Expressions y colocar como connectionString la variable varFileName. Esto permite que a medida que el foreach recorre los ficheros del directorio, los orígenes de datos obtengan sus datos de los ficheros correspondientes.



Luego, sobre la superficie del Control Flow agregaremos un componente Foreach en el cual definiremos el path que contiene los ficheros y el tipo de ficheros a importar, en nuestro caso “*.txt”



En la opción Variable Mappings del Foreach asignaremos la variable varFileName.



A continuación agregaremos dentro del Foreach un Script Task con el cual podremos determinar si el fichero procesado por el foreach se trata de clientes Nacionales o Internacionales. Renombraremos el script task como “Obtener Ambito Cliente” y lo editaremos. En la opción Script, haremos click en el botón Design Script donde agregaremos el siguiente código:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim vars As Variables
Dim FileNationality As String
Dim varsNationality As Variables

'asignamos a la variable local "vars" el contenido de la variable varFileName
'que obtiene el nombre del fichero en el Foreach
Dts.VariableDispenser.LockOneForRead("User::varFileName", vars)

'Procesamos la variable varFileName para obtener el ámbito del fichero txt
FileNationality = vars("varFileName").Value.ToString.Substring(vars("varFileName").Value.ToString.Length - 7, 3)

'instanciamos y asignamos a la variable varFileNationality el ámbito obtenido
Dts.VariableDispenser.LockOneForWrite("varFileNationality", varsNationality)
varsNationality("varFileNationality").Value = FileNationality

Dts.TaskResult = Dts.Results.Success
End Sub

End Class


Resumiendo el código, vemos que obtenemos el nombre del fichero que estamos procesando en este momento procesando la variable varFileName, de la cual extraemos el ámbito (Nac o Int) y lo asignamos a la variable varFileNationality. Ésta variable será la que utilizaremos para desviar el flujo de datos hacia un Data Flow u otro más adelante.

El siguiente paso consiste en crear 2 Data Flow, uno llamado “Clientes Nacionales Data Flow Task” y el otro “Clientes Internacionales Data Flow Task”, donde el primero se encargará de importar en la tabla TablaClientes de la BD Examples los datos relativos a los clientes Nacionales y el segundo, los datos de los clientes Internacionales.

Arrastramos sendas flechas desde el Script hasta los Data Flow recientemente creados y el siguiente paso será editar los Precedence Constraint haciendo doble click sobre las mencionadas flechas que unen el Script con los Data Flow.

Editemos el Precedence Constraint que une el Script con el Clientes Nacionales Data Flow Task. Allí debemos cambiar la Evaluation operation a Expression y dentro del campo homónimo escribir:
@[User::varFileNationality] == "Nac"



Damos OK y repetimos la operación para el Precedence Constraint que une el script con el el Clientes Internacionales Data Flow Task, imitamos la configuración anterior pero reemplazando la Expression por la siguiente:
@[User::varFileNationality] == "Int"

El Control Flow quedará de la siguiente manera:



Con esto hemos finalizado el Control Flow, por lo que debemos realizar la funcionalidad de los Data Flow Clientes Nacionales Data Flow Task y Clientes Internacionales Data Flow Task. En ambos debemos agregar un Flat File Source en donde utilizarán las conexiones “Clientes Nacionales – Origen” y “Clientes Internacionales – Origen” respectivamente. Posteriormente debemos agregar un OLE DB Destination que utilizará la conexión ““localhost.Examples” antes creada.



Luego de unir ambos componentes arrastrando la flecha verde del Flat File Source al OLE DB Destination, debemos realizar el mapeo de los campos en éste último.

Cómo podemos ver ambos Data Flow son muy similares, excepto que en el de los clientes Nacionales no disponemos de la columna “Pais” y por lo tanto no podemos mapearla a la Tabla de la BD.



3. Ejecución del proceso y análisis de resultados
El siguiente y último paso será ejecutar el proceso. Si nos paramos sobre el Control Flow de modo tal de ver el Foreach y todo su contenido y ejecutamos el proceso (Presionando F5) veremos cómo según sea el fichero que se está procesando correspondiente a clientes Nacionales o Internacionales, la ejecución se destinará a uno u otro Data Flow de acuerdo a que condición cumpla la variable varFilenationality obtenida.



Si analizamos los datos de la tabla TablaClientes podemos ver que se han importado correctamente los datos y la diferencia entre los clientes Nacionales e Internacionales es que estos últimos poseen un País, mientras que los nacionales tienen el valor NULL en ese campo.

Espero les haya sido de utilidad mi ejemplo y no duden en escribir comentarios ante cualquier duda o inquietud.

Project REAL

Buenas a todos los miles de lectores de mi Blog (hay que ser optimista en la vida… jeje). Para todos aquellos que estén dando sus primeros pasos en el mundo de BI (Business Intelligence) les recomiendo que den un vistazo a Project Real. Project Real, como se deduce de su nombre, es el resultado de un esfuerzo conjunto entre Microsoft y numerosos e importantes partners (tales como Unisys y Scalability Experts entre otros) cuyo fin es el proporcionar a las empresas una serie de ejemplos cuasi-reales orientados a instruir sobre la utilización de “Best Practices” para la creación de este tipo de proyectos con MS SQL SERVER 2005.

Si te especializas en ETL o Reporting Services puedes encontrar conceptos y ejemplos muy interesantes al respecto. A continuación les dejo una serie de Links que pueden ser de ayuda.

• Project REAL - Home Page
• Project REAL - Best practices
• Project REAL - Analysis Services Technical Drilldown
• Project REAL - Business Intelligence ETL Design Practices

Espero les sean de utilidad estos links. Hasta el próximo post!!!

SSIS – Atributos Históricos tomando fecha desde nombre del Archivo

He regresado con mi 2º post sobre SSIS, espero el anterior les haya sido de utilidad. Este ejemplo es una continuación del anterior, por ello es importante que hayan respetado la nomenclatura y demás características del mismo. Aquí explicaré como a través de un SCD (Slowly Changing Dimension) podemos llevar un registro histórico de los datos de alumnos ingresados en el post anterior, tomando las fechas que poseen los nombres de los ficheros XLS. Para decirlo más claro, la TablaAlumnos va a contener un registro histórico de los cursos en los que han estado los alumnos, con su fecha de alta y baja respectiva para cada alumno y curso. En caso de que un alumno repita de curso, como los registros de los 2 ficheros XLS serán iguales, el SCD lo detectará y sólo se ingresará una vez.
Al igual que el post anterior, procederé a dividirlo en partes para simplificar su comprensión. Estas son:

1. Modificar los datos en último fichero XLS
2. Agregar componente Script Task para obtener la fecha desde el nombre del fichero
3. Agregar componente SCD
4. Ejecutar el proceso y analizar los resultados

1. Modificar los datos en último fichero XLS
Para que nuestro ejemplo funcione, debemos tomar el último fichero XLS (Alumnos 28022008.xls) y realizarle algunas modificaciones. En realidad debemos hacer sólo 2 modificaciones, a los alumnos con legajo 100 (Perez Mieguel Angel) y 102 (Juarez Martin Alejandro) y reemplazar en la columna “Curso” el valor 3 por el 2. Ello significa que esos alumnos repitieron de curso y las filas para esos 2 alumnos serán exactamente iguales en los archivos Alumnos 28022007.xls y Alumnos 28022008.xls


2. Agregar componente Script Task
El siguiente paso consiste en dirigirnos nuevamente al Package y en el Control Flow dentro del Foreach agregar un componente Script Task desde la ToolBox. Luego de agregado el componente, debemos unir su flecha verde con el Data Flow Task como muestra la siguiente figura.


Dentro de este Script, mediante código VB.Net obtendremos la fecha que figura en el nombre del fichero XLS que esté procesando en ese momento el Foreach.
Antes de mostrarles cómo configurar el Script, vamos a crear una variable llamada “varDate” de tipo DateTime, desde la solapa Variables del IDE.


Una vez creada la variable, hacemos click derecho sobre el Script Task para Editarlo. En la ventana de edición, seleccionamos la opción Script y y cliqueamos el botón Design Script…Esto abrirá una nueva ventana del VS en la cual sede quedar el código siguiente.

' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() ' Declaramos las variables que utilizaremos Dim vars As Variables Dim FileDate As String Dim varsDate As Variables 'asignamos a la variable local "vars" el contenido de la variable varFileName 'que obtiene el nombre del fichero XLS en el Foreach Dts.VariableDispenser.LockOneForRead("User::varFileName", vars) 'Procesamos la variable varFileName para obtener la fecha del nombre del fichero XLS 'con el formato dd/mm/aaaa FileDate = vars("varFileName").Value.ToString.Substring(vars("varFileName").Value.ToString.Length - 12, 2) + "/" + _ vars("varFileName").Value.ToString.Substring(vars("varFileName").Value.ToString.Length - 10, 2) + "/" + _ vars("varFileName").Value.ToString.Substring(vars("varFileName").Value.ToString.Length - 8, 4) 'instanciamos y asignamos a la variable varDate la fecha obtenida Dts.VariableDispenser.LockOneForWrite("varDate", varsDate) varsDate("varDate").Value = CDate(FileDate) Dts.TaskResult = Dts.Results.Success End Sub End Class

Nótese que el lenguaje utilizado es solamente VB.Net y se puede hacer Debug como cualquier aplicación en VS 2005.
Cerramos la ventana una vez que ingresamos el código, y damos Ok en la ventana de edición de Script Task. Con esto logramos que por cada vez que se recorra un fichero XLS del directorio correspondiente, la variable varDate tome el valor que figura en el nombre del archivo.

3. Agregar componente SCD
Llegó la hora de agregar el SCD (Slowly Changing Dimensión) que nos permitirá agregar los datos en la tabla TablaAlumnos conservando datos históricos. Para ello nos dirigiremos al Data Flow.
Antes de insertar el SCD será conveniente eliminar el OLE DB Destination existente, ya que el nuevo componente se encargará de agregar todos los componentes necesarios al finalizar de configurarlo.
Una vez eliminado el OLE DB Destination procederemos a insertar el SCD desde el ToolBox. Luego uniremos la flecha verde del componente Derived Column con el SCD. Haciendo click derecho, elegiremos la opción Edit…Allí nos aparecerá un asistente que nos permitirá configurar el SCD fácilmente.
En el primer paso daremos click en Next>, mientras que los demás pasos se detallan a continuación:

Paso Select a Dimension Table and keys
En este paso seleccionaremos la tabla, los campos a ser tenidos en cuenta para los cambios y la Key Business que en este caso será el campo Legajo. Damos Next > para ir al siguiente.


Paso Slowly Changing Dimensión Columns
Aquí es donde debemos especificar que columnas y qué tipo de cambios vamos a necesitar. Para nuestro ejemplo seleccionaremos todas las columnas disponibles y su Change Type como Historical Attribute. Damos Next > para ir al siguiente.



Paso Historical Attribute Options
Usaremos “Start and End Dates” para identificar los registros vigentes y expirados. Alli utilizaremos la variable varDate para setear los valores de los campos Registro_valido_desde y Registro_valido_hasta. Damos Next > para ir al siguiente.


Paso Infered Dimension Members
En este paso desmarcaremos el check box “Enable infered member support” y daremos click en Finish > para ir culminar con la configuración del SCD.
Para más información sobre SCD visite
http://msdn2.microsoft.com/en-us/library/ms141662.aspx

Una vez finalizado el asistente, se generarán una serie de componentes automáticamente, los que llevarán a cabo la tarea que nos propusimos al comienzo. El Package se verá de la siguiente manera.



4. Ejecutar el proceso y analizar los resultados
Ya estamos en condiciones de probar el Package, para ello les recomiendo borrar el contenido de la tabla TablaAlumnos para ver más claramente los resultados.
Ejecutemos el proceso presionando F5 y vayamos a ver el contenido de la tabla a la BD. El resultado debería ser el siguiente:


Analizando los datos de la tabla TablaAlumnos, podremos ver que contamos con 2 tipos de alumnos:
a) Los que pasaron todos sus cursos sin problemas año a año. Por ejemplo Legajo 101 Baresi Mateo, si analizamos sus datos veremos que en el año 2006 cursó 1º año, en el 2007 2º año y en el 2008 se encuentra cursando 3º año ya que el campo Registro_valido_hasta se encuentra vacío.


b) Los que repitieron el 2º año. Por ejemplo Legajo 100 Perez Miguel angel y analizando sus datos veremos que sólo tiene 2 registros y no tres como los que no repitieron de año. Veamos por qué. En el año 2006 cursó 1º y en el año 2007 cursó 2º año, el que todavía no ha concluido debido a que ha repetido el mismo.


Analizándolo desde el punto de vista técnico, podemos decir que el SCD detecta en este caso que existen registros exactamente iguales en los archivos Alumnos 28022007.xls y Alumnos 28022008.xls, y por ello no ingresa en la tabla el registro duplicado.
Aquí finaliza mi 2º post, espero les sea de utilidad y por cualquier duda o inquietud solo tienen que agregar comentarios. Saludos a todos!

SSIS – Ejemplo de Package con origen de datos Excel dinámicos

Estimados amigos lectores, en éste, mi primer post sobre SSIS, quiero presentarles un ejemplo que seguramente les será muy útil si están dando sus primeros pasos con esta herramienta de Microsoft. Intentaré explicar con un ejemplo sencillo como crear un Package de SSIS (SQL Server Integration Services) para importar los datos de todos los documentos XLS (MS Excel) que se encuentran en un directorio particular, procesarlos y luego enviar los datos obtenidos y procesados a una tabla de una Base de datos de SQL Server. En post posteriores iré ampliando este Package, agregando más transformaciones para complejizarlo y ajustarlo más a la realidad de la vida cotidiana, pero para empezar, creo que este ejemplo estará más que bien. Bueno, arranquemos…
El ejemplo constará de 3 partes con la finalidad de simplificar su comprensión. Estas son:

1. Crear Orígenes y destino de Datos
2. Creación del Package sin iteración
3. Creación de iteración para el Package

1. Crear Orígenes y destino de Datos
Lo primero que debemos hacer es contar con 3 archivos XLS (Excel) en una carpeta de nuestra PC. Les sugiero que utilicen los mismos nombres y path que yo para evitar problemas. Si miran la imagen de abajo, verán el path y los nombres de archivos. Por ejemplo:
C:\SSIS\Alumnos\Alumnos ddmmaaaa.xls
Respetar el formato del nombre será importante para futuros post de ampliación del ejemplo. Fíjense que lo único que difiere en ese nombre es el año.


La estructura (columnas) del los 3 archivos debe ser igual, no así los datos de los alumnos, los que sólo deben ser diferentes los valores de la columna Curso en cada uno de los archivos. Por ejemplo, el Alumno Baresi Mateo en el archivo Alumnos 28022006.xls tendrá en la Columna “Curso” el valor 1, en el archivo Alumnos 28022007.xls el valor 2, y en el archivo Alumnos 28022008.xls el valor 3. La estructura del XLS debe ser la siguiente:


La tabla donde se almacenarán los datos obtenidos y transformados para este ejemplo se encuentra en la base de datos Examples y se llama TablaAlumnos (Tener presente que tanto la BD como la tabla han sido creadas para este ejemplo). La estructura de la tabla sería la siguiente:


Donde RowID será la clave principal y autoincremetal.

2. Creación del Package sin iteración
Una vez que ya contamos con los archivos de “origen” de datos y la tabla de “destino” creada procedemos a abrir el SQL Server Business Intelligence Development Studio para comenzar a crear el proceso. Creamos un nuevo Proyecto de Integration Services, le ponemos de nombre IMPORT_XLS y lo guardamos dentro de la carpeta SSIS que creamos con anterioridad.


Luego de creado el proyecto, se nos abre en el IDE del VS el paquete por defecto, cuyo nombre es Package.dtsx. Allí es donde debemos apelar a nuestra creatividad y comenzar a “dibujar” nuestro proceso!!!
Lo primero que vamos a hacer es crear las conexiones de origen y destino para los datos. Para crear el origen de datos de archivos Excel nos situamos en el Administrador de Conexiones, allí hacemos lo siguiente:
- click derecho en la superficie, seleccionamos la opción New Connection…
- Seleccionamos la opción EXCEL, y hacemos clic en Add….
- Luego hacemos click en el botón Browse y seleccionamos el archivo deseado, en nuestro caso seleccionaremos el archivo Alumnos 28022006.xls ubicado en C:\SSIS\Alumnos
- Dejamos chequeada la casilla First row has column name, ya que nuestras columnas tienen encabezado.
- Hacemos click en el botón OK y ya habremos creado el “Origen” de datos. Se llamará Excel connection Manager.


Para crear el “Destino” de datos haremos lo siguiente:
- click derecho en la superficie, seleccionamos la opción New OLE DB Connection…
- en la ventana de configuración hacemos click en la opción New… para crear una nueva conexión a la BD.
- En la nueva ventana de Connection Manager debemos especificar los datos de nuestra conexión. Para nuestro ejemplo, los datos pueden observarse en la figura siguiente.

- Damos Ok en las dos ventanas y ya tendremos creada también la conexión de destino hacia la base de datos llamada localhost.Examples.

Ahora procederemos a la creación del Package propiamente. Lo primero que haremos es insertar en el Control Flow (Flujo de Control) un nuevo Data Flow task (Tarea de Flujo de Datos), arrastrando dicho objeto hacia la superficie que aún se encuentra vacía.
Posteriormente, hacemos doble click sobre el Data Flow task para poder agregarle la funcionalidad adecuada. Una vez dentro del Data Flow, agregamos una Excel Source, ubicada en la sección Data Flow Sources de la Toolbox arrastrándola a la superficie. Hacemos click derecho sobre el elemento agregado, elegimos la opción Edit… Allí nos aseguramos que la conexión seleccionada sea la que creamos con anterioridad y luego sólo seleccionamos la hoja del fichero XLS que contiene la información (en nuestro caso seleccionamos Hoja1$)


Lo siguiente que haremos es agregar el destino de datos, para ello arrastraremos el componente OLE DB Destination desde la Sección Data Flow destination. Una vez insertado en el Package, nos situamos sobre el Excel Source y arrastramos la flecha verde hasta el OLE DB Destination que acabamos de insertar.



Lo siguiente que debemos hacer es Editar el OLE DB Destination y donde debemos en la opción Connection Manager nos aseguramos que la conexión sea la que creamos con anterioridad (localhost.Examples) y seleccionamos la TablaAlumnos. Luego debemos ir a la opción Mappings para relacionar cada una de las columnas de Excel con sus respectivos campos en TablaAlumnos.



Es muy posible que les surjan errores de compilación debido a que no se pueden mapear campos con diferentes tipos de datos. En nuestro caso, los errores surgen en algunos campos. Lo que debemos hacer es fijarnos en el OLEDB Destination los tipos de datos que corresponden a cada campo (posicionando el cursor unos segundos encima del mismo) y luego agregar un nuevo componente para realizar el cambio en el tipo de datos.

El componente a agregar es Derived Column, con el cual podremos crear columnas derivadas con el nuevo tipo de dato. El Data Flow debe quedar como se muestra en la figura siguiente.



Luego debemos Editar el componente Derived Column insertado, agregando columnas derivadas para cada uno de los campos que requieren transformación, para ello vea la siguiente figura y observe que la propiedad Length de de la columna derivada “DerivedDivision” es 1. Eso es porque puede ir solo un carácter en este caso.



Una vez ingresadas estas las columnas, damos click en OK para confirmar estos cambios. Lo que resta en esta primera parte es volver a mapear los campos del destino con problemas de tipo de datos con las nuevas columnas derivadas que acabamos de crear. Para ello debemos editar el componente OLEDB Destination, ir hasta la opción Mappings y allí vincular las nuevas columnas con sus respectivos campos en el destino.


Si damos OK en la ventana, ya habremos concluido esta parte del ejercicio y podremos llevar a cabo la primera prueba. Presionamos F5 para que ejecutar el Package y una vez concluida la ejecución, si nos dirigimos a la tabla TablaAlumnos de la Base de Datos podremos ver los datos importados. Deberíamos tener los siguientes datos:




3. Creación de iteración para el Package

Vamos por buen camino, ya falta menos!!! Hasta ahora el proceso funciona correctamente pero con sólo un fichero XLS. Lo que haremos ahora es agregar las iteraciones para que recorra todo el directorio C:\SSIS\Alumnos, cargando de esa forma los datos de todos los ficheros en la tabla TablaAlumnos.

Debemos situarnos en el Control Flow donde agregaremos un Foreach Loop Container sobre la superficie donde ya se encuentra el Data Flow Task. Luego arrastramos éste último dentro del componente Foreach Loop Container.



Ahora debemos configurar el Foreach, para ello hacemos click derecho sobre el mismo, y elegimos Edit. En la ventana del editor nos situamos en la opción collections en donde debemos poner el path y el tipo de archivo que vamos a recorrer. Donde debemos poner “C:\SSIS\Alumnos” y “*.XLS” respectivamente.



En la opción Variable Mappings debemos agregar una nueva variable que se llamará varFileName, será de tipo String y su valor será el primero de los ficheros XLS que tenemos en el directorio “c:\SSIS\Alumnos\Alumnos 28022006.xls”


Lo último que resta es configurar la conexión para el origen de datos Excel Connection Manager que creamos con anterioridad. Para ello hacemos click derecho sobre él, Propiedades, y en Expressions asignamos la variable varFileName a las propiedades ExcelFilePath y ServerName.


Damos OK y listo! El proceso ya está concluido.

Si ejecutamos nuevamente el proceso (es conveniente eliminar el contenido de la tabla TablaAlumnos antes de cada importación para evitar duplicación de datos) veremos cómo han sido cargados los datos de los 3 ficheros XLS de la carpeta C:\SSIS\Alumnos.

Espero les haya sido útil este ejemplo y en sucesivos posts iré ampliando este ejemplo con más funcionalidades. Saludos a todos los lectores!