3

I have a data flow in a 2012 SSIS package where I'm trying to use a temp table as the data source. I'm using an OLE DB data source with a SQL Command Data access mode.

My code looks like this:

CREATE TABLE #Checksums (DBName sysname, CheckSum bigint)
-- Code that loads the Checksum table here
SELECT DBName, CheckSum FROM #Checksums

In order to get the data source to pull the list of columns I temporarily put the following:

SELECT CAST(NULL AS sysname) DBName, CAST(NULL as bigint) CheckSum

That got the columns created and I was able to map to my destination.

I have ValidateExternalMetadata set to false on the data source and DelayValidation set to true on the data flow.

My code works correctly in SSMS however every time I run the package it errors out with the following:

[OLE DB Source [39]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Invalid object name '#qtemp'.".
[OLE DB Source [39]] Error: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
[SSIS.Pipeline] Error: OLE DB Source failed the pre-execute phase and returned error code 0xC020204A.

Is it possible to use a temp table like this in a data source? If so is there a trick I'm missing?

5
  • 1
    Are you setting the RetainSameConnection on the Connection Manager to TRUE? See if this helps: stackoverflow.com/questions/5631010/…
    – RLF
    Aug 4, 2014 at 18:42
  • @RLF I'm doing this in a loop hitting multiple servers will that affect this? Aug 4, 2014 at 19:09
  • @KennethFisher Sorry, I have never tried to hit multiple servers in a loop from SSIS. It seems logically possible to have an outer SSIS package call a sub SSIS-package that would connect to and retain a connection for a single server, then close that connection. But it is over my SSIS head.
    – RLF
    Aug 4, 2014 at 19:32
  • @RLF I've done the inner/outer package thing but prefer to use expressions to change the server name of the connection manager in a for each loop. I'll give the other a shot. Maybe since it's change server names that will force a connection change at the appropriate time. Aug 4, 2014 at 20:17
  • 1
    @KennethFisher Jaime Thomson (SSIS Junkie) shows Using temporary tables as well as on SE - How to create a temporary table in SSIS control flow task and then use it in data flow task?
    – Kin Shah
    Aug 5, 2014 at 3:18

3 Answers 3

3

These steps helped me:

  1. Write the final result set into a table.
  2. Script that table as CREATE into a new New Query Editor Window.
  3. Remove everything except the open and close brackets that define the columns.
  4. Wrap that into another pair of brackets.
  5. Recompose the calling of your SP from

    exec p_MySPWithTempTables ?, ?
    

into

exec p_MySPWithTempTables ?, ? with result sets
(
    (
        ColumnA int,
        ColumnB varchar(10),
        ColumnC datetime
    )
)
1

The temp table will confuse SSIS. To get around the issue, write your code like this:

Use Database
Begin
    Exec('Create Table ##Table(Field Integer)') With Result Sets None;
    Exec('Select Field From ##Table') With Result Sets
    ((
        Field Integer
    ));
End

This allows the use of the temp table and provides the metadata to SSIS.

3
  • 1
    Dropping local temporary tables is handled automatically when they go out of scope, but you are suggesting a global temporary table, and the behaviour is different for those. So perhaps this will resolve the OP's issue, but won't it introduce another one?
    – Andriy M
    Dec 9, 2015 at 6:47
  • 1
    Please explain "confuse". What is confusing and why is it "confusing"? Dec 9, 2015 at 8:35
  • @AndriyM The global temp table will drop automatically when the last session which has an open transaction on that table will disconnect. In the SSIS scenario that most often means after the package is executed. So Chris's solution is fine.
    – ajeh
    Jul 5, 2017 at 13:51
0

I know this is an old post, but I thought I would offer another solution that I have found helpful not just in SSIS, but in Management Studio Import Wizard, PowerPivot, etc.. If you don't need to index your temp table, you could always use table variables instead. As long as you use the "set nocount on" hint at the beginning, you should be able to use this in your SSIS data flow source. Your new code should look something like this...

set nocount on

declare @Checksums table (DBName sysname, CheckSum bigint)
--Code that loads the Checksum table here
select DBName, CheckSum from @Checksums
1
  • As long as you're executing all your code in a single SQL batch (and thus, in a single SSIS task), this is true. With a temporary table, in theory, if you set RetainSameConnection to true, then you can use a temp table in separate SSIS tasks.
    – RDFozz
    Sep 6, 2018 at 23:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.