Recientemente nos hemos encontrado en un cliente con la necesidad de incluir tablas temporales en nuestras cargas utilizando SSIS 2008 para aliviar un problema de rendimiento. Es muy fácil utilizar consultas directas como orígenes OLEDB, pero ¿y si tenemos que nutrirnos de tablas temporales? Bien, veremos que es igualmente fácil aunque tendremos que trabajar un poco más.

Una consulta similar que teníamos que aplicar pero utilizando AdventureWorks 2008 sería:

if (select object_id(‘tempdb..#temptable’)) is not null

drop table #temptable

 

create table #temptable (a int)

 

insert into #temptable values (1)

insert into #temptable values (2)

insert into #temptable values (3)

 

create clustered index idx_temp on #temptable (a)

 

SELECT * FROM SalesLT.Customer a inner join #temptable b on a.CustomerID = b.a

 

Esto, que en TSQL funciona sin problemas, nos puede dar algún quebradero de cabeza en SSIS. Lo primero que se puede pensar es «bueno, tenemos un origen OLEDB donde podemos poner un comando SQL… ¡vamos a ponerlo todo junto!». Este planteamiento no funcionará ya que SSIS dará un error al no poder identificar la tabla #temptable.

Figura 1- Error al no encontrar la tabla

Figura 1- Error al no encontrar la tabla

062811_1435_Trabajandoc5

Buscando un poco de información por Internet y aplicando un poco de sentido común (y algo de ensayo / error), acabamos dando con la siguiente solución. Construimos un paquete con una tarea SQL antes de nuestro DataFlow donde aplicamos la parte de creación de la tabla:

Figura 2 - Estructura del paquete SSIS

Figura 2 – Estructura del paquete SSIS

 

Nuestra tarea «Crear Temporales» contendrá como comando SQL lo siguiente:

if (select object_id(‘tempdb..#temptable’)) is not null

drop table #temptable

 

create table #temptable (a int)

 

insert into #temptable values (1)

insert into #temptable values (2)

insert into #temptable values (3)

 

create clustered index idx_temp on #temptable (a)

 

Mientras que en nuestro DataFlow utilizaremos una variable para encapsular el comando SQL que nutrirá el origen de datos.

Figura 3 - Consulta SQL encapsulada en una variable

Figura 3 – Consulta SQL encapsulada en una variable

 

Esta variable retornará el mismo metadato que la final para que la primera validación que haga SSIS funcione correctamente. En este paso es donde está el «engaño» a SSIS y lo que permitirá la ejecución del paquete consumiendo la tabla temporal.

También tendremos que configurar el adaptador de conexión y establecer la propiedad RetainSameConnection a TRUE. De esta manera conseguiremos que tanto la Tarea SQL donde hemos creado los objetos y el origen OLEDB compartan conexión y así la tabla temporal sea visible para ambos. Esto es fundamental ya que de cualquier otra manera no funcionará. Podéis probarlo abriendo dos consultas en SSMS contra la misma BD, creando una temporal en una e intentando acceder a ella desde la otra conexión.

Figura 4 - Propiedad RetainSameConnection a True

Figura 4 – Propiedad RetainSameConnection a True

Para finalizar, modificaremos la consulta que tenemos encapsulada en la variable, en nuestro caso eliminando el comentario de la parte donde consumimos la tabla temporal. Esto dará un fallo de validación en el origen OLEDB ya que intenta validar en tiempo de diseño la consulta y, al no estar la conexión abierta, no podrá dar por bueno el comando SQL que encuentra. Por lo tanto, cambiamos la variable:

Figura 5 - Consulta incluyendo tabla temporal

Figura 5 – Consulta incluyendo tabla temporal

Y modificamos la propiedad del origen OLEDB ValidateExternalMetadata para evitar el fallo de validación en tiempo de diseño:

Figura 6 - Propiedad ValidateExternalMetadata a False

Figura 6 – Propiedad ValidateExternalMetadata a False

 

Una vez tengamos configurado el paquete de esta manera, podremos ejecutarlo y nuestra tabla temporal será visible y utilizable como origen de datos:

 

Figura 7 - Ejecución del paquete

Figura 7 – Ejecución del paquete

 

Podemos comprobar que se ejecuta correctamente porque sólo devuelve 3 filas, las 3 que coinciden con las que hemos insertado en la tabla temporal (valores 1, 2 y 3).

Este método puede variar dependiendo del tipo de paquete que tengamos que desarrollar (iterando por un grupo de valores, con varios adaptadores de conexión, etc.) pero básicamente tendríamos que aplicar las mismas pautas para conseguir la compartición de la conexión y poder consumir tablas temporales.

Pau Sempere
Últimas entradas de Pau Sempere (ver todo)