none
Temporary Table load in Data Flow

    Question

  • In my SSIS package, I am creating a temporary table in my control flow using an 'Execute SQL task'. Then inside Data FLow, I want to load this temp table with some data and then finally coming back to control flow I want to update my target table from this temporary table data through an Sql task.

    The problem I am facing in my Data Flow. I want to keep my temporary table as destination inside Data flow and so planned to use an OLE DB destination task to load data into this temporary table. But while doing so, I could not select this temporary table from my list of tables available.

    Actually this temporary table is created under the 'tempdb' of system database not inside  my defined database. So it is not coming up in my drop down list in Destination task.

    I also tried the SQL command option inside 'OLE DB Destination'. But while writing the Insert Query it is not allowing me to load the data from the input columns. By the way, this OLE DB destination is fed from a Lookup task.Can anyone suggest how can I implement this? Am I missing anything? Any urget help will be really useful.

     

    Thanks !!

     

    Tuesday, September 23, 2008 12:23 PM

Answers

  • 1. Create another connection manager to point to the tempdb database and use it in your destination.

    2. The list will not contain the table if it does not exist in the design-time. Create the table once do you can configure the destination component, then you can create nad drop it in every execution session.

     

    HTH,

    Bob

    Thursday, September 25, 2008 5:36 PM

All replies

  • 1. Create another connection manager to point to the tempdb database and use it in your destination.

    2. The list will not contain the table if it does not exist in the design-time. Create the table once do you can configure the destination component, then you can create nad drop it in every execution session.

     

    HTH,

    Bob

    Thursday, September 25, 2008 5:36 PM
  • Hi, I had the similar situation too. I used Execute SQL task to create a table and load data to it. After doing other transformations on data I moved data to my destination table and truncated that temporary table. Hope this helps.
    Thursday, September 25, 2008 7:41 PM
  • Thanks !! it's working now. I had to create a new connection manager pointing to tempdb database.

     

    Friday, September 26, 2008 6:20 AM