Tuesday, September 15, 2015

How to create and use temp tables in SSIS

Creating temp table by using SQL is very easy process. Some times we require to create temp tables in SSIS packages for this we need to configure some properties while creating the ssis package.

Creating a test SSIS  package for  temp table creation:-

 1) Drag execute sql task from SSIS Toolbox and rename it to temp table

 2)Double click on Execute sql task and select your connection string and in your sql statement paste your   SQL statement i pasted below sample

IF OBJECT_ID('tempdb..##temptest') IS NOT NULL
    DROP TABLE ##temptest
    CREATE TABLE ##temptest
    (
        id VARCHAR(255),
        name VARCHAR(255),
        city  VARCHAR (2)
    )
    INSERT INTO ##temptest VALUES
       ('hyderabad', 'cycle', 'ap'),
       ('delhi', 'bike', 'DL')


click ok.

3) Drag a data flow task from SSIS Tool box





4) Double click on the data flow task  after entering into it drag one OLEDB source. now we will pull data from temp table and insert into database by using oledb destination. To avoid error create global temp table in SSMS 



5) Again go to SSIS package and in data flow task go to oledb  select data access mode as SQL command
select the temp table. Click on oledb destination select data access mode as table or view fast load and create table and click ok

6) Once finished above task start debugging. Now you will see below error in execution results.


7)Now we will fix the issue go oledb connection and right click on it. Put the property  retain same connection as true check below image and the delay validation properties as true

8)Now execute the package  successfully executed.


9)Now check in SSMS data loaded into destination.

10) Why global temp table is used here ?

Ans:- We can create it in SSMS and use it in SSIS package because of  global access(##testtemp)(create in any session access in any session). if we use local temp(#testtemp) we face below issue and session specific