如何从 SSIS 执行存储过程以将其输出到文本文件 [英] How to EXEC a stored procedure from SSIS to get its output to text file

查看:73
本文介绍了如何从 SSIS 执行存储过程以将其输出到文本文件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我编写了一个存储过程,将记录读入临时表,然后根据临时表中的数据创建一个透视输出.

从 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 包含一个

  1. 临时表
  2. 动态 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

  1. Temp table
  2. 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