我编写了一个存储过程,它将记录读入临时表,然后从临时表中的数据创建了一个透视输出。
从SSMS运行存储过程很好用。现在我面临的问题是,我正在尝试创建将执行sproc并将其输出写入制表符分隔文本文件的SSIS包。
我使用的是Visual Studio 2015。我的第一个问题是,当我尝试在数据流任务中配置OLE DB源时,在SQL命令文本框内添加SQL命令:EXEC ShopperSkuHistory并单击“确定”时,会出现以下错误:
从SSMS运行存储过程很好用。现在我面临的问题是,我正在尝试创建将执行sproc并将其输出写入制表符分隔文本文件的SSIS包。
我使用的是Visual Studio 2015。我的第一个问题是,当我尝试在数据流任务中配置OLE DB源时,在SQL命令文本框内添加SQL命令:EXEC ShopperSkuHistory并单击“确定”时,会出现以下错误:
我一直在寻找关于这个错误的信息,但是没有找到任何有助于我理解为什么会发生这种情况以及如何修复它的内容。
我希望通过这篇文章学习如何解决这个错误。
非常感谢您提前的帮助。
这是我的存储过程:
更新后的代码
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;
更新错误
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'.".