SSIS Package not wanting to fetch metadata of temporary table

Question

I have an SSIS Package, which contains multiple flows.

Each flow is responsible for creating a "staging" table, which gets filled up after creation. These tables are global temporary tables.

I added 1 extra flow (I did not make the package) which does exactly as stated above, for another table. However, for some reason, the package fails intermittently on this flow, while it is exactly the same as others, besides some table names.

The error that keeps popping up:

Update - Insert Data Flow: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: "Unspecified error". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because statement 'select * from '##TmpMcsConfigurationDeviceHistory86B34BFD041A430E84CCACE78DA336A1'' uses a temp table.".

Creation expression:

"CREATE TABLE " + @[User::TmpMcsConfigurationDeviceHistory]  + " ([RecId] [bigint] NULL,[DataAreaID] [nvarchar](4) COLLATE database_default NULL,[Asset] [bigint] NULL,[Code] [nvarchar](255) COLLATE database_default NULL,[Configuration] [bigint],[StartdateTime] [datetime] NULL,[EndDateTime] [datetime] NULL)

"

Parsed expression (=evaluated):

CREATE TABLE ##TmpMcsConfigurationDeviceHistory764E56F088DC475C9CC747CC82B9E388 ([RecId] [bigint] NULL,[DataAreaID] [nvarchar](4) COLLATE database_default NULL,[Asset] [bigint] NULL,[Code] [nvarchar](255) COLLATE database_default NULL,[Configuration] [bigint],[StartdateTime] [datetime] NULL,[EndDateTime] [datetime] NULL)

Greaves

Answer 1

Using WITH RESULT SETS to explicitly define the metadata will allow SSIS to skip the sp_describe_first_result_set step and use the metadata that you define. The upside is that you can use this to get SSIS to execute SQL that contains a temporary table (for me, that performance helped a lot); the downside is, you have to manually maintain and update this if anything changes.

Query sample (stored procedure:)

    EXEC ('dbo.MyStoredProcedure')
    WITH RESULT SETS
      (
        (
            MyIntegerColumn INT NOT NULL,
            MyTextColumn VARCHAR(50) NULL,
            MyOtherColumn BIT NULL
        )
      )

Query sample (simple SQL:)

EXEC ('
    CREATE TABLE #a 
      (
        MyIntegerColumn INT NOT NULL,
        MyTextColumn VARCHAR(50) NULL,
        MyOtherColumn BIT NULL
      ) 
    INSERT INTO #a 
      (
        MyIntegerColumn,
        MyTextColumn,
        MyOtherColumn
      )
    SELECT 
        1 AS MyIntegerColumn,
        ''x'' AS MyTextColumn,
        0 AS MyOtherColumn

    SELECT MyIntegerColumn, MyTextColumn, MyOtherColumn
    FROM #a')

WITH RESULT SETS
    (
        (
            MyIntegerColumn INT NOT NULL
           ,MyTextColumn VARCHAR(50) NULL
           ,MyOtherColumn BIT NULL
        )
    )

Agnusago

This is the answer that worked for me. The above answer with FMTONLY commands ended up sending 0 rows to the output during actual ETL. – Sclerophyll

Thanks - this was a really useful solution that I just stumbled upon. – Craigcraighead

Works! I'm on Visual Studio 2017. I'm working with small, mature set of stored procedures, so this method is fantastic for procs that don't need to change often. Thanks, Higgins. – Contrabandist

Fantastic solution, it worked for me where I needed to use multiple #TempTables to optimize query performance. I can sleep easy tonight, so thank you for sharing this gold nugget AHiggins & MrEdmundo – Cathexis

Did you try the following approach- create the temp table via management studio. Then in the data flow task's source oledb sql command text you can make use of global temp tables. – Voyeur

Answer 2

Another option (kind of a hack, but it works and doesn't require you to change your use of global temp tables) is to use a SET FMTONLY ON command in front of your actual query to send a fake "First result set" to SSIS with your correct column structure. So you can do something like

SET FMTONLY ON
select 0 as a, 1 as b, 'test' as C, GETDATE() as D
SET FMTONLY OFF

select a, b, c, d from ##TempTable

When SSIS runs sp_describe_first_result_set, it will return the metadata and column names of your FMTONLY command, and won't complain about not being able to determine the metadata of your temp table because it won't even try.

Openhearth

Thanks, I was in a spot where there was a trigger using a temp table and I couldn't touch the trigger (don't ask)... This was what I needed – Sessler

A year and a half later and still helpful. Thanks Kyle. – Diecious

It worked. I had to take a few more steps to make it work. My query had 40+ fields, some of them bigint, some decimal(19,4). In the fake select inside the FMTONLY i had to write many times something like CAST(NULL AS DECIMAL(19,4)) as MyDecimalField. – Eakin

Just FYI: SET FMTONLY has been deprecated since SQL Server 2012. msdn.microsoft.com/en-us/library/ms143729(v=sql.110).aspx – Spic

Answer 3

If you are working on SSIS 2012, then it uses system stored procedure sp_describe_first_result_set to fetch the metadata of the tables and it does not support temporary tables. But you can go for other options like table variables and CTEs which are going to work fine. https://connect.microsoft.com/SQLServer/feedback/details/629077/denali-engine-metadata-discovery-shuns-temp-tables

Indoiranian

I use table variables in my data flows(It's my first day using SSIS so I hope I'm correct), they just contain the DDL for creating the temp table, like all the other data flows (while it works in the other data flows). – Greaves

As I can see it in your error description, a select query is running against the temp table which will in turn ask for meta data. select * from '##TmpMcsConfigurationDeviceHistory86B34BFD041A430E84CCACE78DA336A1' – Indoiranian

Please go through this data flow and confirm if the above mentioned query is running or not. – Indoiranian

Just FYI: By table variables, Soram means SQL table variables (@tables) not SSIS Variables that contain DDL. – Openhearth

Table variables will work "fine" for small result sets. The performance is going to be awful once there are a few hundred rows. – Diatessaron

Answer 4

I had faced a similar issue when SSSI packages were migrated from 2008 to 2016. The latest version uses sp_describe_first_result_set to fetch metadata and it does not work with temporary tables. As a workaround, I used the below query in the OLEDB source editor. I did not change the SQL stored procedure, and it still uses a temporary table. Do be sure to use the Parse Query and Preview option to ensure it works fine. See the image below.

Query:

EXEC [dbo].[spGetNames]
WITH RESULT SETS((
        FirstName varchar(50), 
        LastName varchar(50)
));

Throng

Answer 5

Had the same issue as we use temp table for staging. After spending some time, found a work around.

In the OLE DB/ADO Destination of Data flow task where you specify the name of the staging table .

Change the AccessMode property to SQL command instead of OpenRowSet and specify SQL Command property to "select * from #temp".

Hurray, Its working as expected.

Catch here is when you specify Access mode other than SQL Command, SSIS expects that to be a table / view and it changed the SSIS to call sp_describe_first_result_set to get the meta data. but when you specify SQL Command, it's expecting a query or SP command etc. so luckily it still uses the old way of getting the meta data .

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/cfe1c7c1-910a-4f52-9718-c3406263b177/usage-of-temp-tables-in-ssis-2012?forum=sqlintegrationservices#cfe1c7c1-910a-4f52-9718-c3406263b177

Tadeo

Answer 6

I found that the problem lied in a GUID duplicate issue, I copied elements (like the one to create temp tables) and they all received the same guid upon copying. I used a tool to reset all these guids in my package and this solved my problem.

Thanks!

Greaves