Generando una conexión variable para un archivo de texto en SSIS

Esta entrada también la escribí hace como 2 años y aquí la rescato para que podamos tenerla disponible.


Recientemente me enfrente a un problema particularmente interesante,  se requería trabajar con una serie de paquetes para convertir la información de una base de datos generada en FoxPro a una base de datos de SQL Server 2005. Si bien se utiliza se puede utilizar el ODBC para acceder a la base de datos de FoxPro, nosotros decidimos instalar el driver OLEDB para FoxPro, de tal forma que el acceso a la información se hace más directamente y sin tantas limitantes. Por otro lado la información debería ser completada con la incorporación de fotografías, las cuales se solicito fueran en formato JPEG, para que fueran mas fácilmente cargadas. Para lograr que las fotos se cargaran adecuadamente y no faltara alguna, se genero un pequeño programa que lee el directorio donde se encuentran las fotografías y genera un  archivo de texto, conteniendo el total de las fotografías, con un formato adecuado para que el paquete cargue las fotos en la base de datos.
Bien una vez que he dado el contexto de trabajo, procederé a indicar la solución, primeramente se genero el paquete SSIS conteniendo una tarea de ejecución de proceso, en esta tarea se ejecuta el programa que genera el archivo de texto que contiene la información del directorio de fotos, al finalizar se llama la tarea de ejecución de SQL que limpia la tabla de la base de datos de SQL Server, posteriormente se ejecuta la tarea de flujo de datos que realiza la carga de las fotos en la tabla correspondiente de la base de datos.  Esto se muestra a continuación:

Ilustración 1: Solución SSIS Versión 1

La tarea de ejecución de proceso, que es la  tarea que genera el archivo que sirve de origen de datos para la tarea de flujo de datos,  contiene la siguiente definición para el proceso:

RequireFullFileName	True
Executable	C:\PackageDir\PackageSln\PackageSln\CreaList.exe
Arguments	/path=”C:\Datos”
FailTaskIfReturnCodeIsNotSucces	True
SuccessValue	0
TimeOut	0
WindowsStyle	Normal

La tarea ejecutar SQL es una tarea que se conecta al origen de datos de SQL Server y ejecuta la sentencia de sql TRUNCATE TABLE para la tabla que recibe la información.
Finalmente se tiene la tarea de flujo de datos que utiliza y consume los datos del archivo de texto que es generado anteriormente, de esta forma la conexión al origen de datos se presenta como se muestra a continuación:

Ilustración 2: Conexión al archivo de texto Fotos.txt

Como se sabe, cuando el paquete se inicializa para ejecutar SSIS realiza una validación de las conexiones, así que será necesario que en el directorio se encuentre un archivo denominado Fotos.txt completamente vacío, antes de que el paquete se ejecute, ya que de otra forma no podrá ejecutarse, marcando error por falta de una conexión al archivo Fotos.txt.
En este ejercicio se puede observar que cuando el paquete se ejecute, será necesario proporcionar 3 elementos, el primer elemento a proporcionar es la ruta completa del archivo ejecutable, en este caso la ruta de CreaLista.exe, el segundo elemento es el parámetro que se requiere por el ejecutable, que es el directorio de los datos y finalmente se requiere que se especifique la ruta completa del archivo de texto Fotos.txt.  Si siempre se ejecuta en el mismo equipo y en el mismo directorio, entonces no necesitamos más y el paquete funcionara perfectamente. Ahora bien, si lo que se desea es que el paquete pueda ser ejecutado en varios equipos o que los datos se encuentren en diversos directorios, entonces necesitaremos crear una alternativa.
La primera opción que analizaremos es la necesidad de que la información a migrar este en varios directorios, para ello necesitaremos que el argumento que sirve de parámetro al ejecutable y la ruta del archivo de texto, cambien de una forma mas dinámica. Para ello definimos un par de variables de usuario en el paquete, que denominaremos “ArgsDirectory” de tipo cadena y con el valor inicial que se estableció previamente: /path=”C:\Datos”; la segunda variable se denominara “PhotoFile” de tipo cadena y con el valor inicial que se estableció antes: “C:\Datos\Data\Fotos\Fotos.txt”
Inmediatamente después vamos a la tarea de ejecución de proceso y seleccionamos edición para ir directamente a la pestaña de Expresiones, inicialmente no existen definiciones, así que procedemos a agregar seleccionar la expresión de Arguments y colocamos la variable ArgsDirectory que previamente se definió, en la forma: @[Usuario::ArgsDirectory], se debe observar como en la figura siguiente

Ilustración 3: Expresiones de tarea de ejecución de proceso

Para el caso del origen de datos del archivo de texto, no es fácil hacerlo a través de editar la conexión, sin embargo podemos hacerlo a través de las propiedades de la conexión, para ello nos colocamos en el origen de datos y solicitamos propiedades, aparecerán las propiedades en la parte derecha, buscamos Expresiones al posicionarnos aparecer un pequeño botón, como se aprecia a continuación:

