Using Temporary Tables With Ssis

http://www.sqllike.com – Free SQL Server Articles   

Using Temporary Tables with SSIS   

Many people keep on asking on different SQL Server forums on how to use a temporary table using in an SSIS Package, below mentioned is a mechanism that I have learned and been using for long time for now, below it is… We are going to take this to achieve this goal, we have to create a temporary table, insert some data into it, and using data flow task which we shall dump this data into a permanent table. We shall take the below script for this, Create table ##TempTable(Id int identity(1,1),FName nvarchar(100),LName nvarchar(100)) Insert into ##TempTable(Fname,Lname)Values ( ‘John’,‘Smith’) Insert into ##TempTable(Fname,Lname)Values ( ‘Aaron’,‘James’) Insert into ##TempTable(Fname,Lname)Values ( ‘Albert’,”)

DROP TABLE ##TempTable

To be done on SSIS Front. 1. 2. 3.

Create a new data source Create a connection manager based on this data source Select the connection manager created in step to and go to properties, and mark the “RetainSameConnetion” as “TRUE” 4. Now in the control flow – drag an execute sql task in to designer space. 5. Copy paste the above SQL statements from the above script into that, except the drop table once. J 6. Now select the execute sql task, go to properties and make the “Delay Validation” property under execution as “True”. Before we create the Data Flow Task, we shall first go to the Management Studio and Create a Global Temporary Table so that we have a basic table to map the columns with

7. 8.

Drag the data flow task into the designer In this Data Flow task, create and OLEDB Source Connection, and have this select query in place. select * from ##TempTable

9.

Now create an OLEDB Destination connection with a suitable data source, use the create new table option to create a table for the dump. That’s it, now right click on the package and say execute, zoom it goes… 1  Using Temporary Tables with SSIS | http://www.sqllike.com – Free SQL 

Server Articles   

http://www.sqllike.com – Free SQL Server Articles   

Now let’s see how we have to convert the global temporary table to a local temporary table. 10. Select the execute sql task, and then properties and under expressions we have our sql query, here, at every place replace”##” with “#”. 11. Now go to the data flow task, and in the properties, custom properties, you will find the sql query in “sqlCommand” property, change it here as well. That’s it, now we have a package with Local Temporary Table as well. Horray!!! Reached our Goal….  

2  Using Temporary Tables with SSIS | http://www.sqllike.com – Free SQL 

Server Articles