在 ssis 包中使用临时表

本文介绍了在 ssis 包中使用临时表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个问题 - 我想在存储过程中使用临时表是 SQL Server，它将从 SSIS 包中执行.我阅读了一些技巧，我尝试了这个(第一个答案):Using Temp tables inSSIS但它没有用.我有 MS Visual Studio 2010，这个版本不会有问题吗?这是我在存储过程中的代码:

i have a problem - I want to use temp table in stored procedure is SQL Server, which will be executed from SSIS package. I read some tips how to do it and I tried this one (first answer): Using Temp tables in SSIS but it didn't work. I have MS Visual Studio 2010, couldn't be problem with this version? Here is my code in stored proc.:

CREATE PROCEDURE some_procedure AS SET NOCOUNT ON IF 1 = 0 BEGIN SELECT CAST(NULL AS int) as number END CREATE TABLE #some_table (number int) INSERT INTO #some_table VALUES (250) SELECT number FROM #some_table

感谢您的任何建议或经验.

Thanks for any advice or experience.

这是来自 Visual Studio 的错误消息:

Here is error message from Visual Studio:

数据流任务中的错误 [OLE DB 源 [1]]:SSIS 错误代码DTS_E_OLEDB 错误.发生 OLE DB 错误.错误代码:0x80004005.OLE DB 记录可用.来源:微软 SQL服务器本地客户端 11.0" Hresult: 0x80004005 描述: "无法确定元数据，因为语句INSERT INTO"过程some_procedure"中的#some_table VALUES (250)' 使用临时表.".

Error at Data Flow Task [OLE DB Source [1]]: 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: "The metadata could not be determined because statement 'INSERT INTO #some_table VALUES (250)' in procedure 'some_procedure' uses a temp table.".

数据流任务 [OLE DB 源 [1]] 出错:无法检索列来自数据源的信息.确保您的目标表在数据库可用.

Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

推荐答案

在 SQL Server 2012 中，如果使用临时表，则必须指定结果集.

In SQL Server 2012 if you use temporary tables you must specify a results set.

这是 SSIS 用于返回输出元数据的 sp_describe_first_result_set 过程的问题.

This is an issue with the sp_describe_first_result_set procedure that SSIS uses to returns the output metadata.

例如

EXEC dbo.RptResults_StoredProcedure

成为

EXEC dbo.RptResults_StoredProcedure WITH RESULT SETS (( Date NVARCHAR(10), Location VARCHAR(12), Department CHAR(1), Shift CHAR(1), ForecastSales DECIMAL(18,2), ActualSales DECIMAL(18,2) ))

查看更多信息

blog.concentra.co.uk/2014/08/22/column-metadata-determined-correctly-ssis-data-flow-task-stored-procedure-inputs/