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.
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:
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.
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.
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:
Y modificamos la propiedad del origen OLEDB ValidateExternalMetadata para evitar el fallo de validación en tiempo de diseño:
Una vez tengamos configurado el paquete de esta manera, podremos ejecutarlo y nuestra tabla temporal será visible y utilizable como origen de datos:
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.
1 comment
Hola Pau,
lo que deseo hacer es por medio del multicast insertar datos en una tabla temporal, me podrías aconsejar de como hacer esto?
Te agradezco.