Post reply

How to a use a temp table as a destination for a dataflow in SSIS

  • August 20, 2018 at 3:22 am

    #2002443

    OK, that's working for one table (I've got a bunch of others to roll it out to).  I do have a follow up question though.  Is there any reason I couldn't do that with local, rather than global temp tables?  To my mind, as long as I've got RetainSameConnection set to true, this should be the same as creating the temp table in SSMS Query Window then running a select against it from the same window.  Or doing the same from an app client but stopping at a breakpoint between the two commands.  As long as I'm retaining the same connection am I not effectively in the same session?  So shouldn't I be able to use a local temp?

    I've tried, of course, but it doesn't seem to work. When I open the mapping tab I get invalid object errors so I'm guessing the local table drops out of scope and I suspect this isn't something I can do anything about.  I'm curious as to why though.

  • August 20, 2018 at 6:10 am

    #2002455

    FunkyDexter - Monday, August 20, 2018 3:22 AM

    OK, that's working for one table (I've got a bunch of others to roll it out to).  I do have a follow up question though.  Is there any reason I couldn't do that with local, rather than global temp tables?  To my mind, as long as I've got RetainSameConnection set to true, this should be the same as creating the temp table in SSMS Query Window then running a select against it from the same window.  Or doing the same from an app client but stopping at a breakpoint between the two commands.  As long as I'm retaining the same connection am I not effectively in the same session?  So shouldn't I be able to use a local temp?

    I've tried, of course, but it doesn't seem to work. When I open the mapping tab I get invalid object errors so I'm guessing the local table drops out of scope and I suspect this isn't something I can do anything about.  I'm curious as to why though.

    I had the same problem and could not figure out why, that is why I used a global temp table.

    One possibility maybe

    Create permanant table with the same structure as the temp table
    Set ValidateExternalMetaData to true
    Use the permanent table to do the mapping.
    Edit the properties, set ValidateExternalMetaData to false and overwrite openrowset property with the temp table name.

    Even if this works it is still a worse hack than using a global temp table.

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 2 posts - 31 through 31 (of 31 total)

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