在 ssis 包中使用临时表 [英] Using temporary tables in ssis package

问题描述

我有一个问题 - 我想在存储过程中使用临时表是 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)
))

查看更多信息

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

这篇关于在 ssis 包中使用临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！