ETL with Temp Tables - Query between two servers

Here’s my problem:
Two database servers – I gather the id’s  I need in one database – run queries against those ids in the other database.
Take the results and write it to the first database and export it to excel.
 
My problem is ETL does not like temp tables and my company does not allow linked servers or queries between servers.
 
I cannot get ETL to like the temp table even with the Delay Verification and the Retain Connection.
 
Any thoughts
ralphp1355Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Tell me more about 'write it to the first database'.  

Bunch of ideas...
Is this required (i.e. can you just do the Export to Excel), the temp table we're talking about?
Can this data be written to a less restrictive sandbox db?
Can you do an Execute SQL task that drops/creates the table, then a delay validation data pump?    Single data pump with the two sources and some kind of join / merge join / cashed data set?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ralphp1355Author Commented:
Is this required (i.e. can you just do the Export to Excel), the temp table we're talking about?
I can just export to excel - but each subsequent query has pieces of information that at the end will be exported

Can this data be written to a less restrictive sandbox db?
Yes, that is what I am trying to do. Grab the ids I need, put them in a temp table and join them to the query of the master database.

Can you do an Execute SQL task that drops/creates the table, then a delay validation data pump?  
Yes. but it gives me an error on the #TEMP table

Single data pump with the two sources and some kind of join / merge join / cashed data set?
Hmmmm - I think the data is too much for that, thats why I was trying to get the #temp table working.
Anoo S PillaiCommented:
Think you are using local temporary table, try global temporary table instead.
Note:- Ensure that the same table name does not interfere with any other global temp tables.
Vikas GargAssociate Principal EngineerCommented:
Hello,

You can try the method just shown in the Image.

Solution
Take two sources
Union the results
and then do merging for joining operation

Hope this will do
Vitor MontalvãoMSSQL Senior EngineerCommented:
ralphp1355, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.