CODEWITHSUNDEEP
ssis
user384080
user384080

Reputation: 4692

temporary table in SSIS ado.net source

I can only find a solution for using temp table in OLE DB source. But I can't find a solution for ADO.NET source. How can I successfully use temp table in the ADo.NET source in SSIS package?

Upvotes: 1

Views: 3048

Answers (1)

billinkc
billinkc

Reputation: 61211

I find working with temporary tables in SSIS more pain than they are usually worth. I hope your experience is better.

Create an ADO.NET connection. In the properties of the Connection Manager set the value of RetainSameConnection from false to true. This will allow the temporary table created to be in existence for the duration of the package execution by preventing connection pooling from swapping out threads.

My trouble extends from getting the metadata set up correctly. To get around this, I create a variable, QuerySource, that queries a physical table that mirrors what the temporary table will look like. SELECT S.src_id, S.src_value FROM dbo.SRC AS S; This allows the data flow to establish the correct meta data for the downstream components. I manually use this query in the ADO.NET source. Once that's done, I will need to change the query to use the temporary table, ##SRC. Unlike the OLE DB Source component, you cannot set this property inside the Data Flow task.

Once the data flow work is completed, back in the Control Flow, view the Properties of the Data Flow Task. Change the Delay Validation from false to true. This will prevent any design time validation from firing which is needed once we remove the non-temporary table "scaffolding." Next find the Expressions and click the ellipses (...). In the drop down, you should see the name of your ADO.NET source. I had renamed mine so I saw [ADONET Src].[TableOrViewName] and [ADONET Src].[SqlCommand] in the drop down list. I selected [ADONET Src].[SqlCommand] and as my value, I used @[User::SrcQuery].

I ran the package to ensure it still worked. It did. I then changed the value of my query to be SELECT S.src_id, S.src_value FROM ##SRC AS S; I reran and this time it correctly pulled data from my temporary table.

If you are using SQL Server 2012 as your source, you might be able to make it easier on yourself by using the WITH RESULT SETS option of the EXECUTE statement to explicitly describe your temporary tables metadata.

Upvotes: 4

Related Questions