SQL Server Integration Services (SSIS) FAQ
|
Este Artículos no está Cerrado. Si el contenido de este Artículo te resulta de interés, considera visitarlo más adelante y así poder beneficiarte de sus ampliaciones y correcciones. Seguimos trabajando para mejorar el contenido de este Artículo.
|
|
|
La desaparición de los antiguos Paquetes DTS por la nueva ETL de Microsoft (los Paquetes DTSX), SQL Server Integration Services (SSIS), ha sido uno de los cambios más importantes en Microsoft SQL Server 2005, y dónde adquirimos una ventaja competitiva frente a otros fabricantes de base de datos. En este Artículo, intentaremos resolver las preguntas más frecuentes sobre Microsoft SQL Server Integration Services (SSIS) para aquellos que se inician... y por supuesto, en Castellano !! (ante todo que se entienda... jeje ;-)
Este Artículo es de utilidad para el examen 70-445 y el examen 70-446 de las certificaciones MCTS y MCITP de Business Intelligence.
|
SQL Server Integration Services (SSIS) funciona como una pequeña base de datos, y como tal, tiene sus propios tipos de datos. Estos tipos de datos, se infieren a su vez de los tipos de datos existentes en los orígenes y destinos de datos (ficheros Microsoft Excel, bases de datos Microsoft SQL Server, etc.). Es muy importante saber si trabajamos con datos de tamaño fijo o con datos de tamaño variable. Esto es debido a que muchas tareas, como por ejemplo Merge, Merge Join, Lookup, etc., al realizar comparaciones entre dos valores, pueden producirse resultados no deseados. Así, si estamos leyendo texto de Microsoft Excel, SSIS tomará por defecto valores UNICODE de hasta 250 caracteres de longitud, pero al hacer un Merge Join con un campo NCHAR(10) de Microsoft SQL Server podríamos obtener un resultado no deseado por los caracteres blancos existentes a la derecha (sería necesario hacer un TRIM, por ejemplo con una tarea Derived Column). En esta situación, no se tomaría como iguales los valores del JOIN, y podríamos pensar que existe algún error o bug. No, simplemente, así es la naturaleza de los datos de texto de tamaño fijo y de tamaño variable.
Esta es una situación muy típica al realizar procesos de carga con SSIS que se ejecutan periódicamente. Por ejemplo, si estamos cargando la facturación de una empresa, queremos que sólo se inserten los nuevos clientes, pues al intentar insertar un cliente existente se produciría un error de violación de clave.
Principalmente, tenemos de formas de afrontar este problema con SSIS.
- Utilizando la tarea Merge Join. Esta tarea permite realizar un Join entre dos Flujos de datos de SSIS. A diferencia de la tarea Merge, Merge Join permite realizar no sólo un INNER JOIN, sino también un LEFT OUTER JOIN o un FULL OUTER JOIN.
La tarea Merge Join, y al igual que la tarea Merge, sólo tiene dos entradas (es posible utilizar varias tareas Merge o Merge Join en cascada). Es requisito que sus entradas estén ordenadas, para lo cual, se suele utiliza tareas Sort.
En el caso que nos ocupa, deberemos utilizar un LEFT OUTER JOIN. Seguidamente, utilizaremos una tarea Conditional Split, que nos permita definir una salida con sólo los nuevos clientes, siendo esta salida la que conectaremos con nuestro destino de datos. La condición que utilizaremos en la nueva salida será algo como "ISNULL(ClienteID)".
Finalmente, el Flujo de Datos (Data Flow), quedará como se muestra en la siguiente imagen.
- Utilizando la tarea Lookup. Esta tarea permite hacer una búsqueda (Lookup) sobre otra tabla u origen de datos, permitiendo devolver un valor asociado. Un ejemplo de caso de uso típico es el siguiente: si estamos cargando en SQL Server un fichero de empleados en el que viene el código de la categoría profesional del empleado, pero que no incluye el nombre o descripción de dicha categoría, se podría utilizar la tarea Lookup para hacer una búsqueda sobre la tabla de Categorías, y obtener de la misma la descripción de la categoría para cada empleado.
En el ejemplo de las Facturas, al cargar la tabla de Clientes, podemos utilizar una tarea Lookup para obtener de la tabla Clientes de nuestro Data Warehouse, el código del mismo. De este modo, si obtenemos como código NULL, será porque el cliente es nuevo !!.
Aquí existe un problema: la tarea Lookup por defecto, sólo funciona si para cada fila encuentra otra fila en la tabla de búsqueda. En caso contrario se produce el error "Row yielded no match during lookup". Por lo tanto, no podríamos utilizar la tarea Lookup para nuestros fines.
Sin embargo, es posible alterar la configuración de la salida de error de la tarea Lookup. Para ello, editar la tarea Lookup (click con el botón derecho sobre la tarea Lookup, y después click Edit). En el diálogo Lookup Transformation Editor, click sobre el botón Configure Error Output. A continuación, en el diálogo Configure Error Output, modificar su configuración como se muestra en la siguiente imagen de ejemplo.
Realizado esto, es necesario utilizar una tarea Conditional Split, que nos permita definir una salida con sólo los nuevos clientes, siendo esta salida la que conectaremos con nuestro destino de datos. La condición que utilizaremos en la nueva salida será algo como "ISNULL(ClienteID)".
Como conclusión, podemos comentar lo siguiente:
- Utilizando la tarea Merge Join. Es la manera natural de resolver el problema que nos ocupa. Aunque el desarrollo del Data Flow se hace más complejo, se da un uso apropiado a las tareas utilizadas.
- Utilizando la tarea Lookup. Resulta más fácil y rápido el desarrollo del Data Flow. Sin embargo, implica realizar un uso no apropiado de la tarea configuración de la salida de error.
En principio, si es posible, pero debemos tener en cuenta que es probable encontrarnos con diferentes errores, tanto en tiempo de ejecución como en tiempo de diseño. Esto depende principalmente de que tipo de modificación de esquema estemos realizando, ya que el propio Paquete DTSX almacena metadatos sobre las fuentes de datos (orígenes y destinos) y sobre las distintas tareas utilizadas en cada Flujos de Datos (Data Flow).
La recomendación, es intentar mantener los Paquetes DTSX actualizados con sus Metadatos congruentes con el esquema de las bases de datos subyacentes, es decir, si alteramos el esquema de la base de datos (por ejemplo, por necesidad de una aplicación que no tiene nada que ver con SSIS), editar los Paquetes DTSX para corregir las advertencias y errores que puedan haberse generado por el cambio. Si no podemos permitirnos este trabajo, al menos tendremos que hacerlo así para aquellos cambios que impliquen errores en tiempo de ejecución de los Paquetes DTSX.
Por ejemplo, si tenemos un paquete DTSX de SSIS funcionando, y agregamos un campo a uno de sus orígenes de datos, el paquete DTSX seguirá funcionando correctamente. Sin embargo, si posteriormente editamos dicho Paquete DTSX, obtendremos una advertencia en tiempo de diseño (The external metadata column collection is out of synchronization with the data source columns. The column "MiColumna" needs to be added to the external metadata column collection.), como se muestra en la siguiente imagen:
Esta situación se soluciona fácilmente, editando dicho origen de datos (ej: click con el botón derecho sobre el origen de datos, y después click Edit), y sobre la página Columns desmarcamos o marcamos las nuevas columnas, en función de si deseamos incluirlas en el Flujo de Datos (Data Flow). Finalmente, click OK para cerrar el diálogo editor del origen de datos.
Sin embargo, en otras ocasiones nos podemos encontrar con el siguiente error:
Error at Nombre_del_DataFlow [DTS.Pipeline]: The index is not valid.
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0048004 (Microsoft.SqlServer.DTSPipelineWrap)
|
En concreto, este error también se produce al intentar editar un origen de datos en un Flujo de Datos (Data Flow) de SSIS después de haber agregado columnas a la tabla subyacente, mostrándose el siguiente diálogo:
Para solucionarlo, click OK sobre dicho diálogo. Después, volvemos a editar el origen de datos, seleccionamos la pestaña Columns, deseleccionamos la nueva Columna, y click OK para cerrar el diálogo editor del origen de datos.
Otros cambios, como por ejemplo alterar una columna VARCHAR a INT en un destino de datos, puede provocar un error en tiempo de ejecución, y adicionalmente, al intentar modificar el correspondiente paquete DTSX, nos encontraremos con la advertencia producida por la falta de sincronización entre el esquema de la base de datos y los metadatos del paquete DTSX de SSIS.
SSIS ofrece dos posibles almacenes dónde guardar nuestros Paquetes DTSX de SSIS 2005 (Package Store y SQL Server), pudiendo adicionalmente utilizar el propio sistema de ficheros para almacenar Paquetes DTSX:
- File System. Es la única opción posible durante el desarrollo. Da igual que se trate de una unidad local del equipo utilizado para el desarrollo, o que sea una unidad de red.
Una gran ventaja es que Business Intelligence Development Studio (BIDS) se puede integrar con Source Safe, y así disponer de un control de versiones en nuestro proyecto de Integration Services, que además facilita compartir el código con distintos miembros de un equipo de trabajo.
Una desventaja, es que el servicio de Integration Services sólo es capaz de gestionar los paquetes almacenados en SQL Server (MSDB) o en el Package Store, como acontinuación se explica.
Por último, aunque hemos dicho que BIDS sólo trabaja con Paquetes DTSX almacenados en el File System, también es cierto que BIDS nos permite importar y exportar Paquetes DTSX. Para exportar un Paquete DTSX utilizaremos la opción Guardar Copia de Paquete Como (Save Copy of Package As). Esta opción la podemos encontrar en BIDS, una vez que hemos abierto el Paquete DTSX deseado, desde el menu Archivo (File). Del mismo modo, para importar un Paquete DTSX desde BIDS, desde la ventana Explorador de Soluciones (Solution Explorer), nos posicionaremos sobre el nodo Paquetes SSIS (SSIS Packages), click con el botón derecho sobre dicho nodo, y click sobre la opción Agregar Paquete Existente (Add Existing Package).
- Package Store (También es File System, pero gestionado por SSIS, no una ruta cualquiera). Este almacén de paquetes DTSX si es gestionado por el servicio de Integration Services. Siempre que importemos un paquete a esta ubicación, se depositará por defecto en C:\Program Files\Microsoft SQL Server\90\DTS\Packages, salvo que la instalación de SSIS se haya realizado sobre una ruta distinta de la de por defecto. Es posible crear una jerarquía de subcarpetas, sobre las cuales importar nuestros paquetes DTSX. En cualquier caso, como parte de nuestro Plan de Contingencias, deberemos contemplar hacer Backups del contenido completo de esta carpeta del sistema de ficheros.
- SQL Server (en MSDB, también se podría considerar como Package Store). Este almacén de paquetes DTSX si es gestionado por el servicio de Integration Services. Consiste en guardar los paquetes DTSX dentro de la tabla sysdtspackages90 de la base de datos MSDB. Una ventaja de utilizar MSDB, es el hecho de poder realizar los Backups y Restores junto con el resto de bases de datos de SQL Server, y delegar el problema de la encryptación de los datos sensibles de los Paquetes DTSX (ej: las contraseñas de las conexiones) a SQL Server, en vez de encriptar con User Key o con Password. Es recomendable establecer el valor de la propiedad ProtectionLevel a ServerStorage. Adicionalmente, existen varios roles de base de datos que permiten conceder distintos niveles de privilegio.
Como vemos, para el servicio Integration Services sólo podemos elegir entre las opciones Package Store y SQL Server (MSDB). Por el contrario, con BIDS sólo podemos trabajar sobre File System.
Es posible Importar y Exportar paquetes DTSX entre el sistema de ficheros (File System) del entorno de desarrollo, y otros sistema de ficheros (File System), el Package Store o SQL Server (MSDB), utilizando el comando de consola dtutil.exe. La utilidad dtutil.exe permite copiar, mover, borrar o verificar Paquetes DTSX, así como crear subcarpetas, eliminarlas, etc. Muchas de las tareas que se pueden realizar con dtutil.exe, también se pueden realizar desde SQL Server Management Studio (SSMS).
De forma adicional a la utilización de dtutil, también podemos utilizar la utilidad de implementación (deployment utility) para de forma gráfica (mediante un asistente) publicar nuestros Paquetes DTSX en SQL Server o en el File System. Para poder utilizar esta utilidad, en las propiedades del Proyecto de SSIS, establecer el valor de la propiedad CreateDeploymentUtility a True y establecer la carpeta en que se desea generar la utilidad de implementación (esto es la propiedad DeploymentOutputPath). Al generar nuestro proyecto, se creará en la carpeta especificada una copia de los Paquetes DTSX y un fichero de Manifiesto. Para ejecutar la utilidad de implementación, ejecutar (doble-click) el fichero de Manifiesto, y el asistente de implementación se ejecutará.
Es posible cambiar la Instancia de SQL Server (base de datos MSDB) utilizada por SSIS 2005 (por defecto se utiliza la instancia por defecto), una situación típica en caso de tener en un mismo servidor múltiples instancias de SQL Server. Del mismo modo, también es posible cambiar la ruta del Package Store utilizada por SSIS 2005, por ejemplo, si queremos utilizar un disco diferente del empleado en la instalación de producto. Para realizar estas configuraciones es necesario modificar el fichero de configuración de SSIS 2005 (MsDtsSrvr.ini.xml), que por defecto es C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml.
También es posible agregar más ubicaciones raíz de almacenamiento en SSIS 2005 (ya sean Package Store o SQL Server), de tal modo, que además de tener las dos por defecto, podamos añadir otras ubicaciones (ya sean de tipo SQL Server - MSDB - o de tipo Pacage Store - File System). Esto también se realiza modificando el fichero de configuración de SSIS 2005 (MsDtsSrvr.ini.xml), agregando tags de tipo Folder por dentro del tag TopLevelFolder, como se muestra en el siguiente ejemplo.
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System Alternativo</Name>
<StorePath>..\Packages_Alternativo</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>
|
Una ventaja de esta configuración, es permitir utilizar múltiples Instancias de SQL Server a SSIS 2005 (o múltiples File Systems) para simular múltiples entornos (ej: Desarrollo, Pruebas Integradas, Pre-producción, etc.), pudiendo realizar la operativa de paso entre entornos mediante la exportación e importación de paquetes DTSX entre los distintos almacenes.
A diferencia del motor de base de datos, que permite instalar múltiples instancias de SQL Server en una misma máquina, con SSIS 2005 sólo es posible tener una única instancia del servicio Integration Services en una misma máquina.
Si deseamos disponer de múltiples instancias de SSIS 2005, tenemos las siguientes alternativas:
- Utilizar múltiples máquinas, y en cada máquina instalar una instancia de SSIS 2005.
- Utilizar una única máquina, pero utilizar múltiples ubicaciones raíz para el almacenamiento de paquetes (Package Store). Podemos realizar los pasos entre entornos exportando e importando los paquetes DTSX entre los distintos almacenamientos de paquetes (Package Store). Para crear múltiples ubicaciones raíz de almacenamiento de paquetes, es necesario modificar el fichero de configuración de SSIS: MsDtsSrvr.ini.xml.
- Utilizar una estrategia mixta. Por ejemplo, una máquina para Desarrollo, Pruebas y Pre-Producción, con una instancia de SSIS 2005 y múltiples almacenamientos raíz de paquetes, y otra máquina con una instancia de SSIS 2005 para Producción.
SSIS 2005 no es una aplicación Cluster-aware, es decir, no está preparada por instalarse y configurarse en un Microsoft Cluster (MSCS) y ofrecer alta disponibilidad.
A continuación, se incluye una dirección de TechNet en la que se explica el procedimiento a seguir, si deseamos construir manualmente una instalación de SSIS 2005 en Cluster, algo que aunque es posible, no es una recomendación de Microsoft.
Configuring Integration Services in a Clustered Environment
Antes de iniciar la una configuración de SSIS en Microsoft Cluster (MSCS), leer detenidamente el contenido de la anterior dirección.
Dentro del Control Flow de un Paquete DTSX, podemos utilizar una tarea de tipo ActiveX Script Task, en la cual debemos establecer las siguientes propiedades:
- Propiedad Language. Elegir el lenguaje empleado en nuestro Script (ej: VBScript).
- Propiedad Script. Deberemos escribir el Script que deseamos ejecutar. Es muy importante que el código aquí incluido se deberá codificar en funciones, es decir, que no debemos escribir el código tal cual, sino escribir una función principal (ej: Function Main()), y dentro de la misma el código que deseamos ejecutar.
- Propiedad EntryMethod. Esta propiedad debe contener el nombre de la función principal (ej: Si la función principal es Function Main(), estableceremos el valor de esta propiedad a Main) que hemos utilizado en nuestro código.
Habitualmente, resulta muy útil poder acceder a variables del Paquete DTSX desde el ActiveX Script, para lo cual, utilizaremos la colección DTSGlobalVariables, pudiendo especificar qué variable deseamos acceder, como por ejemplo DTSGlobalVariables("MiVariable").Value. De este modo, podemos leer el contenido de una variable del Paquete DTSX, o bien establecer un valor a dicha variable.
A continuación se muestra un ejemplo didáctico, de un Script en Visual Basic, capaz de mostrar en una ventana de diálogo el contenido de la variable MiVariable (suponemos que existe dicha variable):
Function Main()
MsgBox(DTSGlobalVariables("MiVariable").Value)
End Function
|
Un problema habitual al utilizar VBScript en Paquetes DTSX de SSIS, es que al escribir o generar código VB (ej: Visual Basic 6) ó VBA (ej: Macro Microsoft Excel XP), este código aprovechando las referencias de su proyecto a las librerías correspondientes, tiene visibilidad de las constantes que se utilizan por los métodos en las clases. Sin embargo, en un fichero VBScript (o en una tarea ActiveX Script Task) es necesario establecer los valores literales y nos las constantes, pues éstas últimas no podrán ser resueltas y el código no funcionará.
En cualquier caso, la recomendación es utilizar la tarea Script Task (Scripting utilizando .Net Framework), ya que además de la ventaja de la riqueza propio de .Net Framework, se dispone de un diseñado gráfico para escribir el código (algo que no tiene la tarea ActiveX Script Task, en la cual el código lo escribimos en una enorme y simple caja de texto) dónde disfrutaremos de IntelliSense, del Object Browser, etc.
La respuesta es SI. Más aún, el método natural de agregar un Script a un Paquete DTSX es utilizando una tarea Script Task, he incluyendo en la misma código .Net Framework. Del mismo modo que era muy habitual utilizar VBScript (o similares como JScript) en los Paquete DTS de SQL Server 2000, con SSIS lo natural es utilizar .Net Framework en tareas Script Tasks (aunque podamos utilizar tareas ActiveX Script Task para incluir código VBScript). Con ésto, podemos aprovechar el diseñador gráfico para escribir código, con IntelliSense, el Object Browser, la riqueza de .Net Framework, etc.
Dentro del Control Flow de un Paquete DTSX podemos utilizar una tarea de tipo Script Task, en la cual deberemos especificar habitualmente las siguientes propiedades:
- ReadOnlyVariables y ReadWriteVariables. Para poder acceder a variables de nuestro Paquete DTSX, deberemos especificarlas en estas propiedades (en una o en otra propiedad, en función del tipo de acceso que deseemos realizar), separadas por comas. Si no lo hacemos así obtendremos el error "The script threw an exception: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there".
- El botón DesignScript. A través de éste botón, podemos acceder al editor de código en el cual editar y escribir el código .Net Framework para nuestra tarea.
Algo muy habitual dentro de un Paquete DTSX es acceder a las variables del paquete. Además de utilizar las propiedades ReadOnlyVariables y ReadWriteVariables para especificar a qué variables deseamos acceder, desde nuestro código podemos utilizar la colección Dts.Variables para acceder a las variables del Paquete DTSX, como por ejemplo Dts.Variables("MiVariable").Value.
También puede resultar interesante lanzar o forzar un error (raise error), por ejemplo ejecutando Dts.Events.FireError(0, "Comprobando Fecha de la Excel y Fecha del Nombre del Fichero", "Las fechas no coinciden", "", 0).
Existen distintas formas de ejecutar un Paquete DTSX de SSIS, cada una de las cuales puede presentar ciertas ventajas o inconvenientes según en el caso particular en que la deseemos aplicar.
- Ejecutar un Paquete DTSX desde Business Intelligence Development Studio (BIDS). Desde BIDS podemos crear nuestros Proyectos de SSIS, que estarán formados por nuestros Paquetes DTSX. Estos Paquetes DTSX se almacenarán en el File System, y NO se almacenarán ni en SQL Server (MSDB) ni en el Package Store. Como mucho, podremos integrar BIDS con Visual Source Safe, o en todo caso, desplegar o publicar los Paquetes DTSX del Proyecto en un Package Store, en un servidor SQL Server, o en otro File System. La principal ventaja de BIDS, es que al tratarse de la herramienta de desarrollo de SSIS, podemos ver de forma gráfica la ejecución del Paquete DTSX, incrustar Lectores de Datos para ver los datos que se mueven por nuestros Flujos de Datos (Data Flows), etc. Es muy cómodo, pero sólo se trata de una herramienta de desarrollo y se limita a la ejecución de Paquetes DTSX almacenados en el File System.
- Ejecutar un Paquete DTSX con la utilidad dtexec. La utilidad dtexec es un comando de consola (es decir, a lo MS-DOS) que permite ejecutar un paquete, independiente de que esté almacenado en el File System, en SQL Server, o en el Package Store. En caso de necesidad, podemos plantearnos la posibilidad de ejecutar dtexec desde el procedimiento almacenado del sistema xp_cmdshell, con lo cual, podríamos ejecutar un Paquete DTSX desde un simple Procedimiento Almacenado o desde un bloque e código Transact-SQL (recordar que por defecto, xp_cmdshell viene desactivado por seguridad, y deberá ser activado con sp_confire o Surface Area Configuration Tool).
Cabe la posibilidad de ejecutar dtexec desde una tarea programada de Windows, desde otra herramienta utilizada para planificación de tareas, o bien, también se puede invocar manualmente (ejecutando un fichero BAT) o desde otra aplicación o proceso.
Podríamos ver a dtexec como la herramienta que sustituye a la utilidad dtsrun, que se utilizaba para el mismo propósito con SQL Server 2000.
Entre sus muchas posibilidades, está poder establecer valores a las variables del Paquete DTSX, actualizar las cadenas de conexión utilizadas por los Connection Managers del Paquetes, especificar ficheros de configuración, la contraseña de decriptación si el Paquete DTSX fué guardado cifrando la información sensible, y un largo etc. Es decir, todas las opciones que se le pueden indicar a un Paquete DTSX para su ejecución, se le pueden indicar utilizando dtexec.
La realidad, es que la utilidad dtexec junto a la utilidad dtutil, hacen un equipo perfecto !!, pues esta última permite gestionar SSIS (mover Paquetes, copiarlos, borrarlos, etc.).
Cabe destacar, que en una máquina de arquitectura x64, la instalación de SSIS instalará dos versiones de dtsexec: la de 32-bit y la de 64-bit.
Requiere instalar SSIS en la máquina en la que se desea utilizar dtexec.
- Ejecutar un Paquete DTSX con la utilidad dtexecui. La utilidad dtexecui es una interfaz gráfica, que permite ejecutar un paquete independiente de que esté almacenado en el File System, en SQL Server, o en el Package Store. Su principal ventaja es que permite de forma sencilla especificar todas las opciones necesarias para ejecutar un Paquete DTSX, ofreciendo todas las opciones de dtexec pero de forma gráfica, incluso permite generar la línea de comandos para dtsexec desde las opciones que tengamos especificadas (vamos, que nos sirve de chuleta).
- Ejecutar un Paquete DTSX desde un Job del Agente de SQL Server. Desde un JOB del Agente de SQL Server, podemos añadir un paso del tipo SQL Server Integration Services Package, y en las propiedades de dicho paso, podemos especificar todas las opciones necesarias para ejecutar el Paquete DTSX, del mismo modo que lo haríamos con dtexecui. Sin embargo, el Agente de SQL Server nos permitirá planificar la ejecución de nuestro Paquete DTSX, y disfrutar de todas las ventajas que ofrece el Agente de SQL Server (Alertas, Operadores, etc.). Permite ejecutar un paquete independiente de que esté almacenado en el File System, en SQL Server, o en el Package Store.
Requiere instalar SSIS en la máquina SQL Server en la que se desea ejecutar el JOB..
Se debe garantizar que el usuario utilizado en las Credenciales (Credentials) de la cuenta Proxy empleada en el paso del JOB, tiene los suficientes permisos (ej: acceso al sistema de ficheros, conexiones de base de datos, etc.) para la ejecución del Paquete DTSX.
- Ejecutar un Paquete DTSX desde Programación. Es posible desde una aplicación .Net Framework (Windows Formas, ASP.Net, XML Web Service, etc.), utilizar el modelo de objetos de SSIS (SSIS Object Model), y con unas pocas líneas de código lograr nuestro objetivo. Necesitaremos utilizar las clases de Microsoft.SqlServer.Dts.Runtime, para poder cargar el Paquete DTSX (método LoadPackage) desde dónde se encuentra almacenado (File System, Package Store o SQL Server) y después ejecutarlo (método Execute). Puede encontrarse más información y ejemplos en los Libros en Pantalla (BOL - Books On Line). Requiere de un desarrollo .Net Framework 2.0, y además requiere instalar SSIS en la máquina en la que se desean ejecutar los Paquetes DTSX.
De forma alternativa, desde una aplicación .Net Framework sería posible ejecutar la utilidad dtexec.exe, y así también lograr nuestro objetivo. Otro truquillo sería ejecutar un Job del Agente de SQL Server que a su vez ejecute el Paquete DTSX que deseamos, pudiendo ejecutar dicho JOB desde código Transact, o a través del modelo de objetos de SQL Server (SMO - SQL Server Management Objects).
Como conclusión, tenemos varias maneras de ejecutar nuestros Paquetes DTSX, y para gustos, hay colores. Yo personalmente desarrollo con BIDS (evidentemente) y prefiero ejecutar los Paquetes DTSX con el Agente de SQL Server y dejarlos almacenados en SQL Server (es decir, en MSDB).
En cualquier caso, debemos tener en cuenta que al desarrollar en un equipo y mover el Paquete DTSX desarrollado a otro equipo (o incluso almacenarlo en SQL Server), podemos encontrarnos con problemas relativos a seguridad, en particular, al valor de la propiedad ProtectionLevel. Quizás, sea este el principal detalle a tener en cuenta al elegir con qué método ejecutar nuestros Paquetes DTSX, pues nos podemos encontrar que nuestros Paquetes DTSX no se ejecuten y se produzca el siguiente error:
Error loading PackageName: Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state."
You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
Un error muy habitual al empezar a trabajar con los Paquetes DTSX de SSIS, es que después de haber desarrollado nuestro proceso de ETL, y tras haberlo probado con éxito una y otra vez desde Business Intelligence Development Studio, al ejecutar el Paquete DTSX desde un entorno distino (Pruebas Integradas, Producción, etc.), y en consecuencia en otra máquina y/o con otro usuario, se produce el siguiente error y el Paquete DTSX no se ejecuta:
Error loading PackageName: Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state."
You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
Habitualmente esto es causa del valor de la propiedad ProtectionLevel del Paquete DTSX. Esta propiedad está concebida para proteger la información sensible del Paquete DTSX, siendo un ejemplo habitual de información sensible la contraseña de una conexión, por motivos evidentes de seguridad. Los valores posibles son:
- Do not save sensitive (DontSaveSensitive). Esta opción implica que no se guardará la información sensible. Si volvermos a abrir el Paquete DTSX desde BIDS, deberemos volver a especificar el valor de los datos sensibles.
- Encrypt all with password (EncryptAllWithPassword). Esta opción implica que se encripta el Paquete DTSX completo, utilizando para la encriptación, un clave especificada por el usuario, como si se tratase de una contraseña. Si volvermos a abrir el Paquete DTSX desde BIDS o si queremos ejecutarlo (ej:con dtexec), deberemos especificar la password para poder recuperar el Paquete DTSX.
- Encrypt all with user key (EncryptAllWithUserKey). Esta opción implica que se encripta el paquete completo, utilizando para la encriptación, un clave basada en el perfil de usuario. Sólo el mismo usuario utilizando el mismo perfil, puede volver a cargar el Paquete DTSX.
- Encrypt sensitive with password (EncryptSensitiveWithPassword). Similar a EncryptAllWithPassword, pero en este caso, sólo se encripta la información sensible del Paquete DTSX. Utiliza DPAPI.
- Encrypt sensitive with user key (EncryptSensitiveWithUserKey). Similar a EncryptAllWithUserKey, pero en este caso, sólo se encripta la información sensible del Paquete DTSX. Utiliza DPAPI.
- Rely on server storage for encryption (ServerStorage). Protege el Paquete DTSX completo mediante la utilización de los roles de base de datos de MSDB (db_dtsoperator, db_dtsadmin, db_dtsltduser). Esta opción no está soportada desde BIDS guardando el Paquete DTSX en el File System.
Por defecto, al crear un nuevo Paquete DTSX se utiliza el valor EncryptSensitiveWithUserKey, por lo cual, sólo podremos abrir o ejecutar el Paquete DTSX utilizando el mismo usuario y ordenador. Esta es la razón principal del error comentado (la primera en la frente ;-). Por lo tanto:
- Si deseamos abrir o ejecutar el Paquete DTSX en otro ordenador o con otro usuario, podemos soluciar este problema utilizando un valor de ProtectionLevel que utilice encriptación por Password (ej: EncryptSensitiveWithPassword), facilitando la contraseña para poder abrir o ejecutar el Paquete DTSX. Como alternativa, podemos utilizar el valor DontSaveSensitive y utilizar un fichero de configuración para almacenar la información sensible.
- Si deseamo almacenar el Paquete DTSX en SQL Server, la recomendación es utilizar para la propiedad ProtectionLevel el valor ServerStorage, para lo cual resulta muy útil utilizar la utilidad de implementación (deployment utility), que nos permitirá de forma gráfica (mediante un asistente) publicar nuestros Paquetes DTSX en SQL Server y especificar que utilice para la propiedad ProtectionLevel el valor ServerStorage (esto se consigue activando la opción "Rely on server storage for encryption" en el asistente). Como alternativa, también podemos utilizar el valor DontSaveSensitive y utilizar un fichero de configuración para almacenar la información sensible.
Como conclusión, es recomendable tomar como costumbre utilizar la opción EncryptSensitiveWithPassword de la propiedad ProtectionLevel, al menos durante el desarrollo con BIDS, y utilizar para los entornos posteriores (Pruebas Integradas, Pre-Producción, Producción, etc.) el almacenamiento de los Paquetes DTSX en SQL Server con la opción ServerStorage de la propiedad ProtectionLevel. De hecho, al promocionar entre entornos los Paquetes DTSX se pueden cambiar las propiedades de conexión y utilizar distintos ficheros de configuración.
En SSIS disponemos de los Ficheros de Configuraciones. Es posible crear y utilizar uno o varios Ficheros de Configuración (o ninguno, si no se quiere) para cada Paquete DTSX (siempre es más cómodo utilizar el menor número de configuraciones, pero utilizar múltiples configuraciones, pueder ser también de utilidad). Cada Fichero de Configuración permite asignar una o varias Propiedades, ya sea del Paquete DTSX o de cualquiera de sus componentes o tareas (ej: connection managers, log providers, variables, etc.). También es posible asignar valores a las Variables de los Paquetes DTSX. Los Ficheros de Configuración se cargan y evalúan en tiempo de ejecución del Paquete DTSX.
No sólo se pueden obtener configuraciones desde un Fichero de Configuración, sino que también se pueden obtener configuraciones de otras fuentes como el Registro, variables de entorno, etc. A continuación, comentamos los distintos tipos de configuraciones:
- Fichero de Configuración XML (ficheros con extension .dtsConfig). Los valores se obtienen desde un fichero XML. Permite almacenar la configuración de múltiples propiedades.
- Variable de Entorno. Permite obtener el valor de una única propiedad desde una variable de entorno.
- Entrada del Registro. Permite obtener el valor de una única propiedad desde una única entrada del registro.
- Variable del Paquete DTSX padre. Permite obtener el valor de una única propiedad desde la propiedad especificada en el Paquete DTSX padre.
- SQL Server. Permite obtener el valor de múltiples propiedades desde una tabla de SQL Server.
Como vemos, los únicos tipos de configuraciones que permiten almacenar valores para múltiples propiedades, son los Ficheros de Configuración XML y SQL Server. El resto de tipos, requiere utilizar múltiples configuraciones para poder establecer valores a múltiples propiedades.
Existe otro detalle de suma importancia. Al margen del tipo de configuración elegido, existen dos métodos para especificar la ubicación de la configuración:
- Método directo. Especificar de forma explícita la ubicación de la configuración (ej: en caso de un fichero de configuración, especificar explícitamente su ruta completa).
- Método indirecto. Especificar una variable de entorno, cuyo contenido es el valor de la ubicación de la configuración (ej: una variable SSIS_CONF_FILE_FACTU cuyo valor es la ruta completa del fichero de configuración deseado). Esta es una muy buena opción, dado que podemos asignar a la variable de entorno deseada, distintos valores en los diferentes servidores o entornos (ej: Desarrollo, Pruebas Integradas, Producción, etc.), facilitando así la promoción entre entornos de nuestros procesos de ETL. Hay que tener en cuenta también, que en el despliegue o publicación de los Paquetes DTSX, habrá que tener en cuenta el hecho de garantizar la existencia de la configuración utilizada, y la existencia de la variable con un valor correcto.
Resulta especialmente interesante el Método indirecto junto con los Ficheros de Configuración XML, por la versatilidad que aporta.
Sin embargo, ¿Cómo se puede establecer una variable de entorno?. La forma más sencilla, es hacer click con el botón derecho sobre Mi PC (My Computer), y después click en la opción Propiedades (Properties) del menú contextual. Seguidamente, en el diálogo Propiedades del Sistema (System Properties), seleccionamos la pestaña Opciones avanzadas (Advanced), y click sobre el botón Variables de Entorno (Environment Variables). En el diálogo Variables de Entorno (Environment Variables) agregaremos o modificaremos la variable de entorno del sistema que deseamos, ya que si utilizamos una variable de entorno de usuario, si ejecutamos el Paquete DTSX con un usuario distinto al usuario contextual, dicho usuario no tendrá visibilidad sobre la variable de entorno.
¿Cómo podemos crear ún Fichero de Configuración XML (.dtsConfig)?. Desde Business Intelligence Development Studio (BIDS), en el menú SSIS, click en la opción Configuraciones de Paquetes (Package Configurations). En el diálogo Organizador de configuraciones de paquetes (Package Configurations Organizer), es posible tanto habilitar o deshabilitar las configuraciones de paquetes, como agregar, modificar o eliminar cualquier configuración, del tipo que sea. De este modo, podemos crear una configuración de tipo Fichero de Configuración XML utilizando una ruta fija y especificando las configuraciones deseadas, y seguidamente modificar dicha configuración para que en vez de obtener la ubicación del fichero como un valor fijo, lo tome de una variable de entorno que crearemos previamente.
Es posible definir Variables en un Paquete DTSX, con el fin de poder utilizarlas en el Paquete DTSX o en cualquiera de sus componentes (tareas, conexiones, etc.), a través de Expresiones Simples o Complejas.
Se puede distinguir entre Variables definidas por el usuario (las que creamos nosotros mismos en nuestros Paquetes DTSX) y Variables del Sistema (ya vienen incorporadas, y sólo se pueden leer o especificar un evento que salte cuando cambien de valor). Los nombres de Variables son susceptibles de mayúsculas y minúsculas.
Las Variables de SSIS también tienen ámbito. Así, se pueden crear en el ámbito de un Paquete DTSX (como si se tratase de una variable global), o en el ámbito de un contenedor, tarea o controlador de evento.
Una Expresión es una combinación de símbolos (funciones, variables, etc.) que se puede evaluar devolviendo una valor. Una Expresión Simple puede ser sencillamente una Variable, una Constante o Literal, o una Función. Una Expresión Compleja, puede incluir además varios Operadores, Funciones, Columnas, etc.
Resulta especialmente útil, asignar a Propiedades el valor de las Variables, de tal modo, que a través de Ficheros de Configuración XML (o a través de otros tipos de configuraciones: Entradas de Registro, Variables de Entorno, etc.) se pueda establecer el valor de las Variables, y en consecuencia de las Propiedades. Esto es muy cómodo, ya que en caso de utilizar un mismo valor en varios sitios, es suficiente con asignar a la Varible una Configuración, y asignar la Variable a las Propiedades que la necesiten. Sin embargo, esta técnica no siempre nos será de utilidad. Por ejemplo, no se puede asignar una Expresión (ej: una Variable) a la Propiedad Password de una conexión de base de datos, al tratarse de un dato sensible. En este caso, sólo se podrá asignar directamente el valor de la Configuración.
Otra utilidad interesante es la utilización de Expresiones en las Restricciones de Precedencia. Como sabemos, dentro del Flujo de Control (Control Flow) de un Paquete DTSX, podemos establecer Restricciones de Precedencia para indicar el orden o flujo de ejecución de las distintas tareas del Flujo de Control. Por ejemplo, podemos tener una Tarea A y otra Tarea B, y establecer una Restricción de Precedencia para que la Tarea B se ejecute después que la Tarea A si la Tarea A finalizó con éxito. Habitualmente, las Restricciones de Precedencia se basan en el estado de finalización de la Tarea origen (Con éxito, fracaso, o la finalización). Sin embargo, también podemos configurar la Restricción de Precedencia para que, por ejemplo, la Tarea destino se ejecute si la Tarea origen finaliza con éxito y además se evalúa con éxito una Expresion (que por ejemplo, podría depender de una variable que se establece en la Tarea origen). Así, las posible Operaciones de Evaluación de una Restricción de Precedencia son:
- Una Restricción (Constraint). Usa el resultado de la ejecución de la tarea anterior, que puede ser éxito, fracaso o conclusión. Así, podemos especificar que la Tarea B se ejecute sólo si la Tarea A finalizó con éxito, y en caso de fracaso se ejecute una Tarea C. Este tipo de Restricción, es la más habitual.
- Una Expresión (Expression). Usa el resultado de la evaluación de una expresión (la evaluación debe dar TRUE), para decidir si la Tarea de destino se ejecuta o no.
- Una Expresión y una Restricción (Expression and Constraint). Requiere que se cumpla la evaluación de una Expresión y además una Restricción (eligiendo en éxito, fracaso o conclusión). Es decir, se deben de cumplir ambas condiciones.
- Una Expresión o una Restricción (Expression or Constraint). Requiere que se cumpla la evaluación de una Expresión o una Restricción (eligiendo en éxito, fracaso o conclusión). Es decir, es suficiente con que se cumplir una de las dos condiciones.
Este comportamiento se puede especificar desde el diálogo de Propiedades de la Restricción, o bien, click con el botón derecho sobre la Restricción, y después click sobre la opción Editar (Edit) del menú contextual, para así abrir el diálogo Editor de Restricciones de Precedencia (Precedence Constraint Editor).
Además, existen varias tareas que requieren utilizar Expresiones y/o Variables para su funcionamiento, como son:
- Contenedor de Bucles FOR (FOR LOOP Container). Las Expresiones permiten especificar las instrucciones de inicialización, evaluación e incremento del bucle.
- Transformación División Condicional (Conditional Split). Utiliza una estructura de decisión basada en expresiones booleanas para dirigir filas a los destinos deseados.
- Transformación Columna Derivada (Derived Column). Utiliza valores creados mediante Expresiones para llenar las nuevas columnas (o sustituir el valor de columnas existentes) en un Flujo de Datos (Data Flow).
Llegados a este punto, ya podemos tener una idea de la importancia y la utilidad de las Variables y de las Expresiones en los Paquetes DTSX de SSIS. Ahora queda dar una pincelada práctica al asunto.
¿Cómo se puede crear una Variable en ámbito del Paquete DTSX?. En el menú SSIS de BIDS, seleccionaremos la opción Variables, con lo que se mostrará la ventana de Variables. A continuación, abrir el Paquete DTSX deseado y hacer click sobre el fondo o tapiz del Flujo de Control (Control Flow). Ahora, en la ventana de Variables podemos ver, crear, modificar, eliminar, etc., las Variables de ámbito de Paquete.
¿Cómo se puede crear una Variables en un ámbito de Contenedor de Bucles FOR?. En el menú SSIS de BIDS, seleccionaremos la opción Variables, con lo que se mostrará la ventana de Variables. A continuación, abrir el Paquete DTSX deseado y hacer click sobre el Contenedor de Bucles FOR deseado en el Flujo de Control (Control Flow). Ahora, en la ventana de Variables podemos ver, crear, modificar, eliminar, etc., las Variables de ámbito de Contenedor de Bucles FOR.
¿Cómo se puede asignar a una Propiedad el valor de una Variable (Expresión Simple) o el valor de una Expresión Compleja?. Abrir el Paquete DTSX deseado, y abrir el diálogo Propiedades del Paquete o del elemento que se desea configurar (click con el botón derecho, y después click en Propiedades). Seguidamente, en el diálogo de Propiedades modificar la propiedad Expresiones (podemos hacer click sobre el icono de los tres puntitos). En el diálogo Editor de Expresiones de Propiedad (Property Expression Editor) podemos elegir la Propiedad deseada y escribir para la misma la Expresión que necesitemos (ya sea de memoria, o mediante el Generador de Expresiones si hacemos click sobre los tres puntitos de la Expresión).
Una configuración vital que jamás debemos olvidar es la del Registro (Logging) de nuestros Paquetes DTSX. El Registro (Logging) es un mecanismo que permite especificar qué Eventos deseamos registrar durante la ejecución de un Paquete DTSX y dónde se desea guardar la información de dichos Eventos. Se debe considerar, que es posible habilitar el Registro (Logging) a distintos niveles: Paquete, Contenedor, y/o Tarea, de tal modo, que en cada nivel podemos configurar el Registro (Logging) de los Eventos que deseemos.
Podemos seleccionar todos aquellos eventos que necesitemos, existiendo los que a continuación se enumeran:
- OnError.
- OnExecStatusChanged.
- OnInformation.
- OnPipelinePostEndOfRowset.
- OnPipelinePostPrimeOutput.
- OnPipelinePreEndOfRowset.
- OnPipelinePrePrimeOutput.
- OnPipelineRowsSent.
- OnPostExecute.
- OnPostValidate.
- OnPreExecute.
- OnPreValidate.
- OnProgress.
- OnQueryCancel.
- OnTaskFailed.
- OnVariableValueChanged.
- OnWarning.
- Diagnostic.
Principalmente, interesará utilizar los Eventos OnError, OnWarning, y OnInformation.
Así, para cada Evento podemos seleccionar qué campos deseamos que queden registrados. Por defecto, serán todos los campos, pudiendo elegir entre:
- Computer.
- Operator.
- SourceName.
- SourceID.
- ExecutionID.
- MessageText.
- DataBytes.
Podemos utilizar distintas ubicaciones para guardar la información de nuestros Eventos, a través de los distintos Proveedores de Registro que ofrece SSIS:
- Proveedor de registro SSIS para archivos de texto. Destaca por ser el método más sencillo, fácil de explorar, y fácil de manipular si posteriormente se desea cargar en una base de datos SQL Server, por poner un ejemplo. Utiliza un formato de fichero ASCII separado por comas (CSV).
- Proveedor de registro SSIS para el Analizador de SQL Server. Permite generar ficheros para abrirlo posteriormente con la herramienta Analizador de SQL Server (SQL Server Profiler).
- Proveedor de registro SSIS para SQL Server. Permite almacenar la información en SQL Server, lo cual, tiene varias ventajas: primero, que permite poder consultar la información aprovechando la potencia de Transact-SQL, y segundo, que es posile la descarga desde MSDN de un Pack de informes de Reporting Services. Otra ventaja, es que utilizar una base de datos SQL Server es un método ideal como repositorio compartido de información para su consulta por distintos usuarios. Utiliza la tabla sysdtslog90, de tal modo, que si la tabla no existe en la base de datos especificada, la crea automáticamente (no es necesario crearla manualmente).
- Proveedor de registro SSIS para el Registro de sucesos de Windows. En entornos de producción críticos, puede resultar de especial interés registrar la información de ejecución de los Paqutes DTSX en el Registro de sucesos de Windows.
- Proveedor de registro SSIS para archivos XML. Tiene prácticamente las mismas ventajas de los archivos de texto, y además, la ventaja adicional de poder utilizar XSL Transformations (XSLT) para visualizar el Registro de Paquetes DTSX como si fuese una página Web.
La elección de cuántos Proveedores de Registros y de qué tipo utilizarlos, depende principalmente de quiénes sean los usuarios de dichos Registros. En cualquier en caso, en un Paquete DTSX podemos utilizar varias Configuraciones de Registro del mismo tipo de Proveedor (ej: una configuración de Ficheros XML en una carpeta para su revisión por un equipo de trabajo, y otra configuración similar pero en otra carpeta para su almacenamiento histórico).
Por último, queda la parte práctica: ¿Cómo se implementa el Registro (Logging) de un Paquete DTSX?. Muy Fácil. Abrimos el Paquete DTSX deseado con Business Intelligence Development Studio (BIDS). Desde el menú SSIS, click a la opción Registro (Logging). En el diálogo Configurar registros de SSIS (Configure SSIS Logs), es posible especificar de forma gráfica, en cada nivel de granularidad (Paquete, Contenedor o Tarea) que se desee, qué Registros se desean utilizar (especificando su tipo y datos de conexión). Debe tenerse en cuenta que en la pestaña Detalles (Details) se debe especificar qué Eventos, y dentro de la pestaña Detalles si utilizamos el botón Avanzadas (Advanced) es posible indicar qué Columnas se desean para cada Evento.
Las Transacciones en SSIS (al igual que ocurre con las transacciones en los motores de base de datos como SQL Server) permiten realizar varias tareas como una única unidad de trabajo (incluso trabajando sobre distintos destinos de datos), de tal modo, que se realice todo el trabajo o nada, y así garantizar la integridad de los datos aún en el caso de que se produzca algún error de ejecucion en alguna tarea de la Transacción. SSIS soporta los siguientes dos tipos de transacciones:
Transacciones DTC (Distributed Transaction Coordinator)
Utiliza el servicio DTC, por lo cual, el servicio DTC debe estar disponible para la correcta ejecución del Paquete DTSX. En caso de que no esté disponible, se producirá el siguiente error:
Error: 0xC001401A at Data Flow Task: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.
Una ventaja de las Transacciones DTC es que no requiere programar explícitamente las transacciones con sus sentencias de tipo BEGIN TRAN, COMMIT TRAN, y/o ROLLBACK TRAN, y además, permite trabajar con destinos de datos heterogéneos. Para configurar un Paquete DTSX para usar Transacciones DTC, se debe configurar la propiedad TransactionOption a nivel de Paquete, Contenedor y/o Tarea, conforme se requiere. Los posibles valores de la propiedad TransactionOption son:
- Required. El Paquete, Contenedor y/o Tarea inicia una nueva transacción, salvo en el caso de que el componente principal ya haya iniciado una transacción, en cuyo caso de juntará con la transacción del componente principal.
- Supported. El Paquete, Contenedor y/o Tarea nunca inicia una nueva transacción. Tan sólo se puede juntar con la transacción del componente principal (si fue iniciada).
- NotSupported. El Paquete, Contenedor y/o Tarea nunca inicia una nueva transacción, y nunca se junta o combina con una transacción existente.
Es importante tener en cuenta que el valor por defecto de la propiedad TransactionOption es Supported.
Así, existen distintas configuraciones posibles, que a fin de cuentas, dependen como se configure la opción TransactionOption en los Paquetes DTSX y en sus componentes o tareas (puede verse como una jerarquía). Las más típicas son las siguientes:
- Un único Paquete DTSX con una única Transacción. El Paquete DTSX se configura con el valor Required para TransactionOption, mientras que el resto de sus tareas se configuran con el valor Supported.
- Un único Paquete DTSX con múltiples Transacciones. El Paquete DTSX se configura con el valor Supported para TransactionOption, mientras que el resto de sus tareas se configuran con el valor Required.
Debido a que los Paquetes DTSX pueden incluir a su vez tareas Execute Package para ejecutar otros Paquetes DTSX secundarios, es posible utilizar otras configuraciones como por ejemplo, usar múltiples Paquetes DTSX y un única Transacción (es necesario, que exista un Paquete DTSX principal con el valor Required de la propiedad TransactionOption, y que dicho Paquete DTSX utilice tareas Execute Package).
Dicho esto, la realidad es que las Transacciones DTC son una solución excepcional, ya que para aprovechar su funcionalidad, tan sólo será suficiente con establecer a Required la propiedad TransactionOption del Paquete (o de la Tarea deseada) y listo. Además, es la única forma de aportar trasaccionalidad a las tareas de tipo Data Flow.
El problema de las Transacciones DTS, es que no todos los proveedores de datos soportan Transacciones DTC. Y aquí entra la picardía. Por ejemplo, si tenemos un origen de datos que no soporta Transacciones DTC y un destino de datos SQL Server (que sí soporta Transacciones DTC), podemos crear un Paquete DTSX con un primer paso en el que, sin usar Transacciones DTC, se carga del origen de datos a una base de datos temporal SQL Server, tal cual.... y seguidamente, desarrollamos el Paquete DTSX, pero ya leyendo de un destino que SI soporta Transacciones DTC, y en consecuencia, activando la utilización de Transacciones. Bueno... es sólo una idea... Así conseguimos evitar errores como:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Con SAP Excel" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
De hecho, en las ocasiones que me he encontrado éste error, con quitar el soporte de Transacciones DTC, problema solucionado.
Transacciones Nativas de SQL Server
Utiliza las capacidades nativas de las Transacciones de SQL Server. Tiene la ventaja de ser la solución de transaccionalidad más ligera, pero tiene varios inconvenientes:
- Sólo funciona con bases de datos SQL Server.
- Sólo funciona con tareas Execute SQL, en consecuencia, no podremos utilizarsas en tareas de Flujo de Datos (Data Flow).
- Se debe incluir explícitamente la codificación apropiada de las transacciones (sentencias de tipo BEGIN TRAN, COMMIT TRAN, y/o ROLLBACK TRAN).
Para su configuración, todas las tareas de la misma transacción deben utilizar la misma conexión (es decir, el mismo Connection Manager), y se debe establecer la propiedad RetainSameConnection de la conexión utilizada (perdón… del Conection Manager) al valor True en las tareas de la misma transacción.
Hoy en día, en las empresas que desarrollan con SSIS, es habitual que los desarrolladores utilicen sus estaciones de trabajo (Windows XP Professional o Windows Vista, habitualmente) para realizar los desarrollos, y seguidamente, utilicen los servidores empresariales (Windows Server 2003, habitualmente) para desplegar sus soluciones y ejecutarlas en sus entornos de Pruebas Integradas, Pre-Producción y Producción. Y esto ¿qué tiene de interesante? Pues mucho, porque habitualmente los puestos de trabajo montan sistemas operativos de 32-bit (arquitectura x86) mientras que los servidores empresariales suelen montar sistemas operativos 64-bit (arquitectura x64, ya que Itanium es muy poco habitual). Y esto tiene su importancia.
El principal problema de utilizar diferentes arquitecturas (ej: x86 y x64) impacta en que no todo el software y no todos los drivers (ej: Drivers ODBC, Proveedores OLEDB, Proveedores .Net, etc.) están disponibles tanto en x86 (32-bit) como en x64 (64-bit), y mucho menos aún para Itanium. Un ejemplo significativo es el Proveedor MSDASQL, que se trata del Proveedor OLEDB que hace de puente con ODBC, es decir, a través de él podemos acceder a orígenes de datos ODBC desde OLEDB. Este Proveedor OLEDB es quizás uno de los Proveedores OLEDB más utilizados, sin embargo, resulta que sólo estaba disponible en 32-bit (x86). De hecho, Microsoft no tenía intención de darle continuidad en 64-bit, sin embargo, en respuesta a las múltiples solicitudes de los usuarios, finalmente accedió y desde Abril de 2008 está disponible para descarga gratuita el Proveedor MSDASQL de 64-bit (tanto para x64 como para Itanium - IA64). ¿Y hasta Abril 2008 que podíamos haber hecho para sacar adelante nuestros desarrollos?
Para más información sobre el Proveedor MSDASQL puede visitarse el artículo ¿Como consultar un origen de datos ODBC (DSN) desde SQL Server 2005 64-bit a través de OPENROWSET u OPENQUERY + Servidor Vinculado (Proveedor MSDASQL 64-bit)?.
Bien, con este ejemplo creo que ya estamos en situación. Es decir, en una máquina x64 puede ejecutarse código x64 (código 64-bit) y código x86 (código de 32-bit, que realmente se ejecuta emulado en lo que se denomina WoW: Windows-on-Windows). De este modo:
- Un proceso de 64-bit puede utilizar sólo y exclusivamente las conectividades (OLEDB, ODBC, .Net, etc.) de 64-bit. Por lo cual, si tenemos un Paquete DTSX y lo ejecutamos en 64-bit, tenemos una dependencia directa con la disponibilidad de los drivers 64-bit necesarios, para que el Paquete DTSX pueda ejecutarse con éxito.
- Un proceso de 32-bit puede utilizar sólo y exclusivamente las conectividades (OLEDB, ODBC, .Net, etc.) de 32-bit. Por lo cual, si tenemos un Paquete DTSX y lo ejecutamos en 32-bit, tenemos una dependencia directa con la disponibilidad de los drivers 32-bit necesarios, para que el Paquete DTSX pueda ejecutarse con éxito. Bueno... realmente, esto no es problema, ya que en 32-bit (x86) suele estar disponible todo.
Resumiendo, el problema que podemos tener (y que se trata de un problema habitual), es necesitar ejecutar un Paquete DTSX sobre un servidor de 64-bit (x64), con el inconveniente de que NO exite alguno de los Drivers (OLEDB, ODBC o .Net) que utiliza para 64-bit. En este caso ¿No podemos ejecutar el Paquete DTSX? ¿Que soluciones tenemos? ¿Que debemos tener en consideración? Aquí van algunos consejos que nos pueden ayudar:
Lo primero de todo, debemos tener en cuenta que las utilidades dtexec.exe, dtutil.exe y DTSWizard.exe (el asistente de importación y exportación), sobre una máquina x64 están disponibles tanto en 64-bit como en 32-bit. Eso si, la versión 64-bit la encontraremos dentro del directorio Program Files mientras que la versión 32-bit la encontraremos dentro del directorio Program Files (x86). En consecuencia, podremos ejecutar la versión de 32-bit o de 64-bit, según nos interese, para lo cual será suficiente ejecutar el ejecutable correspondiente (el de 32-bit o el de 64-bit).
Por ello, en la planificación de JOBs con el Agente de SQL Server para ejecutar Paquetes DTSX en 32-bit sobre máquinas x64 (64-bit), si tenemos SQL Server 2005 64-bit (x64) instalado, el Agente de SQL Server será un proceso 64-bit, y por lo tanto, un JOB del Agente de SQL Server con un paso del tipo SQL Server Integration Services Package, ejecutará el Paquete DTSX en 64-bit.
Sin embargo tenemos dos alternativas con las que podemos jugar:
- Utilizar en el JOB un paso del tipo Operating system (CmdExec), y en el comando a ejecutar, invocar a la versión 32-bit de dtexec.exe, la del directorio Program Files (x86), especificando todos los parámetros necesarios para su ejecución.
- En las Propiedades del Proyecto de SSIS, establecer la propiedad Run64BitRunTime a False. Esta propiedad por defecto es True. En las máquinas 32-bit es ignorada, sin embargo, en las máquinas 64-bit permite especificar si deseamos utilizar el entorno de ejecución (RunTime) de 64-bit o de 32-bit.
También es importante tener en cuenta, que si no tenemos disponibilidad de un Proveedor OLEDB 64-bit para acceder a una determinada base de datos, quizás podamos utilizar el correspondiente Proveedor .Net 64-bit para acceder a dicha base de datos, por poner otra alternativa.
Al construir un entorno de desarrollo de SSIS siempre surgen las mismas dudas:
- ¿Qué permisos son necesarios para que un desarrollador pueda guardar, ejecutar, etc. un Paquete DTSX en MSDB? ¿Cómo asignar dichos permisos?
- ¿Qué permisos son necesarios para que un desarrollador pueda crear, editar y ejecutar JOBs del Agente de SQL Server para sus Paquetes DTSX? ¿Cómo asignar dichos permisos?
En ambos casos será necesario utilizar funciones de base de datos del sistema en MSDB (es decir, roles de base de datos - fixed database roles - ... vamos, que son grupos).
Para conceder permisos a los desarrolladores (o también a operadores y administradores) sobre el almacenamiento de Paquetes DTSX en MSDB, están disponibles las siguientes funciones de base de datos (osea, grupos... que el término funciones es algo confuso) en MSDB:
- db_dtsltduser. Es necesario para poder tener acceso a los Paquete DTSX almacenados en MSDB, principalmente, para poder ver, ejecutar y exportar los Paquetes DTSX propios almacenados en MSDB.
- db_dtsoperator. Mismos permisos que db_dtsltduser, sin embargo, en éste caso es para todos los Paquetes DTSX almacenados en MSDB (no sólo para los propios).
- db_dtsadmin. Mismos permisos que db_dtsoperator, pero además puede eliminar Paquetes DTSX de MSDB, cambiar las funciones de base de datos asociadas a los Paquetes DTSX (es decir, si deseamos utilizar otras que no sean las de por defecto: db_dtsltduser, db_dtsoperator, db_dtsadmin), etc.
Además, si deseamos personalizar más la seguridad de los Paquetes DTSX almacenados en MSDB, es posible utilizar funciones de base de datos definidas por el usuario. Es decir, si nos conectamos a SQL Server Integration Services (SSIS) desde SQL Server Management Studio (SSMS), al seleccionar con el botón derecho del ratón un Paquete DTSX almacenado en MSDB, se mostrará el menú contextual correspondiente. En éste menú, al seleccionar la opción Package Roles, se mostrará la siguiente ventana de diálogo:
De este modo, podemos elegir para cada Paquete DTSX, si deseamos utilizar las funciones de base de datos por defecto de SSIS (db_dtsltduser, db_dtsoperator, db_dtsadmin), o bien, seleccionar qué funciones de base de datos definidas por el usuario (luego, tendremos que crearlas nosotros, y conceder la pertenencia a los usuarios correspondientes) deseamos utilizar para acceso de lectura y/o acceso de escritura sobre el Paquete DTSX de MSDB (SQL Server).
Del mismo modo, para conceder permisos para la creación, edición y ejecución de JOBs con el Agente de SQL Server, están disponibles las siguientes funciones de base de datos en MSDB:
- SQLAgentUserRole. Es necesario para poder crear, editar y ejecutar JOBs del Agente de SQL Server (eso sí, los JOBs de los que se es propietario... el resto ni verlos).
- SQLAgentReaderRole. Mismos permisos que SQLAgentUserRole, pero además puede ver todos los JOBs (ojo, sólo ver).
- SQLAgentOperatorRole. Mismos permisos que SQLAgentReaderRole, pero además puede ver Proxies, Alertas, Operadores, etc. También puede ejecutar o parar cualquier JOB, etc.
Es importante tener en cuenta, que si no disponemos de pertenencia a ninguna de las funciones de base de datos relativas al Agente de SQL Server, desde SQL Server Management Studio (SSMS) no podremos visualzar el nodo SQL Server Agent (excepto que seamos miembros de sysadmin, etc.).
En consecuencia, los permisos mínimos que deberemos conceder a los desarrolladores, es la pertenencia a las funciones de base de datos db_dtsltduser y SQLAgentUserRole en MSDB. Es interesante conceder siempre los mínimos permisos posibles a los desarrolladores. De hecho, si no deseamos que puedan crear JOBs, etc. (que sería lo mejor, para que los administradores puedan tener mayor control de qué ocurre en la base de datos), se debería conceder sólo y exclusivamente la pertenencia a db_dtsltduser.
Tanto las funciones de base de datos relativas a SSIS y MSDB (db_dtsltduser, db_dtsoperator, db_dtsadmin) como las funciones de base de datos relativas al Agente de SQL Server (SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole), se trata de funciones de base de datos nuevas en SQL Server 2005 (es decir, no existían en SQL Server 2000 ni en versiones anteriores).
En cualquier caso, es posible encontrar más información en los Libros en Pantalla (BOL ó Books-On-Line... vamos, la ayuda de SQL Server ;-), principalmente si deseamos obtener un detalle más exhaustivo de los permisos asociados a cada una de las funciones de base de datos descrita (aquí he querido presentar la solución y comentarlo por encima, pero en los BOL está muy bien explicado y detallado).
Dicho ésto, lo único recordar que para poder hacer miembro de estas funciones de base de datos del sistema (de MSDB) a un usuario, es necesario crear un usuario de base de datos en MSDB para los inicios de sesión a los que deseamos conceder dichos permisos. Cara a crear un usuario de base de datos, es importante recordar que si utilizamos un Inicio de Sesión para un Grupo de Directorio Activo, es posible crear un usuario de base de datos para un Usuario de Directorio Activo en particular miembro de dicho Grupo, eso sí, deberemos hacer ejecutando la correspondiente sentencia CREATE USER ... FOR LOGIN (de forma gráfica, desde SQL Server Management Studio, no podremos... sólo a través de Transact-SQL con CREATE USER).
Un problema (bueno... realmente no lo es...) al que muchos nos hemos encontrado alguna vez al desarrollar Paquetes DTSX con Business Intelligence Development Studio (BIDS), es que tenemos abierto un Paquete DTSX desde el entorno de desarrollo (Visual Studio 2005, es decir, BIDS), y cuando queremos ejecutarlo (el F5 de toda la vida ;-) para probarlo, nos encontramos que el botón de ejecutar está deshabilitado (a que jode, eh ;-)
Bueno, vamos por partes (dijo Jack). Primero, no se denomina botón de ejecutar (F5) ya que en Business Intelligence Development Studio (BIDS) el botón que utilizamos para ejecutar, se denomina Iniciar Depuración (Start Debugging). En cualquier caso, jode lo mismo cuando el botón Iniciar Depuración está deshabilitado, y no sabemos por qué.
De hecho, si observamos, además de estar deshabilitado el botón Iniciar Depuración (F5), también está deshabilitado el botón Iniciar sin Depurar (Start without Debugging). Esto jode aún más. Queremos ejecutar un Paquete DTSX desde Visual Studio 2005 (BIDS) y no podemos porque los botones están deshabilitados. Están lo botones, lo vemos, pero están en gris, y no los podemos pulsar para ejecutar el Paquete DTSX. Vamos, que es imposible ejecutar el Paquete DTSX desde BIDS, no se puede ejecutar el Paquete DTSX y no sabemos por qué.
Pues nada, que es una tontería. Así es como funciona Visual Studio 2005, que a fin de cuentas, es el entorno de desarrollo en el que estamos. Para poder ejecutar un Paquete DTSX desde Visual Studio (BIDS) es necesario que dicho Paquete DTSX pertenezca a un Proyecto, y abrir dicho Proyecto con Visual Studio 2005 (BIDS). Si sólo abrimos el Paquete DTSX, los botones que utilizamos para ejecutar, es decir, el botón Iniciar Depuración (Start Debugging) y el botón Iniciar sin Depurar (Start without Debugging) aparecerán deshabilitados.
En el peor de los casos, podemos crear un nuevo Proyecto de Integration Services (o abrir un Proyecto existente) y agregar a dicho Proyecto el Paquete DTSX deseado, eso sí, recordar que al agregarlo se creará un copia (en otros tipos de Proyecto de Visual Studio 2005, se agregaría el fichero y punto, pero con Integration Services se crea una copia... no tiene mayor importancia, pero comentarlo para evitar la confusión).
En fin, un tema la mar de sencillo, pero al menos a mí, la primera vez que me ocurrió me quedé patidifuso ;-) Espero que os sirva.
[Fecha artículo: 19/02/2008]
[Estado artículo: Abierto]
[Autor: GuilleSQL]
|
