When we are working on the complex SQL script (in Execute SQL
task) sometimes we need to use the temp table. I mean this table is very useful
when we need to store and manipulate an interim result set during ETL or other
data processing operations.
Sometimes we need to create a temp table in one execute SQL
task and we need to use this on another execute SQL task.
Let’s see this demo
In this execute SQL task I am creating the temp table
In the execute SQL task
In this task, I am selecting the data from the temp table.
Now I am running this package.
Ohhhh!
It failed.
See the error
Error:
[Execute
SQL Task] Error: Executing the query "select * from #temp" failed
with the following error: "Invalid object name '#temp'.". Possible
failure reasons: Problems with the query, "ResultSet" property not
set correctly, parameters not set correctly, or connection not established
correctly.
About Connection manager:
Each time a connection
manager is used by an SSIS component, a new connection is created. If we set
RetainSameConnection to true, we tell the connection manager to create only one
connection and hold on to it as long as the package runs. This prevents
temporary tables or transaction to be dropped.
To overcome this issue
we need to set below two property
- RetainSameConnection
- Delay
Validation
To true
Now I am setting both properties on both SQL execute task
Keep in mind: RetainSameConnection property is available on
Connection manager property.
Now I am running this
package.
Now we get the expected
result.
See the execution log
Enjoy the reading.
No comments:
Post a Comment
If you have any doubt, please let me know.