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!