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

Question

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

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

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

FunkyDexter Hall of Fame Points: 3907 More actions · Answer 1

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.

David Burrows SSC Guru Points: 65142 More actions · Answer 2

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.