A tricky but useful SSIS solution

If you have a small table (e.g a set of particular clients) at your database and that you need to get more information from Production OLTP Database from time to time as such;

select c.name, p.production, p.purchase_date
from local_server.dbo.client c
innerjoin production_server.dbo.production_transaction p
on c.client_id = p.client_id

Well, in the most commercial environment, you might have a limit access to Production database and you are not allowed to use linked server. so the SSIS Merge Join Transformation is a common approach. However, there is another much efficient way by using global temp table (##client) to push local table to Production database and run an Execute SQL Task with complex sql script to get your result set, then use Data Flow Task (DFT) get the result set back. it is 20 times faster than merge join transformation, in my case a 200 million production transaction.

As the temp table is not permanent, how to make it appear as destination? how to make it run the ETL without error? Here is the tricky part.

1. Drag an Execute SQL Task to Create Temp Table by using the following script and connection is destination, make sure the property [RetainSameConnection] true

IF OBJECT_ID('tempdb..##abcTable') IS NOT NULL
    DROP TABLE ##abcTable


CREATE TABLE ##abcTable
(
	[abc] nvarchar(255)
)

2. Create another connection manager to point to destination tempdb, make sure the property [RetainSameConnection] true

3. Drag DFT, create source and destination, the destination use tempdb connection.

4. At this stage, you would not see your temp table in destination. You need to run [Create Temp Table] task first. then configure your destination table and mapping with source.You also need to turn [DelayValidation] to true for your DFT

We create a temp table inside a SSIS package that other tasks in the package can use the temp table as long as it use same destination connection, At end of package, we should another task that will drop the temp tables.