[RESOLVED] Can I use a temp table as the destination for a dataflow in SSIS?

[FunkyDexter]

I need to use data from one server to update a table on another server. For reasons of GDPR compliance there cannot be a direct connection between these two servers so I can't use a linked server to do this in a single tsql query. Instead I plan on using SSIS to create a temp table on the destination server, populate it with the pertinent data from the source and then use an execute sql task to update the destination table based on the temp table.

My problem is when I come to set up the temp table as a destination. Because the table doesn't exist at design time it's not available to use on the column mappings tab of the destination wizard. I can find plenty of examples on line of using a temp table as a source but I can't find any that use one as a destination.

**From those examples it occurred to me that I could perhaps use a global temp table (##) but it's still not available as a destination table in the destination wizards drop down so I still can't use the mappings tab. I'd also rather not give it global scope. This process is intended to run as a single instance so collisions shouldn't be a problem but I'd rather not take the risk if I can avoid it.

Similarly, I know I could use "real" table but I'd rather avoid the clutter and, again, I'd rather avoid the global scope.

S**o can I use a temp table in this way? If not I guess I'll fall back on real tables.

[techgnome]

I started to say yes, that I had done it before... then I thought about it some more, and realized what I had usually done in the past, is if I don't need the staging table afterwards is to add a SQL Script action that does a create table to create a table (duh), then use it as the destination table, do what I need to do with the data, then drop table it when done. Or, keep the table and simply truncate it when I'm done to remove the data... You could also add a schema name to the table name that would help limit its scope, so that it's not on the default dbo schema. That's what we did at my previous job in the data warehouse. Some of the data was in the dbo, some of it was in a couple differrent schemas to prevent contamination.

-tg

[szlamany]

How about an actual middleman database - so that you CAN link to it.

[FunkyDexter]

I've been doing some research and basically I'm finding out that, no, I can't

So permanent tables it is. It doesn't really matter where they sit, whether it's a separate DB, schema etc. I'll just create them at the beginning of the process and delete them at the end. This is going to be a rarely run process and it should be rare enough that it won't collide with another run. If it does start needing to ne run more often we can always add a random guid to the tablename for each individual run.

Thanks for the thoughts guys.

[GaryMazzone]

Did you think of pumping the data to the SSIS's raw format that can be used again later in the process with out the connection to the other server

[FunkyDexter]

Not sure I follow what you mean by SSIS's raw format. I can pull it into an object but that can't be used for an update (except one row at a time in a ForEach container).

[GaryMazzone]

It can be used to store data and reload back into another object. Pull data from table on one system place it into the raw format and save it to disk temporarily. Connect to second server and reopen the save raw format in a data flow and use it as normal

[FunkyDexter]

Got you. That's basically what I've ended up with but I had been hoping to avoid the clutter of permanent tables. They come with all sorts of overhead where your trying to avoid collision if the process is kicked off by two separate users at the same time. There's things you can do by, for example, attaching a randomly generated id to the table name, but using temp tables would have been neater. Still, the nature of this is such that simultaneous runs are highly unlikely so permys'll to for now.