Cte and Temptables in SSIS

Question

Cte and Temptables in SSIS

Sachin4all

Ten Centuries

Points: 1249
More actions
June 20, 2011 at 7:34 am

#242914

Does any one have any idea on how to use CTE and TempTable in SSIS

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

You can certainly use them in Execute SQL task, or perhaps in your dataflow source. You are really going to have to elaborate on your concern/question to get a useful answer. What is your intention?

Sachin4all Ten Centuries Points: 1249 More actions · Answer 2

I would like to create a temp table #hs in the execute sql task becuase i dont want create a staging table

in the production database and then use the temptable as the staging table for my ssis package.

I would like to use the temp table in the DF task.

For example

In the oledb source connection manager,

I would like to use the temptable in the Sql command.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

You certainly won't be able to use a CTE, since it only lives as long as the batch that creates it. If you can find some way in SSIS of making sure that all your tasks use the same connection, you may be able to use a local temp table. Otherwise, you're looking at a global temp table. Make sure you delete it when you're finished, and test for its existence before you start. There's a few articles out there on this subject, so have a quick search before you decide what to do.

John

Sachin4all Ten Centuries Points: 1249 More actions · Answer 4

Thanks John

i found a article

http://consultingblogs.emc.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx