Loading temporary tables in parallel

Question

I load data from one SQL Server A to temporary table via Execute SQL Task (select * into x from remote_server) and join with another remote SQL Server B in Data flow.

So I have two source in Data Flow:

1.Local temporary table which contains date from SQL Server A (loaded in previous task)

2.Table on remote SQL Server B.

To achieve it I change "RetainSameConnection" connection manager property (which I use it to pull data from SQL Server A to local machine (SSIS server) to TRUE. It works but I cannot load these tasks parallely, because I get:

S [[209]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Invalid object name '##V_DEL'.".

[SSIS.Pipeline] Error: "S" failed validation and returned validation status "VS_ISBROKEN".

How to solve it?

Answer 1

If you are using RetainConnection = True then only 1 object at a time can have the retained connection. This means that if 2 sql tasks run in parallel then only 1 will use the retained connection. Similar to the issue that you cant have a lookup and a dest with the same retained connection. The only workaround is to serialize your SQL calls.

Answer 2

Simple solution: Just take Global temp table creation task in one sequence container and insert part in another sequence container. It will work fine and we can run the 2 task in parallel.

Answer 3

Take temp table creation part in one sequence container and insert part in another sequence container and it will work even if we run task in parallel having more than one Global temp tables.