SSIS 包不想获取临时表的元数据

本文介绍了SSIS 包不想获取临时表的元数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个包含多个流的 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 ) )