none
How to Use Temp tables in ssis 2005 ?

    Question

  • Hi All,

    I hav one sql query containing 3 - 4 Temp tables .

    I need to prepare ssis package . based on this query .

    How to create or use temp tables as source and destination .

     

    Thanks,

    Apps


    • Edited by appy1249 Tuesday, October 25, 2011 7:47 AM
    Tuesday, October 25, 2011 7:39 AM

Answers

  • for me it's better to create a staging table that you'll create at the start of the process and dropping them at the end, using temp table adds complexity during development and when opening the package in design time.

    Aside from db_datareader and db_dbwriter permission, you'll need the db_ddladmin to be able to drop/create the tables on target database.

    Lastly, if you're just linking results from one source and checking it whether it's existing on another you don't need a temp table for it since it can be done by:

    1. Create two OLEDB Source, one for source A and another for source B.

    2. Add sorting on both source. Sort it by the ID.

    3. Use Merge Join (LEFT JOIN).

    4. Use Conditional Split to determine if the record can be found, suppose you link Source A with B. If Source A cannot be found in Source B, the ID of source B will be NULL.

    5. Process records accordingly or you can link the result to another Source.

     

     

     


     

    Randy Aldrich Paulo

    MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog



    Friday, October 28, 2011 2:29 PM

All replies

  • You want to store the output of your results temporarily in some intermediate table and use the same in source and destination.

    you have to create physical tables to store the results 

    you can use temdb database to create those tables

    and delete those tables once your package objective is achieved

    Tuesday, October 25, 2011 8:08 AM
  • What does your package do? Is it stored procedure that contains temporary tables?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, October 25, 2011 8:18 AM
  • Hi Appy1249,

    It is not possible to use temp table in SSRS & SSIS, you have to use physical table for that, but if you really dont want to have that physical table in database, you can drop table at the end.


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/
    Tuesday, October 25, 2011 9:49 AM
  • Hi,

    SSIS goes and check if the tables exist when preparing the package.

    To make it work you need 2 things

    1) Start SSMS and create the temp tables on the database your SSIS package is using

    2) In BIDS find the connection you use in your package, choose properties and set RetainSameConnection = true

    Now you're able to prepare your package.

    You can eventually consider to delay the validation of the package items.

    Regards

    Régis

     

    • Proposed as answer by Koen Verbeeck Thursday, October 27, 2011 9:35 AM
    Tuesday, October 25, 2011 9:57 AM
    Moderator
  • No its just a sql query ....like

     

    1)I am loading the some data into temp table ...

    2)Later i am loading another data into another temp table

    based on first temp table like ..... select * from _____ where ____ not in (select 1 from temp)

    3)finanally i want to  use second temp as source  .

     

     

    Tuesday, October 25, 2011 11:52 AM
  • 1) You can achieve your objective through a single stored procedure

    2) if you want it in SSIS, then use intermediate staging tables since you wont be able to create a temp table in one component and use it in another component

    - you can then delete the tables if required

    Thursday, October 27, 2011 7:32 AM
  • It is not possible to use temp table in SSRS & SSIS, you have to use physical table for that,


    Amit

     

    ... since you wont be able to create a temp table in one component and use it in another component

    Amit and no_prakash, please get your facts straight before posting replies like that on a very frequently visited forum.
    It is perfectly possible to use temp tables in SSIS, as long as you use the work around that Régis suggested.


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...
    Thursday, October 27, 2011 9:39 AM
  • Koen

    What happens when you close the session in SSMS where you had created the temp table ?

    Did you check ?

    will you be able to execute the same package without error containing the temp tables ?

    I think "You have to start a new session and create the tables again to make the package run"

     

    • Edited by no_prakash Thursday, October 27, 2011 1:52 PM
    Thursday, October 27, 2011 1:43 PM
  • Koen

    What happens when you close the session in SSMS where you had created the temp table ?

    Did you check ?

    will you be able to execute the same package without error containing the temp tables ?

    I think "You have to start a new session and create the tables again to make the package run"

     


    Creating the temp table in SSMS is only necessary during design time, so that the package can be configured without SSIS complaining about missing tables. At runtime, the temp table will typically be created using an Execute SQL Task, or maybe in the OLE DB Source itself.
    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...
    Thursday, October 27, 2011 7:06 PM
  • Hi Regis ,

    I tried it  ...but i couldn't able to find temp table in oledb destination ...

     

    How can i ...?

    Friday, October 28, 2011 6:58 AM
  • Hi Regis ,

    I tried it  ...but i couldn't able to find temp table in oledb destination ...

     

    How can i ...?


    Ah yes, that one is a bit special.
    Make sure the temp table is already created (as Régis suggested, you can use SSMS for that).
    Select the OLE DB Destination and press F4 to get the properties. In the OpenRowset property, type in the name of the temp table. Now you can open the OLE DB Destination and edit the mappings.


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...
    Friday, October 28, 2011 7:09 AM
  • Hi Koen,

     

    I tried it in the same way .....

    Everytime it show the error like .... invalid object name #Active

     

     

     

    Friday, October 28, 2011 7:27 AM
  • I tried it in the same way .....

    Everytime it show the error like .... invalid object name #Active

    You did set the RetainSameConnection property on the connection manager?
    The temp table still exists? Meaning, executing the following statement in SSMS works:

    SELECT * FROM #Active
    

    Another option is to try using a global temp table: ##Active.


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...
    Friday, October 28, 2011 7:38 AM
  • I set that property also ....retain same connection ....

     

    Friday, October 28, 2011 9:40 AM
  • Hi appy1249,

    as mentioned by Koen, plz try using global temp variable (##Active) inspite of using (#Active)

    1) create the table ##Active while designing the package.

    It will provide the metadata for mapping the temp table in the OLEDB Source to the Destination

    2) Once the mapping is validated/done, create the same global temp table in an Execute SQL Task before all the tasks using the temp table.

    3) Set DelayValidation = TRUE for the package.

    Set RetainSameConnection = TRUE

    4) Execute the package without caring/changing the mappings in the DFT.

    Local temp Table is dependent on the sessions in SQL Server.

    So it gives the error "invalid object name #Active" from the OLEDB Destination component

    • Edited by no_prakash Friday, October 28, 2011 11:53 AM
    Friday, October 28, 2011 11:49 AM
  • for me it's better to create a staging table that you'll create at the start of the process and dropping them at the end, using temp table adds complexity during development and when opening the package in design time.

    Aside from db_datareader and db_dbwriter permission, you'll need the db_ddladmin to be able to drop/create the tables on target database.

    Lastly, if you're just linking results from one source and checking it whether it's existing on another you don't need a temp table for it since it can be done by:

    1. Create two OLEDB Source, one for source A and another for source B.

    2. Add sorting on both source. Sort it by the ID.

    3. Use Merge Join (LEFT JOIN).

    4. Use Conditional Split to determine if the record can be found, suppose you link Source A with B. If Source A cannot be found in Source B, the ID of source B will be NULL.

    5. Process records accordingly or you can link the result to another Source.

     

     

     


     

    Randy Aldrich Paulo

    MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog



    Friday, October 28, 2011 2:29 PM
  • Lastly, if you're just linking results from one source and checking it whether it's existing on another you don't need a temp table for it since it can be done by:

    1. Create two OLEDB Source, one for source A and another for source B.

    2. Add sorting on both source. Sort it by the ID.

    3. Use Merge Join (LEFT JOIN).

    4. Use Conditional Split to determine if the record can be found, suppose you link Source A with B. If Source A cannot be found in Source B, the ID of source B will be NULL.

    5. Process records accordingly or you can link the result to another Source.

    Wouldn't a Lookup component be much easier than a MERGE JOIN to check if records exists?
    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...
    Monday, October 31, 2011 6:19 AM
  • www.sqlservercentral.com/articles/Integration+Services+(SSIS)/65112/

    Thursday, January 09, 2014 10:39 AM
  • Hi Apps,

    Yes, it is very much possible to use temp table in SSIS at source , but not straight forwad, you have to do some workaround for the same.

    Few information requried.

    Steps

    1. First of all create temp table at your databse with the same name.

    2. Via Query as a source

       Select * from temp table

    Set retain same connection property of connection true


    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Friday, January 17, 2014 8:47 AM