En el artículo anterior combinamos eventos de propagación, registro y patrón padre-hijo para crear un sistema de registro personalizado para paquetes SSIS.

En este artículo, actualizaremos nuestra solución a SQL Server 2012 Integration Services y mostraremos variables SSIS, configuración de variables y manejo dinámico de valores mediante expresiones. Aunque hemos usado variables SSIS en ejercicios previos, no las analizamos profundamente. En este artículo nos enfocaremos exclusivamente en las variables SSIS.

Comenzando con el IDE de Visual Studio 2012 para desarrollo SSIS 2012

Cree un nuevo proyecto llamado My_Second_SSIS_Project (tenga en cuenta que estoy usando VS2013...).

La mayor innovación en SSIS 2012 es el nuevo modelo de desarrollo basado en proyectos (Project Deployment Model). SSIS 2012 también admite el modelo anterior basado en paquetes (Package Deployment Model).

Antes de comenzar, renombre el archivo Package.dtsx a VariablesAndParameters.dtsx:

Figura 10

Trabajo con Variables

Abra la ventana de variables y agregue una variable llamada MyVariable como se muestra:

Figura 12

Observe el ámbito de las variables en el paquete VariablesAndParameters. El uso de contenedores define mejor el ámbito. En la siguiente figura vemos tres contenedores:

1. Tarea
2. Contenedor
3. Paquete

Figura 13

Las tareas, contenedores y paquetes son ejecutables. En SSIS, un objeto ejecutable tiene propiedades y eventos. Una tarea siempre se coloca dentro de un contenedor. "¿Pero qué pasa si coloco directamente una tarea SQL en el flujo de control?" Buena pregunta: el paquete mismo es un contenedor. SSIS también ofrece otros contenedores: Sequence Container, For Loop Container y Foreach Loop Container. Cada contenedor puede contener tareas y cada contenedor, incluido el paquete, es un objeto ejecutable.

SSIS también permite representar ámbitos de manera diferente. Hagamos una demostración: agregue un Sequence Container al flujo de control y luego una tarea SQL:

Figura 14

El explorador de paquetes muestra el ámbito en vista de árbol, tal como se ve en la figura 13: este paquete contiene un contenedor (Sequence Container) que a su vez contiene una tarea (Execute SQL Task) (mostrado en la figura 15):

Figura 15

La vista de propiedades ayuda a visualizar el "arriba" y "abajo" en el ámbito. Desde la vista de la tarea SQL, el Sequence Container está "arriba".

El ámbito por defecto de las variables en SSIS 2012 es el paquete. El ámbito de las variables puede modificarse, aunque hasta ahora no he encontrado un uso especial para las variables en ámbito de paquete. Haciendo clic en el segundo botón de la barra de herramientas de la ventana de variables, podemos mover variables a diferentes ámbitos:

Figura 16

Ajuste el ámbito al Sequence Container:

Figura 17

Nota: el ámbito ya ha cambiado a Sequence Container.

Figura 18

Cuando hace clic en el área vacía del flujo de control:

Figura 20

Como MyVariable no está en ámbito de paquete, ya no se muestra en la ventana de variables. Aunque puede cambiar esta configuración:

Figura 21

Haga clic en el botón Grid Options y seleccione "Mostrar variables de todos los ámbitos":

Figura 22

Después de hacer clic en Aceptar, MyVariable aparecerá en la ventana de variables:

Figura 23

Elimine la tarea SQL y reemplácela con una tarea Script. Cree una nueva variable MyVariable (el ámbito por defecto es el paquete VariablesAndParameters):

Figura 24

Para la demostración, establezca dos valores diferentes para MyVariable.

Haga doble clic en la tarea Script para abrir el editor, luego establezca el lenguaje de script en Microsoft Visual Basic 2012. En Variables solo lectura, seleccione MyVariable:

Figura 25

Después de seleccionar MyVariable, haga clic en Aceptar. El editor de tareas Script muestra:

Figura 26

Haga clic en Editar script y agregue el siguiente código en el subprocedimiento público Main():

MsgBox(Dts.Variables("User::MyVariable").Value.ToString)

</div>*Código 1*

