SSIS e Tabelas Temporárias

Ás vezes, no desenvolvimento de pacotes ETL com SSIS nos deparamos com a necessidade de consumir uma Stored Procedure que contenha uma ou mais tabelas temporárias em seu código, como no exemplo abaixo.

CREATE PROC dbo.MinhaProc AS

SELECT ID, NOME, TELEFONE

INTO #CONTATO

FROM CONTACT

            
               Infelizmente os campos da tabela temporária não são acessados de forma “nativa” no SSIS. Para que isso seja possível podemos lançar mão de um dos três procedimentos :   

1)  Procedimento rápido porém com indesejáveis efeitos colaterais

O modo mais rápido é adicionar o comando SET FMTONLY OFF no início da Stored Procedure, porém fará com que a Procedure seja executada mais de uma vez, segundo Paul Ibison,  no artigo: (http://www.sqlservercentral.com/articles/Integration+Services/61824/) , fará com que a Procedure seja executada 5 vezes exatamente, o que pode fazer com que a TASK no SSIS demore 5 vezes mais e ainda pior, se no código da Procedure houver “inserts”/”updates”, esses serão executados 5 vezes. A conclusão é que esta alternativa deve ser sempre evitada já que possui efeitos colaterais graves.

2)  Procedimento bom porém mais trabalhoso

Basicamente se resume em converter as tabelas temporárias em variáveis do tipo tabela. As vezes variáveis tipo tabela são tão ou mais performáticas que tabelas temporárias. O SQL Server não mantém nenhuma estatística para variáveis tipo tabela. Toda vez que este tipo de variável for utilizada numa “query” ocorrerá  “table-scan”. Se a Stored Procedure possuir mais de 1 tabela temporária todas devem ser convertidas para variáveis tipo tabela mesmo se apenas campos de 1 tabela temporária forem manipulados pelo pacote SSIS.

3)  Procedimento rápido e o mais recomendado (o pulo do gato)

Basta “enganar” o SSIS colocando um IF no início da Stored Procedure que nunca será verdade (exemplo: IF 0=1). Para nossa Stored Procedure original a técnica poderia ser implementada assim:

CREATE PROC dbo.MinhaProc AS

SET NOCOUNT ON

                IF 0 = 1

                   BEGIN

                      SELECT CAST(NULL AS INT) AS ID,

                        CAST(NULL AS NVARCHAR(50)) AS NOME,

                        CAST(NULL AS NVARCHAR(12)) AS TELEFONE

  END

SELECT ID, NOME, TELEFONE

INTO #CONTATO

FROM CONTACT

Nesta “query de contrato” que fica dentro do IF deve haver tantos campos ou menos do que há na tabela temporária e os tipos dos dados (data types) devem coincidir. Qualquer campo que existe na tabela temporária e que não esteja na “query de contrato” não será visível no SSIS e qualquer campo integrante da “query de contrato” que não exista na tabela temporária será visível no SSIS, porém o pacote gerará um erro de execução se este campo for manipulado dentro do SSIS.