none
Creating and accessing temp tables in SSIS package

    Question

  • Hi,

     

    I want to create a local temporary table in execute sql task and and want to use the same in Data flow task as source table.

     

    I follow the following steps to achieve this:

     

    01. Created a new SSIS package

    02. Create a connection string to "(local)/." server, "tempdb" database

    03. Set the "RetainSameConnection" property value to "TRUE"

    04. Set the "DelayValidation" to "TRUE", where ever I found this property

    04. In Control Flow I added to items

          a. Execute SQL Task

          b. Data Flow Task

    05. For "Execute SQL task" I set the connection to "tempdb"

    06. I written the following query

           Create table #transfer_CompaniesToProcess_tbl
           (
              companyID int not null 
           )
           GO
    07. In Data Flow task I added "OLE DB Source" and "OLE DB Destination"

    08. In "OLE DB Source" I changed the "Data access mode:" to "SQL command"

    09. In "SQL command text:" I entered "select * from #transfer_CompaniesToProcess_tbl"

    10. When I clicked on the "OK" button; I ended with following error:

     

    TITLE: Microsoft Visual Studio
    ------------------------------

    Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E14.
    An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E14  Description: "Statement(s) could not be prepared.".
    An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E14  Description: "Invalid object name '#transfer_CompaniesToProcess_tbl'.".

     

    ------------------------------
    ADDITIONAL INFORMATION:

    Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

     

    I gone through the following article and it seems I missed some thing.

    http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx

     

    Can any one have any idea where I am doing wrong?

     

    Thanks

    Sreekanth

    Wednesday, September 26, 2007 1:39 PM

Answers

All replies

  • That *might* work at runtime, but during design time, I suspect that the same connection isn't being used when you're developing the OLE DB source.  When developing the OLE DB Source, it tries to validate its metadata, but the temp table doesn't exist because it hasn't been created (because it gets created via an Execute SQL Task).

    You likely have to build a physical table that mimics the design of the temp table to use in developing the OLE DB source initially, and then override the SQL in the OLE DB Source at runtime to use the temp table.
    Wednesday, September 26, 2007 1:59 PM


  • Is all this work worth it? Why not having a staging table already created to use by the package; or adding an extra execute sql task at the end to drop the table? Just wondering...
    Wednesday, September 26, 2007 2:35 PM
  •  Rafael Salas wrote:


    Is all this work worth it? Why not having a staging table already created to use by the package; or adding an extra execute sql task at the end to drop the table? Just wondering...


    Fundamentally, I agree with this as well.
    Wednesday, September 26, 2007 2:40 PM
  •  

    Hi Phil Brammer / Rafael Salas,

     

    First thank me for your reply and suggestions.

     

    The main purpose and requirement is as follows:

     

    I had a master server; where I had data for 60 thousand objects in different tables.

    The tables contains millions of rows; every day I need to refresh data related to 10 thousand objects for different reasons.

     

    If I ran the code on the same master server it is taking lot of resources and virtually hangs the server.

     

    I am planning to distribute the refreshed objects on multiple systems (generation); and want to run the refresh code on multiple systems.

     

    ex:

         1000 objects related data on each machine and 10 machines.

     

    I need to pull the data for objects from master server to generation servers at same time (little variable). I will ran the refresh code and append the data back to master server.

     

    This SSIS package will be called by each generation server to pull the data from master server.

     

    If I use the staging table; I don't know which generation server objects related data is available in this staging table. As if two generation systems called this SSIS and asked for data; the staging table will be deleted and re-created.

     

    I used linked servers concept and I lost the indexes and table hints as they are not possible through linked servers. and the queries are taking very longer time than expected. (If I am not wrong some time row-by-row insertion / retrieval is going through linked servers).

     

    To avaoid this slow execution and retrieve the data fast I am planning to use SSIS package with temp tables..!!

     

    Please let me know if I am not clear and suggest me if there is any work around.

     

    The data going to be transfered is millions of rows!!!

     

    Thanks

    Sreekanth

    Wednesday, September 26, 2007 6:02 PM
  •  

    Try following the steps here:

     

    SSIS: Using temporary tables

    (http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx)

     

    -Jamie

     

    Wednesday, September 26, 2007 6:19 PM
  • Hi,

    I know this post was made some time ago. But this aswer can help someone.

    I think you should put ValidateExternalMetadata to true on OLE DB Destinantion after you create in management studio the temp tables.

    Regards

    Marcelo Lamounier de Miranda


    • Proposed as answer by SequelBug Thursday, August 11, 2011 3:33 PM
    Monday, July 27, 2009 1:29 PM
  • Hi,

    I know this post was made some time ago. But this aswer can help someone.

    I think you should put ValidateExternalMetadata to true on OLE DB Destinantion after you create in management studio the temp tables.

    Regards

    Marcelo Lamounier de Miranda


    HI,

    I had the same issue. Created the package as  mentioned in Jamie's blog

    http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx

    and set MARS Connection property in connection manager to TRUE to get this working

    • Proposed as answer by jim07 Friday, April 12, 2013 9:08 PM
    Friday, April 12, 2013 9:08 PM
  • Use global temp table instead of local temp table like ##transfer_CompaniesToProcess_tbl . Also, before running the package run the same create table statement on SSMS and run the package. Hope, this will full fill your requirement.
    Tuesday, May 07, 2013 12:25 PM