SSIS Cargando archivos Excel

 

Siempre hay quienes comienzan a hacer algo nuevo y este post está dedicado a estas personas, en concreto a Claudio. Un compañero de los foros de Integration Services en castellano que se preguntaba como podía conseguir resolver su proceso mediante paquetes ETL de SSIS.

 Escenario

el asunto es que yo tengo una consulta, esta consulta me trae unos datos los cuales los tengo que poner en un excel con la fecha de la generacion y enviarlo a una persona, la idea es crear un paquete que permita mediante algun proceso crear el archivo excel con el nombre del dia(ejemplo ventas_19-06-2012.xls), mas que nada seria para automatizar un par de tareas que debo realizar diariamente 

 

Una Solución

Por que siempre hay varios caminos para llegar al mismo sitio. Para este caso he sugerido utilizar una plantilla de libro Excel como destino de datos, que vamos a copiar con una tarea File System para generar un nuevo archivo con la fecha de la consulta que será el destino de los datos obtenidos mediante la misma (consulta)

Vamos con los detalles…

Es nuestro paquete ETL añadimos un DataFlow, con un origen OLEDB source con la consulta que vamos a realizar contra el origen de datos. Para esta entrada utilizaré la base de datos AdventureWorks para realizar esta consulta:

SELECT [ProductID],[Name],[ProductNumber],[MakeFlag],[FinishedGoodsFlag],[Color]FROM [Production].[Product]

En el archivo Excel que vamos a utilizar como plantilla creamos las columnas necesarias para mapear con los datos de origen:

 

Hay que tener clara la versión de Excel a utilizar, dependiendo de esto podremos utilizar el componente Excel Destination (hasta Excel 2007) o nos veremos obligados a utilizar OLEDB Destination en caso de Excel 2010. En este caso la versión es 2007.

Añadimos un componente Excel Destination. Conectamos la salida de datos del OLEDB Source a la entrada del destino Excel y editamos este componente para establecer el archivo destino y el mapeo de datos.

Creamos una nueva conexión que apuntamos al archivo plantilla:

 

Una vez finalizada la conexión realizamos el mapeo de datos entre las columnas del origen de datos y el archivo Excel:

 

Y finalizamos la edición del componente de destino. Hasta ahora fácil, no hemos hecho nada especial para generar archivos dinámicamente en función de la fecha pero hemos conseguido establecer los metadatos que necesitan los componentes en el flujo de datos.

En las propiedades de este componente (en el panel de propiedades, F4 teniendo seleccionado el componente Excel Destination), hay que cambiar la propiedad ValidateExternalMetadata a false. Es importante este cambio para poder ejecutar el paquete sin que exista previamente el archivo sobre el que vamos a volcar los datos:

 

El siguiente paso es cambiar algunas propiedades en el administrador de conexión del archivo Excel, en concreto vamos a poner la atención en Delay Validation, que vamos a establecer a True. Observar la propiedad ExcelFilePath, que modificaremos más adelante mediante una variable que proporcionará el nombre de fichero para cada ejecución a través de Expressions:

 

Nos volvemos a la pestaña de ControlFlow y creamos una variable para generar el nombre del archivo en cada ejecución. Para esto hay que asegurarse de que tenemos seleccionado el lienzo y no el componente DataFlow que añadimos antes, para que el ámbito de esta variable sea del alcance de todo el paquete. Para acceder al panel de variables puede hacer clic derecho sobre el lienzo y seleccionar la opción variables. Crearemos la variable vNombreArchivo de tipo string y sin asignación de valor:

 

Seleccionamos la variable y pulsamos F4 para mostrar su panel de propiedades. En la propiedadEvaluateAsExpression seleccionamos el valor True y en Expressions vamos a añadir una expresión que conformará el valor de esta variable

 

En el editor de expresiones generamos una para construir un nombre de fichero en función de la fecha de proceso. Yo he utilizado la siguiente:

?

1"C:\\Consultas2Excel\\Consulta_" + (DT_STR,20,1252) ( (DT_I8) (YEAR( @[System::StartTime] )) * 100000000 + MONTH(@[System::StartTime])*1000000 + DAY(@[System::StartTime]) * 10000 + datepart("hh",@[System::StartTime] ) * 100 + datepart("mi",@[System::StartTime] )   ) + ".xlsx"

 

Ya tenemos un nombre dinámico para nuestro fichero destino. Ahora solo falta que ese fichero exista y lo vamos a conseguir con una tarea File System Task que añadimos al ControlFlow y establecemos restricciones de precedencia, conectando la salida de ejecución correcta de File System Task con el DataFlow que ya teníamos:

 

Editamos la tarea File System para modificar sus propiedades. Lo primero es asegurar que la operación es Copy File. Establecemos los siguientes valores por propiedad

Propiedad Valor

Operation Copy File

IsDestinationPathVariable True

OverwriteDestination (evaluar la necesidad de sobreescribir el archivo de destino si existe en la operación de copia)

IsSourcePathVariable False

SourceConnection (Nueva conexión a nuestro archivo plantilla) c:\PlantillaDestino

 

En el panel de conexiones, buscamos la conexión a Excel que utiliza nuestro destino en el DataFlow y accedemos a las propiedades, en concreto Expressions y añadimos una expresión para la propiedadExcelFilePath. Escribimos @[User::vNombreArchivo]

 

Y listo!

Ya tenemos un paquete que lee datos de una consulta y lo vuelca en un fichero Excel nuevo que genera en cada ejecución:

 

 

Conclusión

Como también vimos en la entrada SSIS Dinamizando propiedades de componentes , el uso de variables facilita en gran medida operaciones de asignación dinámica de propiedades de componentes, tareas, DataFlow, ControlFlow… Además, podríamos utilizar configuraciones de paquetes (en package deployment model) o parámetros y entornos (project deployment model) para exponer estas variables y asignarles valor previa ejecución del paquete.

Si tienes cualquier duda o quieres obtener el paquete ETL  desarrollado durante esta entrada, deja un comentario.