Using a local temporary table between different components in SSIS

The Connetion property Retainsameconnection is a Boolean value that specifies whether to keep the same link, and the default value is False, which means that each component will use connection alone. Open connection at the beginning of component and close connection at the end of component. Different componet are used between different connection.

Because the life cycle of a local temporary table is in the current connection, it terminates when the link is closed, and the temporary table does not exist. Using temporary tables between different components in SSIS requires setting the Connetion property retainsameconnection=true so that all task,component will use the same connection. The life cycle of a temporal table is extended to different componet,task.

example, using temporary tables in different componet,task

1, Design control flow

Taskcreate SQL statement for temporary table

if object_id ('tempdb.. #dt_temporary'isnullint)

SQL statement for TASK EXEC SQL statement

INSERT into #dt_temporaryvalues (1)

Task Insert into Dt_test SQL statement

Insert  into dbo.dt_test (ID) Select ID  from #dt_temporary

SQL statement for Task drop temporary table

Drop Table #dt_temporary

2, set the properties of the Connetion retainsameconnection=true

3, execute Package, view results

4, if you set Connetion property Retainsameconnection=false, when you execute the package, an error occurs,

[Execute SQL Task] Error:executing the query "
INSERT INTO #dt_temporary
VALUES (1) "failed with the following error:" Invalid object name ' #dt_temporary '. ". Possible failure Reasons:problems with the query, ' ResultSet ' property not set correctly, parameters not set correctly, O R connection not established correctly.

The cause of the error is explained above: Because the task create temporary table ends, its link closes, causing the temporary table life cycle to end, and the temporary table is destroyed by SQL Server. In task Exec SQL statement, reopen the link and cannot use a temporary table that does not exist, so the error "Invalid object name ' #dt_temporary '.".

5, the SQL variable declared in the task cannot be used between tasks because the life cycle of the SQL variable is in current batch, not connection. Each task executes a batch. When batch ends, the SQL variable life cycle ends.

In SSMs, the GO statement identifies the end of a batch, and the following statement executes with an error because the lifetime of the SQL variable is within a batch.

The life cycle of a local temporary table is in a connection, and the life cycle of the temporary table terminates at the end of the current connection.

Using a local temporary table between different components in SSIS