如何从 SSIS 执行存储过程以将其输出到文本文件 [英] How to EXEC a stored procedure from SSIS to get its output to text file
问题描述
我编写了一个存储过程,将记录读入临时表,然后根据临时表中的数据创建一个透视输出.
从 SSMS 运行存储过程工作正常.我现在面临的问题是,我正在尝试创建将执行 sproc 的 SSIS 包,并将其输出写入制表符分隔的文本文件.
我使用的是 Visual Studio 2015.我的第一个问题是,当我尝试在数据流任务中配置 OLE DB 源时,在 SQL 命令文本框中添加 SQL 命令时:EXEC ShopperSkuHistory 并单击确定,我得到这个错误:
我一直在寻找有关此错误的信息,但没有找到任何可以帮助我了解发生这种情况的原因以及如何解决此问题的信息.
我希望通过这篇文章我可以学习如何修复这个错误.
先谢谢你.
这是我的 sproc:
更新代码
ALTER PROCEDURE [dbo].[ShopperSkuHistory]作为开始如果 OBJECT_ID('tempdb..[#ShopperSku_History_Load]') 不是 NULL开始删除表 [#ShopperSku_History_Load];结尾;-- 创建主表创建表 [#ShopperSku_History_Load]([ID] INT IDENTITY(1, 1) 非空, [shopper_id] CHAR(32) 非空, [sku] VARCHAR(100) 非空, time_ added DATETIME)设置无计数;-- 填充表格插入 [#ShopperSku_History_Load] ([shopper_id], [sku], [time_ added])SELECT DISTINCT [cr].[shopper_id], LEFT([cri].[sku], 9) [sku], GETDATE() [time_ added]FROM [dbo].[receipt_item] [cri]内部连接 [dbo].[收据] [cr]ON [cri].[order_id] = [cr].[order_id]WHERE[cri].[list_price] >0AND [cri].[IsInitialPurchase] = 1AND LEFT([cri].[sku], 3) = 'MN0'AND ([cr].[date_entered] > DATEADD(YEAR, -2, GETDATE()))AND EXISTS(从[product] [cp] WHERE [cp].[pf_id] = [cri].[sku] AND [cp].[for_sale] = 1中选择1)AND NOT EXISTS (SELECT 1 FROM [dbo].[shopper] [cs] WHERE [cs].[IsTesting] = 1 AND [cs].[shopper_bounce] = [cr].[shopper_id])ORDER BY [shopper_id];创建表 [#HistoryOutput]([shopper_id] VARCHAR(32), skus 文本)插入 [#HistoryOutput]( [shopper_id], [skus] )选择[购物者 ID], STUFF(( SELECT ', ' + ISNULL([a].[sku], '')来自 [#ShopperSku_History_Load] [a]WHERE [a].[shopper_id] = [b].[shopper_id]为了XML 路径('')), 1, 1, '') [skus]来自 [#ShopperSku_History_Load] [b]GROUP BY [shopper_id];选择[购物者 ID], [skus]从[#HistoryOutput];结尾;
更新错误
来自 HRESULT 的异常:0xC0202009数据流任务 [OLE DB 源 [1]] 中的错误:SSIS 错误代码 DTS_E_OLEDBERROR.发生 OLE DB 错误.错误代码:0x80040E14.OLE DB 记录可用.来源:Microsoft SQL Server Native Client 11.0" Hresult:0x80040E14 描述:无法准备语句.".OLE DB 记录可用.来源:Microsoft SQL Server Native Client 11.0" Hresult:0x80040E14 描述:'shopper_id' 附近的语法不正确.".
这个错误
<块引用>无法确定元数据,因为语句 'EXEC过程XXXXXX"中的XXXXXX"包含动态 SQL.考虑使用 WITH RESULT SETS 子句来明确描述结果集.
发生是因为 SP 包含一个
- 临时表
- 动态 SQL
由于这些动态元素,SSIS/SSDT 无法正确获取列元数据.更多信息
对于 SSIS/SQL 2008,您可以尝试设置 FMTONLY OFF
对于 SSIS/SQL 2012,您可以使用来自 SSIS 的 RESULT SET
包装存储过程.试试这个...
其他选项包括更新存储过程本身和添加一个 WITH RESULTS SETS
子句.或者更新存储过程以返回表变量.
I wrote a stored procedure that reads records into a temp table and then creates a pivoted output from the data in the temp table.
Running the stored procedure from SSMS works just fine. What I am facing problems is now that I am trying to create the SSIS package that will execute the sproc, and write its output to a tab delimited text file.
I am using Visual Studio 2015. My first problem is that when I try to configure the OLE DB Source within the Data Flow Task, when adding the SQL Command inside the SQL Command Text box: EXEC ShopperSkuHistory and click OK, I get this error:
I have been looking for information about this error, but I have not found anything that helps me understand why this is happening, and how I can fix it.
I hope through this post I can learn how to fix this error.
Thank you much in advance.
Here is my sproc:
UPDATED CODE
ALTER PROCEDURE [dbo].[ShopperSkuHistory]
AS
BEGIN
IF OBJECT_ID('tempdb..[#ShopperSku_History_Load]') IS NOT NULL
BEGIN
DROP TABLE [#ShopperSku_History_Load];
END;
-- Create main table
CREATE TABLE [#ShopperSku_History_Load]
(
[ID] INT IDENTITY(1, 1) NOT NULL
, [shopper_id] CHAR(32) NOT NULL
, [sku] VARCHAR(100) NOT NULL
, time_added DATETIME
)
SET NOCOUNT ON;
-- Populate the table
INSERT INTO [#ShopperSku_History_Load] ([shopper_id], [sku], [time_added])
SELECT DISTINCT [cr].[shopper_id], LEFT([cri].[sku], 9) [sku], GETDATE() [time_added]
FROM [dbo].[receipt_item] [cri]
INNER JOIN [dbo].[receipt] [cr]
ON [cri].[order_id] = [cr].[order_id]
WHERE[cri].[list_price] > 0
AND [cri].[IsInitialPurchase] = 1
AND LEFT([cri].[sku], 3) = 'MN0'
AND ([cr].[date_entered] > DATEADD(YEAR, -2, GETDATE()))
AND EXISTS (SELECT 1 FROM [product] [cp] WHERE [cp].[pf_id] = [cri].[sku] AND [cp].[for_sale] = 1)
AND NOT EXISTS (SELECT 1 FROM [dbo].[shopper] [cs] WHERE [cs].[IsTesting] = 1 AND [cs].[shopper_bounce] = [cr].[shopper_id])
ORDER BY [shopper_id];
CREATE TABLE [#HistoryOutput]
(
[shopper_id] VARCHAR(32)
, skus TEXT
)
INSERT INTO [#HistoryOutput]
( [shopper_id], [skus] )
SELECT
[shopper_id]
, STUFF(( SELECT ', ' + ISNULL([a].[sku], '')
FROM [#ShopperSku_History_Load] [a]
WHERE [a].[shopper_id] = [b].[shopper_id]
FOR
XML PATH('')
), 1, 1, '') [skus]
FROM [#ShopperSku_History_Load] [b]
GROUP BY [shopper_id];
SELECT
[shopper_id]
, [skus]
FROM
[#HistoryOutput];
END;
UPDATED ERROR
Exception from HRESULT: 0xC0202009
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Incorrect syntax near 'shopper_id'.".
This error
The metadata could not be determined because statement 'EXEC XXXXXX' in procedure 'XXXXXX' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.
happens because the SP contains either a
- Temp table
- Dynamic SQL
Because of these dynamic elements, SSIS/SSDT has trouble getting the column metadata correctly. More info here. We need to help SSIS get that column metadata. Depending on your SQL Server version, there are two solutions.
For SSIS/SQL 2008, you can try setting FMTONLY OFF
For SSIS/SQL 2012, you can wrap the stored procedure with a RESULT SET
from SSIS. Try this ...
Other options include updating the Stored Procedure itself and adding a WITH RESULTS SETS
clause. Or updating the stored procedure to return a table variable.
这篇关于如何从 SSIS 执行存储过程以将其输出到文本文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!