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

Antecedentes

Hace tiempo 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 Microsoft SQL Server 2005. Si bien 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 solicitó 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.

Solución

Bien una vez que he dado el contexto de trabajo, procederé a indicar la solución, primeramente se genero el paquete SSIS (SQL Server Integration Services) 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 que limpia la tabla de la base de datos de Microsoft 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 Microsoft 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.