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
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 propiedad EvaluateAsExpression 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:
"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 propiedad ExcelFilePath. 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.
Excelente aporte Victor, muy bien detallado y era justo lo que buscaba, ahora tengo un problema y una consulta extra, la primera despues de terminar todo me da un error que no se como arreglar Dice: "Column X cannot convert between unicode and non-unicode string data types" asi pasa con todas las columnas y cuando busque revisar el mapping ya no lo puedo ver porque me da otro error que dice "Opening a rowset for"Excel Destination" failed. check" no entiendo porque segun una propiedad era para evitar que existiera el archivo y veo que en el patch de la conexion aparece el nuevo donde se va a alojar el archivo, lo unico distinto es que uso excel 2003 con respecto al tuyo. Y mi consulta era como puedo hacerlo un job que se ejecute mensual. en espera de tu pronta respuesta y muchas gracias de antemano
ResponderEliminarHola, gracias por tu comentario. Con respecto al problema con las columnas de tipo varchar, unicode y non-unicode, es que Excel espera por defecto colmnas de tipo nvarchar (unicode). Hay varias vias para solucionarlo, quizás la más sencilla sea utilizar el componente "Data conversion".
EliminarEn cuanto al error "Opening a rowset for 'Excel destination' failed", comprueba que has cambiado las dos propiedades del administrador de la conexión Excel: ValidateExternalMetadata=False; Delay Validation=True. Esta última cambiala también a nivel de DataFlow.
Un saludo,
Víctor
Hola, primero que todo dar las gracias por el detalle. muy bueno!!!!
ResponderEliminarquiero realizar unas preguntas si me puedes ayudar por favor:
1.- yo utilizado "OLE DB Source"->"Data Conversion"->"OLE DB Destination"..... en el "OLE DB Destination" le doy la direccion del excel 2007 donde quiero ingresar los datos de mi consulta realizada en "OLE DB Source" y mi "Data Conversion" realiza la conversion para que no me genere el error, pero al momento de abrir el excel con los datos de la consulta ya enviados, estos son todos tipo texto e inclusos los numericos(en el "Data Conversion" le digo que los convierta en numeric). que puedo hacer para que lleguen como numeros? (ya que esos datos necesito utilizarlos con tablas dinamicas en el excel 2007).
2.-alguna idea de generar un excel incluidas las tablas dinamicas?
si te das cuenta van relacionadas las dos preguntas.
ojala puedas orientarme frente a este reto, de ante mano muchas gracias.
Saludos!!!!!!!!!
Hola César,
Eliminar1. Para que lleguen los datos en el formato requerido será necesario que modifiques las propiedades de las columnas externas en el componente Excel Destination, en la edición avanzada, tienes las columnas de destino y su tipo de datos. Modifica esta configuración.
2. Crear una tabla dinámica en Excel es posible, pero no se me ocurre otra forma abriendo el libro a través del modelo de objetos y creando una pivot table programáticamente con un Script component modificado para que haga de destino. Las pivot tables son objetos que requieren la definición de un rango de datos y otros detalles. No existen ningún componente destino (que yo conozca) que realice esa función.
Un saludo,
Víctor.
Muchas Gracias!!!!
Eliminartodo excelente.
me puedes ayudar con una pregunta mas por favor.
Eliminarestoy dando una direccion de destino al archivo copiado fuera de mi pc, por ejemplo un servidor de datos. pero no me entrega dos errores:
-no encuentra la direccion ( la entego correctamente).
-me dice que la cadena de la expression es muy larga.
si conoces algo sobre estare pendiente.
saludos!!!!
Muchas gracias por tu colaboración ahora si rueda.
ResponderEliminary si se lee de 10 tablas de sql 2008, como seria para ponerlos en hojas diferentes pero en el mismo archivo de excel?
ResponderEliminarHola,
EliminarComo el archivo destino sería el mismo puedes utilizar la misma técnica, sólo que utilizando tantos componentes destino como hojas tengas que generar. También puedes dividir el proceso en varios DataFlows.... No sé si me pierdo algo.
Un saludo,
Víctor
Hola Victor
ResponderEliminarGracias por tu blog, necesito hacer algo parecido a lo que haces aquí pero los datos necesito filtrarlos desde la última fecha de ejecución del paquete hasta la actualidad. Como puedo obtener la última fecha de ejecución??
Gracias, un saludo
Hola,
EliminarSegún como quieras obtener esa fecha de ejecución, podría ser desde la fecha de modificación del último archivo generado, pero lo que me parece más sensato es guardar la fecha con la que se ejecuta el paquete en una tabla o en un archivo. Cada vez que ejecutes el paquete lees ese valor para utilizarlo como parámetro. Antes de finalizar la ejecución del paquete lo vuelves a actualizar con la fecha de ejecución actual.
Un saludo,
Víctor.
Hola Victor
EliminarLo que hice fue crear una nueva hoja excel donde guardo la fecha de ejecución pero al hacer la consulta para recuperar la fecha tengo un error de tipo de datos. Podrías orientarme como hacerlo?
Gracias, un saludo
Hola,
Eliminar¿Cuándo sucede el error? Si es al leer el archivo Excel debes tener en cuenta el tipo de dato configurado para la celda y comprobar que en el administrador de conexión en SSIS se corresponde.
Si puedes dar más detalles del error quizás pueda orientarte mejor.
Un saludo.
necesito consultarte algo. Tengo un problema cuando actualizo una celda desde SSIS. Suponiendo que en la celda A1 tengo el valor 1, y en la A2 la formula "=A1+1". Naturalmente al visualizarlo veré el valor 2.Ahora si yo actualizo la celda A1 Con el valor 5 de SSIS y automáticamente realizo una lectura de la celda A2 el valor que obtengo sigue siendo el 1 y no el 6. Esto debe ser porque el motor de excel no esta en funcionamiento por ende no actualiza los campos. Pero mi pregnuta: ¿Existe una manera de solucionar este asunto? ¿A alguien le paso? Si pueden ayudarme se los agradeceré mucho. Saludos y muchas gracias
ResponderEliminarHola Fernando,
EliminarLa verdad es que no me he encontrado con este caso, personalmente. Suponía que Excel tenía la opción de calcular las formulas antes de guardar, pero si no lo hace puedes intentarlo hacer manualmente con un script task. Hay varios objetos que tienen el método .Calculate según el ámbito al que quieras llegar. Échale un vistazo a esta URL http://msdn.microsoft.com/en-us/library/office/bb687891.aspx
Un saludo.
Hola Victor, como estas??
ResponderEliminarOye, alguna vez se te ha presentado éste error? Unable to open Step output file. The step failed.
Al momento de ejecutar la tarea la ejecuta "correctamente", pero en el paso 1 (el unico) se detiene y marca ese error. Ya revise todos los permisos y no se que pueda ser.
O talvez no estoy atacando el problema como debe ser...
saludos!!
Hola,
EliminarParece como si estuvieras ejecutando el paquete desde el agente, el mensaje puede indicar que el fichero que está intentando abrir no existe o está siendo utilizado por otro proceso. Es posible que tengas configurado un log para el job? si es así intenta cambiar el nombre del archivo para comprobar que puede crear uno nuevo.
Un saludo
buenos dias me puedes facilitar por favor el paquete que se genero aca
ResponderEliminarmuchas gracias... heleduat@gmail.com
Hola Victor.
ResponderEliminarLa primera linea (la cabcera) de mi template es azul, el resto no. Sin embargo todas las filas de mi excel final copian el mismo formato de la cabacera
Sabes como puedo arreglas eso?
Victor. excelente aporte. como lo descargo
ResponderEliminarexcelente
ResponderEliminarhola:
ResponderEliminarhe estado siguiendo los pasos de la exportacion a Excel pero no me trae ningun dato, bueno, el data source es una consulta que he colocado y cuando pongo un data viewer no salen datos, sin embargo, cuando hago un preview si aparecen los datos... que puede ser? por cierto, estoy usando una funcion para obtener la fecha de hoy (GetDate()) tendrá algo que ver
Saludos
Hola,
EliminarTe refieres a la consulta de origen? ¿Estas filtrando y pasando algún parámetro? El uso de la función GetDAte() o cualquier otra no es un problema, he realizado consultas bastante complejas en las que se utilizan variables de tipo tabla, CTEs, etc.. Mientras sea capaz de identificar los metadatos irá bien.
Intenta poner un visor de datos despues del componente de origen a ver si están saliendo filas... Ya me contarás, y disculpa la demora en la respuesta ;)
como se haría si el archivo final que se debe generar es un csv? gracias y bueno el blog
ResponderEliminarHola,
EliminarEl mecanismo seria muy similar, sólo que utilizando como destino Flat File Destination. Tendrías que configurar el Administrador de conexión para utilice delimitadores como punto y coma para separar las columnas y listo. La forma de dinamizar la cadena de conexión es similar, también puedes cambiarla a través de expresiones.
Un saludo!
Víctor lo que quiero hacer es agregar un for each para leer archivos de Excel como origen de una carpeta y pasarlo a destino ole de sql server tendría que crear 3 variables? para que Excel source lea automáticamente?, ayúdame con esto por favor
ResponderEliminarGracias amigo, eres un capo !!!
ResponderEliminarexcelente post
ResponderEliminarExcelente post, gracias por tu ayuda
ResponderEliminarHola Victor.
ResponderEliminarAntes que todo, agradecer por el excelente post. Dicho esto, quisiera, por favor, me puedas ayudar con la siguiente consulta: cómo hago para recuperar el valor de la variable donde almacené la ruta del archivo?
Seguí cada paso y me funcionó todo perfectamente; sin embargo, luego de generar el archivo (con nombre variable) y ejecutar algunos Task adicionales, tarto de desplegar (con messageBox) el valor en la variable "vNombreArchivo" y el script task retorna un error que indica que el elemento no se encuentra en la colección u objeto contenedor.