Ilustración 4: Propiedades del origen de datos o conexión

Al oprimir el botón, aparece una ventana que nos permite agregar las expresiones, agregamos la correspondiente a ConnectionString y le asignamos la de la variable PhotoFile, en el formato @[Usuario::PhotoFile], como se ve a continuación:

Ilustración 5: Expresiones de propiedad para conexión

Una vez que hemos definido las variables y las expresiones, será necesario agregar una tarea de secuencia de comandos para que sea la que genere la conexión correspondiente y la asigne a la variable PhotoFile a partir del valor de la variable ArgsDirectory.  Esta tarea la colocamos inmediatamente antes de la tarea de flujo de datos para tener la cadena de conexión al procesar. Como se puede apreciar en la siguiente figura:

Ilustración 6: Paquete SSIS versión 2

La tarea de secuencia de comandos utiliza las variables definidas y genera el valor de la cadena de conexión, como se indica a continuación:

       Public Sub Main()

        '

        ' Define una variable de paso para contener el valor de ArgsDirectory

        Dim sDirectory As String = Dts.Variables("ArgsDirectory").Value.ToString()

        ' Define una variable para armar la cadena de conexion al archivo fotos.txt

        Dim sPhotosFile As String = String.Empty

        ' Arma la cadena de conexion para el archivo fotos.txt

        sPhotosFile = sDirectory & "\Data\Fotos\Fotos.txt"

        ' Asigna la cadena de conexión a la variable PhotosFile

        Dts.Variables("PhotosFile").Value = sPhotosFile

             '

       Dts.TaskResult = Dts.Results.Success

End Sub

Finalmente podemos ejecutar el paquete proporcionándole a la variable ArgsDirectory el valor correspondiente al directorio donde se encuentran los datos, para que el ejecutable genere el archivo de texto y sea utilizado en la tarea de flujo de datos.

Un segundo escenario es definir una variable que será utilizada para colocar la ruta del ejecutable en la tarea, de tal forma que podemos en este caso ejecutarlo en un equipo diferente y solo tendremos que acceder a las variables para proporcionar los valores correspondientes sin tener que modificar o editar las tareas y la conexión del archivo.

Nuevamente les solicito de sus opiniones y comentarios para saber si esta solución les ha sido de utilidad.

Homologar la intercalación de las columnas de todas las tablas para una base de datos

Hace tiempo inicie un espacio de blog que perdí, sin embargo hice algunas entradas que he rescatado y quiero aquí mantener, por ello aquí les dejo una entrada que creo puede ser de utilidad.


Muchas veces cuando estamos desarrollando o bien administrando un servidor de base de datos se presenta la necesidad de adquirir una base de datos que fue generada en un servidor diferente, ya sea por la vía de una restauración o por agregar los archivo correspondientes en el directorio seleccionado, es común que esta base de datos adquirida se presenta con una intercalación diferente a la que se tiene definida en el servidor. Para obtener la intercalación que se tiene en el servidor, es la que está definida en la base de datos master, de tal forma que es posible obtener esta información utilizando la consulta siguiente:

SELECT collation_name FROM sys.databases where name = 'master'


Como todo mundo sabe es importante que la intercalación de las bases de datos sea la misma que la que se tiene en el servidor para mejorar el rendimiento, ya que en caso contrario será necesario que en las consultas se haga referencia a la intercalación adecuada. Si bien es cierto que es muy sencillo realizarlo con las sentencias de SQL Server, de tal forma que para cambiar la intercalación utilizamos la siguiente sentencia:

ALTER DATABASE [DatabaseName] COLLATE [CollateName]


Si bien esta sentencia cambia la intercalación de la base de datos, no necesariamente cambia la intercalación de todas las columnas de las tablas de la base de datos. Entonces qué hacer para ese caso, una solución es abrir la definición de cada tabla y cambiar manualmente la intercalación, o utilizar la sentencia ALTER TABLE para cambiar la intercalación de cada columna, esto puede ser una tarea muy lenta.

Cuando ocurre esta situación hace falta una herramienta o bien un script que nos ayude a cambiar la intercalación de todas las columnas de tipo char, varchar, nchar, nvarchar, ntext o text que haya en las distintas tablas de la base de datos. Es entonces útil saber que toda la información de la base de datos se encuentra definida en las tablas del sistema, mas específicamente la tabla INFORMATION_SCHEMA.COLUMNS contiene todas las columnas de las tablas, por lo que la siguiente consulta nos arrojara la información de todas las columnas de tipo char, varchar, nchar, nvarchar y text de las tablas de la base de datos

USE DatabaseName

SELECT Table_Schema+'.'+Table_Name, Column_Name, Data_Type, CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')

  AND (Data_Type = 'char' OR Data_Type = 'varchar' OR Data_Type = 'nchar' OR Data_Type = 'nvarchar' OR Data_Type = 'text' OR Data_Type = 'ntext')

