Search code examples
ssisetlssis-projects

I don't use temp table in SSIS

I have problem when I used OLE-DB Source temp table in SSIS.

I create temp table in Execute T-SQL Statement Task and I change DelayValidation : True and RetainSameConnection : True . But problem is not solved .

Control Flow

enter image description here

Solution

  • Background

    What's likely happening here is that the table does not exist at the moment. Temporary tables come in two variants: local and global.

    A local temporary table, uses a name with a single sharp/pound/hash/octothorpe prepended to it i.e. #TEMP. The only query that can use that instance of the temporary table is the one that creates it. This is why the advice across the internet says you need to set RetainSameConnection to true to ensure the connection that created the table is reused in the data flow. Otherwise, you're at the mercy of connection pooling and maybe the same connection is used in both places, maybe not and believe me that's an unpleasant bit of randomness to try and debug. The reason for the DelayValidation on the data flow is that as the package starts, the engine will validate that all the data looks as expected before it does any work. As the precursor step is what gets the data flow task into the expected state, we need to tell the execution to only validate the task immediately before execution. Validation always happens, it's just a matter of when you pay the price.

    A global temporary table is defined with a double sharp/etc sign prepended to it, ##TEMP. This is accessible by any process, not just the connection that created it. It will live until the creating connection goes away (or explicitly drops it).

    Resolution

    Once the package is designed (the metadata is established in the data flow), using local temporary table is going to work just fine. Developing it though, it's impossible to use a local temporary table as a source in the data flow. If you execute the precursor step, that connection will open up, create the temporary table and then the connection goes away as does the temporary table.

    I would resolve this by the following steps

    1. Copy the query in your Execute SQL Task into a window in SSMS and create your local temporary table as a global temporary table, thus ##TEMP.
    2. Create an SSIS variable called SourceQuery of type String with a value of SELECT * FROM ##TEMP;
    3. Modify the "Data access mode" from the OLE DB Source from "SQL Command" to "SQL Command from Variable" and use the variable User::SourceQuery
    4. Complete the design of the Data Flow
    5. Save the package to ensure the metadata is persisted
    6. Change the query in our variable from referencing ##TEMP to #TEMP
    7. Save again.
    8. Drop the ##TEMP table or close the connection
    9. Run the package to ensure everything is working as I expect it.

    Steps 2, 3, and 6 in the above allows you to emulate the magician pulling the tablecloth out from underneath all the dishes.

    If you were to manually edit the query in the data flow itself from ##TEMP to #TEMP, the validation fires and since there is no #TEMP table available, it'll report VS_NEEDSNEWMETADATA and likely doesn't let you save the package. Using a variable as the query source provides a level of indirection that gets us around the "validate-on-change"/reinitialize metadata step.