El editor de script ahora muestra:

*Figura 27*

Cierre el editor de script y ejecute el paquete para ver qué valor se muestra:

*Figura 28*

¿Por qué se muestra 42? Recuerde la pila de ejecución mencionada anteriormente. Piense cómo se propagan los eventos entre estos objetos ejecutables. ¿Recuerda cómo funcionan? Los eventos se propagan paso a paso, lo que denomino "burbuja de eventos".

El comportamiento de las variables es similar. Antes de que se ejecute la tarea Script, SSIS intenta bloquear la variable *MyVariable*. ¿Por qué bloquearla? Imagínese si dos objetos ejecutables usan la misma variable al mismo tiempo. Supongamos que uno la escribe y otro la lee. Para garantizar que el valor no cambie durante la lectura, SSIS bloquea la variable.

Imaginemos el mecanismo de bloqueo de SSIS ("portavoz" para esta analogía), que recorre la pila de ejecución buscando la variable *MyVariable*. El portavoz comienza preguntando al Script Task: "¿Tienes una variable llamada *MyVariable*?" El Script Task responde: "No." Luego el portavoz avanza en la pila y pregunta al Sequence Container: "¿Tienes una variable llamada *MyVariable*?" El Sequence Container responde: "Sí." Luego el portavoz detiene el bloqueo. ¿Ha encontrado la variable buscada? ¿O no?

Si mi variable está oculta, podría no darme cuenta de que tengo dos variables con el mismo nombre *MyVariable* pero en diferentes ámbitos. Podría haber cambiado accidentalmente el ámbito de *MyVariable* a Sequence Container y luego crear una variable *MyVariable* en el ámbito del paquete (acabamos de crear esta variable).

Además, cuando cierra SSDT, la opción "Mostrar variables de todos los ámbitos" se restablece.

Por eso no me gusta la ocultación de variables en diferentes ámbitos por defecto: no puedo acceder a la variable *MyVariable* del ámbito del paquete desde la tarea Script. No aparece en la lista de Variables solo lectura. Tampoco puedo especificar "VariablesAndParameters.User::MyVariable". Simplemente no existe esa opción, y no sabré de su existencia a menos que active "Mostrar variables de todos los ámbitos".

Tipos de datos de variables
---------------------------

SSIS ofrece varios tipos de datos para variables: diversos tipos numéricos, fecha, byte, booleano, cadena y carácter. El más interesante es el tipo de datos Object:

*Figura 29*

El tipo de datos Object en SSIS puede contener muchos valores, incluyendo enteros simples, caracteres y fechas. También puede contener objetos como colecciones, matrices, conjuntos de registros y conjuntos de datos. No mostraré todos, pero recomiendo leer mi artículo SSIS 101: Variables Object, ResultSets y Foreach Loop Containers.

Creando conexiones usando variables
-----------------------------------

Aplicaremos lo aprendido. Las variables pueden usarse para construir otros valores de variables. Crearemos una conexión a un archivo plano usando variables.

Cree un archivo plano Songs.csv:

