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.