Why Does My SSIS Package Fail to Acquire Metadata for a Temporary Table, and How Can I Fix It Using `WITH RESULT SETS`?

SSIS Package Stalls Acquiring Metadata for Temporary Table

In an SSIS package, multiple flows create global temporary tables as staging areas for data. However, one flow encounters an intermittent failure when attempting to fetch metadata for its temporary table. The error message reads:

Unspecified error. The metadata could not be determined because statement 'select * from '##TmpMcsConfigurationDeviceHistory86B34BFD041A430E84CCACE78DA336A1'' uses a temp table.

The creation expression for the temporary table is:

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)

Evaluated, this expression becomes:

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)

Solution

To resolve this issue, consider using WITH RESULT SETS to explicitly define the metadata for the temporary table. This allows SSIS to bypass the sp_describe_first_result_set step and use the provided metadata.

Implementation

For stored procedures, use the following syntax:

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

For simple SQL queries, use the following syntax:

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
        )
    )

The above is the detailed content of Why Does My SSIS Package Fail to Acquire Metadata for a Temporary Table, and How Can I Fix It Using `WITH RESULT SETS`?. For more information, please follow other related articles on the PHP Chinese website!