4

My issue is as follows:

I'm trying to create a global temp table using data from DB1, then use that temp table in a query executed in DB2 and then have the results imported into a new table on DB2. Since the query is a bit more complicated a simple Merge join won't do as I need to transform the data and add an extra column (dense_rank) based on data from both Databases. According to one of my colleagues this should all be possible to do in SSIS.

Simplified Query that I would like to execute on connection from DB2:

Select db2.a,db2.b,db2.c,db1.c, db1.d,
DENSE_RANK() OVER (PARTITION BY db2.a order by db1.c asc) as FinalRank
from DB2.table  as db2
left join ##globaltemp as db1 on db2.a=db1.a

After extensive googling and different tries, the DB2 source connection won't find my temp table (##globaltemp) which has been created in DB1.

Is such an operation even possible ?

tldr: Is it possible to create a global temp table in SSIS session and pull data from it no matter the Database?

5

Even a local temporary table (#Table) would work as long as you stayed in a session context. A global temporary table (##Table) is only global in that it can be seen by other sessions.

What is probably happening here is your create ##Table session is going out of scope/context and when the session goes away, there goes it's temp table. Whether local or global. So the process that created the temporary table is out of scope and it's temp table is gone.

In other words - the problem isn't the scope of the database you are connected to. any temporary table can be access regardless of the database you are using. A temp table isn't created in any of your user databases (DB1 or DB2), they are always created in TempDB.

Try using a derived table or consider a staging table, etc.

5

In addition to Mike's fine answer, the setting in SSIS that you are missing is you must specify the RetainSameConnection property is True for your OLE/ADO connection manager. Otherwise, SSIS can/will create a connection for the Execute SQL Task that creates the global table, close that connection and then open a new one for the Data Flow Task in which case the temp table would have been dropped.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.