Post reply

Cte and Temptables in SSIS

  • June 20, 2011 at 7:34 am

    #242914

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

  • June 20, 2011 at 7:40 am

    #1341195

    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?

  • June 20, 2011 at 8:25 am

    #1341227

    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.

  • June 20, 2011 at 8:39 am

    #1341236

    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

  • June 20, 2011 at 9:19 am

    #1341265

    Thanks John

    i found a article

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

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

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