SSIS Package not wanting to fetch metadata of temporary table

28

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)
sql
sql-server
ssis
ssis-2012
asked on Stack Overflow Aug 20, 2013 by Mortana • edited Aug 20, 2013 by Mortana

5 Answers

44

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
        )
    )
answered on Stack Overflow Feb 4, 2015 by AHiggins • edited May 19, 2020 by AHiggins
15

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.

answered on Stack Overflow Aug 21, 2013 by Kyle Hale
9

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

answered on Stack Overflow Aug 20, 2013 by Sonam • edited Aug 20, 2013 by Sonam
0

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!

answered on Stack Overflow Aug 26, 2013 by Mortana
0

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

answered on Stack Overflow Apr 1, 2014 by vonbalaji

User contributions licensed under CC BY-SA 3.0