Using Temp Tables in SSIS

Temporary
tables are created in the TempDB database, which persists for a particular session. The
objective is to maintain that session, until the temp table information
is used and dump the data into a physical table. To maintain a session in SSIS, there is a
‘Retain Same Connection’ property of the Connection Manager. If we need to
maintain a single session, we have to mark this property ‘True’.

Let us
start with a sample package. I will create the package step by step.

  1. Create two Connection manager’s – ‘Server 1′ and ‘Server 2′. Server 2 will be
    the server where we need to create the temp table using Server 1 and thereafter use that
    temp table in the queries executed on Server2.

    image

  2. Set
    the RetainSameConnection property of the Server 2 to True.

    image

  3. Drag an
    Execute SQL Task in the Control Flow.

    image

  4. Insert
    the SQL Statement create table #temptestusers (id int) inside the
    SQLStatement field in the Execute SQL Task Editor. This will create the temp table. Also set the Connection to
    Server2.

  5. Drag a
    DFT below the Execute Sql Task, which will insert the data in the temp table.

  6. Drag
    OLEDB source and OLEDB destination in the DFT

  7. In the
    OLEDB Source, enter the query

    SELECT 1 as id
    UNION ALL
    SELECT 2 as id
    

  8. The next
    step is to connect the ‘OLEDB Destination’.  Our objective is to load the data
    into a temporary table. However, you will not be directly able to select the temp
    table in Design mode. So, we will create a new temp table, if you do not have
    table creation rights, you can create a global temporary table and select
    that. Here, we will create a TestDestination table and map the Source and
    Destination columns :

  9. Now, as we mapped it in design mode, we can modify it
    later to use the Temporary table. To use Temporary table in the Destination, follow
    the below steps:

    a) Go to SSIS Menu > Select Work Offline

    o

    b) Select the OLEDB Destination > Properties >
    Set the OpenRowset property to use the temporary table ‘dbo.[#temptestusers]‘

  10. Now, we can verify the data in the temp table. First of
    all Deselect the ‘Work Offline’ mode, which was used in above step. To verify
    we will add other Data Flow Task which will query this temp table created above
    and will load the data from it to another server table. Add a  second DFT, ‘Use
    the temp table and insert in physical table’.

  11. Add OLEDB Source and Destination in the new DFT. Use
    SQL to select the rows from temp table select id from
    #Temptestusers
    .

  12. You will not be able to select it in design mode, as the
    temp table does not exist right now. So, first select from any other table and
    start the DFT working, then follow the same steps as shown above. ‘Go to
    the SSIS menu -> Select Work Offline -> Go to the OLEDB Source Properties
    -> Select SQLCommand and modify the query in the String Value editor to use
    the temp table.

  13. Next in the OLEDB Destination, select the server on which
    you have permission to create table, in this case I am selecting Server1.

  14. That’s it. The temporary table package is now completed.
    We can run the package and review the flow of data.

    2 Rows transferred into #temptestusers table.

    2 rows transferred to the physical table from temp table.

Hopefully this will help anyone to make use of
temporary tables in their design. The only thing is to make sure of is that we set
‘Work Offline’ flag on/off in the package.




Array

5 Responses to “Using Temp Tables in SSIS”

  1. What is the advantage using the temptable?

  2. Hi Divya,

    I Tried to follow you example, but I am getting error when running the SSIS package saying the required #temp table is not exist. Not sure if I am missing anything.

    Could you please provide me the sample SSIS package to my email id.
    sanjay.bollina@gmail.com

    Thanks
    Sanjay

  3. Hi Divya

    I found the issue, you need to set DelayValidation for that task as “Tue”

    Thank You.

  4. Fantastic! I even did it within a Sybase database with the tempdb. I used ##table_name though to make sure it’s same connection but it does work.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |