SSIS 包不想获取临时表的元数据 [英] SSIS Package not wanting to fetch metadata of temporary table

问题描述

我有一个包含多个流的 SSIS 包.

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.

我为另一张桌子添加了 1 个额外的流(我没有制作包)，它的作用与上述完全相同.但是，由于某种原因，该包在此流程上间歇性失败，而除了一些表名外，它与其他完全相同.

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.

不断弹出的错误:

更新 - 插入数据流:错误:SSIS 错误代码 DTS_E_OLEDBERROR.一个发生 OLE DB 错误.错误代码:0x80004005.OLE DB 记录是可用的.来源:Microsoft SQL Server Native Client 11.0"Hresult:0x80004005 描述:未指定的错误".一个 OLE DB记录可用.来源:Microsoft SQL Server Native Client11.0" Hresult: 0x80004005 描述: "无法确定元数据，因为语句 'select * from##TmpMcsConfigurationDeviceHistory86B34BFD041A430E84CCACE78DA336A1"使用临时表.".

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.".

创建表达式:

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

推荐答案

使用 WITH RESULTSETS 显式定义元数据 将允许 SSIS 跳过 sp_describe_first_result_set 步骤并使用您定义的元数据.好处是你可以使用它来让 SSIS 执行包含临时表的 SQL(对我来说，性能帮助很大)；缺点是，如果有任何变化，您必须手动维护和更新.

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

查询示例(简单的 SQL:)

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

这篇关于SSIS 包不想获取临时表的元数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！