<div>```

Id,Artist,Song
"0","Waylon Jennings","Lonesome, On'ry, and Mean"
"1","Willie Nelson","Blue Eyes Cryin' in the Rain"
"2","Kris Kristofferson","Sunday Mornin', Coming Down"

Agregue una tarea Data Flow y conectela al Sequence Container:

Figura 39

Abra la tarea Data Flow y agregue un origen de archivo plano:

Figura 40

Abra el editor del origen de archivo plano y cree una nueva conexión de archivo plano:

Figura 41

El editor de administrador de conexiones de archivo plano muestra el nombre de la conexión como "Songs Flat File" y la ruta del archivo como el archivo creado previamente. Establezca el "Text qualifier" como comillas dobles:

Figura 42

Haga clic en Aceptar para cerrar el editor del administrador de conexiones de archivo plano. Muestra:

Figura 43

Haga clic en Aceptar para cerrar el editor del origen de archivo plano.

Cree una base de datos llamada "TestDB":

Use master go If Not Exists(Select name From sys.databases Where name = 'TestDB') begin print 'Creating TestDB' Create Database TestDB print 'TestDB created' end Else print 'TestDB already exists' go

</div>*Código 3*

El código 3 es un ejemplo de script idempotente. Aprendí este término de Jamie Thomson (blog | @jamiet), quien lo define como código reejecutable.

Arrastre un destino OLE DB al SSDT:

*Figura 44*

El proceso de creación de conexión se omite. Haga clic en el botón "Nombre de la tabla o vista" y aparecerá una ventana con el DDL generado desde el flujo de datos (Lenguaje de Definición de Base de Datos). Cambie el nombre de la tabla a "Songs". Note que las columnas provienen del flujo de datos:

*Figura 48*

Después de hacer clic en Aceptar, se muestra:

*Figura 49*

El editor del destino OLE DB muestra una advertencia:

*Figura 50*

Haga clic en la pestaña Mapeos para obtener una coincidencia automática:

*Figura 51*

Puede preguntar: ¿qué es la coincidencia automática? Las columnas de entrada disponibles representan el esquema del flujo de datos desde la ruta del flujo de datos. Las columnas de destino disponibles representan las columnas de la tabla o vista objetivo. ¿Cómo crea la tabla el destino OLE DB usando metadatos?

Abra la ruta del flujo de datos y haga clic en la pestaña Metadatos para mostrar la estructura de la ruta del flujo de datos:

*Figura 52*

Los metadatos contienen nombres de columnas, tipos de datos y longitudes. Cuando hacemos clic en el botón Nuevo para crear una tabla, estos metadatos proporcionan la información del esquema.

Las columnas de destino disponibles (mostradas en la figura 51) también se generan con esta información, que luego se usa para la coincidencia automática.

Expresiones con variables
-------------------------

En la ventana de variables de SSIS, cree una variable de nivel de paquete llamada *FileDirectory* y establezca su valor como la carpeta donde se encuentra el archivo *Songs.csv*:

*Figura 53*

Cree una variable *FileName* y establezca su valor como "Songs.csv":

*Figura 54*

Cree una variable *FilePath*:

*Figura 55*

Luego establezca la expresión para *FilePath*:

Variables en expresiones dinámicas de propiedades
-------------------------------------------------

Ahora tenemos una variable *FilePath* que contiene la ruta completa del archivo de origen, generada por una expresión que incluye los valores de *FileDirectory* y *FileName*.

¿Qué haremos ahora? Usaremos esa ruta completa para gestionar dinámicamente la conexión del administrador de conexiones de archivo plano ("Songs Flat File"). Haga clic en el administrador de conexiones "Songs Flat File" y presione F4 para abrir el panel de propiedades:

*Figura 62*

Haga clic en el botón de puntos suspensivos junto a Expresiones. En el menú desplegable de propiedades, seleccione la propiedad ConnectionString:

*Figura 63*

Haga clic en el botón de puntos suspensivos junto a la propiedad Expression y arrastre la variable *FilePath* allí:

*Figura 64*

Después de completar, se muestra:

*Figura 65*

Introducción a puntos de interrupción y estado de variables
-----------------------------------------------------------

Los puntos de interrupción son útiles para depurar. Antes de probar, haga clic en la tarea Data Flow y seleccione "Editar puntos de interrupción..." desde el menú contextual. En este ejemplo, estableceremos un punto de interrupción para pausar la ejecución cuando la tarea Data Flow dispare el evento PreExecute:

*Figura 66*

En "Establecer puntos de interrupción", seleccione "Pausar cuando el contenedor recibe el evento OnPreExecute":

*Figura 67*

Después de confirmar, el punto de interrupción se mostrará en el icono de la tarea Data Flow:

*Figura 68*

Ejecute el paquete. Verá que se detiene cuando la tarea Data Flow dispare el evento PreExecute:

*Figura 69*

Haga clic en el menú Debug → Windows → Locals para ver el estado de *FilePath*:

*Figura 70*

Expandiendo la sección Variables, localice la variable *User::FilePath*:

*Figura 71*

Presione F5 o haga clic en el botón Continuar (Play) para continuar la ejecución. Finalmente importamos tres filas de datos:

*Figura 72*

</div>