Hello,

I have to combine data from DB2 and SQL server and do some manipuation. I wanted to do union all and put in temp table for further manipulation. I created a temp table in control flow, 

CREATE TABLE ##SiteTemp
    (
      LEVEL2 VARCHAR(20),
      LEVEL3  VARCHAR(25)

Then I was trying to use that temp table for destination but I can do see that in destination. I have to automate the package and do that everyday. I read some blogs but did not understand how they did it. 

I did set retainsameconnection to true. I did find this thread but i did not understand how it was done. 

https://social.msdn.microsoft.com/forums/sqlserver/en-US/f2df224e-22ea-4da5-bd9f-63056e25fe83/ssis-data-flow-temp-table-destination

I have two OBL DB sources, Then I have Union ALL and then OLE Destination in data flow.

I have the temp table code in Execute sql task.

How do i achieve that?

• Edited by 1234alex 8 hours 28 minutes ago

Hi

Create a physical table with same name and do all your activity (mapping and manipulation) and run the package . if it working as expected open the package and add the ##  before the table name every where  and save  and rerun the same package .

Here catch is if you do again any changes in any of the task it will throw missing object . So when ever you have to do some modification do with Physical table later on change with ##.

Enjoy...

@mit

The Source Connection manager will not be able to detect the global temporary table.

Simply create a normal that is staging table (truncate before each run), or better yet a staging database with such a table.

You can use the Merge Transformation to combine the data flowing from two different data sources and avoid using the UNION.