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?