我有一个问题 - 我想在存储过程中使用临时表是 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/
更多推荐
在 ssis 包中使用临时表
发布评论