ORDER BY Table_Schema,Table_Name

Esto nos dará como resultado las tablas, con sus respectivos esquemas, en nombre de la columna, el tipo y la longitud de la columna, existe una consideración para los tipos de datos varchar(max) y nvarchar(max) que a partir de la versión 2005 se incluye para sustituir los tipos text, que el valor para la longitud aparece como -1.

En una base de datos con 50 tablas en la que participe, se localizaron aproximadamente 210 columnas que contenían los tipos de datos especificados anteriormente. Imagínense que se hiciera la siguiente instrucción por cada una de las columnas listadas.

ALTER TABLE TableName ALTER COLUMN ColumnName DataType(MaxLength) COLLATE CollateName

Donde; DataType es uno de los tipos char, varchar, nchar, nvarchar, ntext o text
MaxLength es la longitud de la columna, no se especifica en el caso de text o ntext.

Es por ello que hice un script que utiliza un cursor para llevar a cabo el cambio en las columnas de todas las tablas por la intercalación especificada, como se muestra a continuación

/*********************************************************************************************

** Autor:    Julio J. Bueyes (jjbueyes@hotmail.com)

** Fecha:    Abril, 2009

** Proposito: Homologar la intercalacion de una base de datos y las columnas de las tablas

**                         con la intercalacion definida en el servidor

**********************************************************************************************/

-- Definicion de variables para el proceso

DECLARE @TableName AS varchar(100)      --<< Almacena el nombre de la tabla junto con el esquema

DECLARE @ColumnName AS varchar(60)      --<< Almacena el nombre de la columna

DECLARE @DataType AS varchar(10)  --<< Almacena el tipo de dato de la columna

DECLARE @MaxLength as varchar(5)  --<< Almacena la longitud de la columna

DECLARE @Command as varchar(1000) --<< Contiene el comando sql a ejecutar

-- Definicion de variables que seran usadas como constantes

DECLARE @Collate as varchar(50)         --<< Contendra el valor de la intercalacion

DECLARE @Database as varchar(50)  --<< Contendra el nombre de la base de datos

-- Establecer el valor de las constantes

SELECT @Collate = collation_name FROM sys.databases where name = 'master' --<< Establece la intercalacion que se utiliza en el servidor

SET @Database = 'DatabaseName'          --<< Establecer el nombre de la base de datos que se desea homologar

-- Cambiar la intercalacion de la base de datos especificada por la del servidor

SET @Command = 'ALTER DATABASE ' + @Database + ' COLLATE ' + @Collate

EXEC (@Command)

-- Establecer el ambito de la base de datos

SET @Command = 'USE ' + @Database

EXEC (@Command)

--Declarar un cursor con la informacion de las columnas de todas las tablas de la base de datos

DECLARE CurTables CURSOR

FOR

SELECT Table_Schema+'.'+Table_Name, Column_Name, Data_Type, CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')

  AND (Data_Type = 'char' OR Data_Type = 'varchar' OR Data_Type = 'nchar' OR Data_Type = 'nvarchar' OR Data_Type = 'text' OR Data_Type = 'ntext')

ORDER BY Table_Schema,Table_Name

-- Abrir el cursor y obtener el primer registro

OPEN CurTables

FETCH NEXT FROM CurTables INTO @TableName, @ColumnName, @DataType, @MaxLength

-- Mientras existan registros en el cursor se cambiara la intercalacion de las columnas en la tablas de la base de datos

WHILE @@FETCH_STATUS = 0

BEGIN

       -- Identifica si es text o de otro tipo de dato

       IF @DataType = 'char' OR @DataType = 'varchar' OR @DataType = 'nchar' OR @DataType = 'nvarchar'

             SET @Command = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @DataType + '(' + CASE @MaxLength WHEN -1 THEN 'max' ELSE @MaxLength END + ') COLLATE ' + @Collate

       ELSE

             SET @Command = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @DataType + ' COLLATE ' + @Collate

       -- se mantiene un registro de las instrucciones ejecutadas

       PRINT @Command

       -- ejecuta el comando generado

       EXEC (@Command)

       -- obtiene el nuevo registro del cursor

       FETCH NEXT FROM CurTables INTO @TableName, @ColumnName, @DataType, @MaxLength

END

-- Cerrar y liberar el cursor

CLOSE CurTables

DEALLOCATE CurTables

      

--- Las columnas de las tablas de la base de datos especificada se han homologado con la intercalacion del servidor

Como pueden observar se obtiene la intercalación del servidor y se aplica a la base de datos y a las columnas de tipo char, nchar, varchar, nvarchar, text y ntext, con el objetivo de mejorar el desempeño de las consultas y evitar los usos de la especificación de la intercalación en las mismas.

Espero que esto les ayude a mejorar su trabajo, si tienen algún comentario o sugerencia no duden en enviármela al correo especificado. Gracias.