In case both the OLE DB Source and the OLE DB Destination use the same database, a seperate Connection must be created for the session does not use temp table.
My imaginary scenario is one where I want to create a temporary table using an Execute SQL Task and then consume it using an OLE DB Source component in a data-flow task. Here’s what you have to do:
1.Create an OLE DB Connection Manager to point at a SQL Server database .
2.Set connection manager property RetainSameConnection=TRUE. As explained earlier this is a fundamental step in getting this to work.
3.Drag on an Execute SQL Task and configure it to create a global temp table.
4.Copy the same CREATE statement from the Execute SQL Task, paste it into SQL Server Management Studio, and create the table from there. This gets around the problem of the table not existing when it is initially created by a SSIS package at design-time. It has to be a global temp table in order that it can be accessed by your package at design-time.
5.Create a data-flow task that consumes your global temp table in an OLE DB Source component.
6.Set DelayValidation=TRUE on the data-flow task.
At this stage you will have a package that can be executed successfully. It will create a global temp table and consume it. If you want the temp table to be scoped locally instead of globally then there are a few extra steps that you need to do:
1.On the SSIS menu in BIDS, select ‘Work Offline’.
2.Change SQLStatementSource property of Execute SQL Task to create a local temp table instead of global.
3.Change SQLCommand property of OLE DB Source to use the local temp table instead of global. You will have to use the Properties window to do this because if you try to do it in the component editor it will try and validate the external metadata.