asked on

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

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralphp1355

ASKER

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 Pillai

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.

SOLUTION

Vikas Garg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

ralphp1355, do you still need help with this question?