Hello, 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.
Here is my code:
ALTER PROCEDURE [dbo].[ShopperSkuHistory]
IF OBJECT_ID('tempdb..[#ShopperSku_History_Load]') IS NOT NULL
DROP TABLE [#ShopperSku_History_Load];
-- 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
CONSTRAINT [PK_ShopperSku_History_Load] PRIMARY KEY CLUSTERED
([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 93) ON [PRIMARY]
) ON [PRIMARY]
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] WITH (NOLOCK)
INNER JOIN [dbo].[receipt] [cr] WITH (NOLOCK)
ON [cri].[order_id] = [cr].[order_id]
WHERE[cri].[list_price] > 0
AND ([cr].[date_entered] > DATEADD(YEAR, -2, GETDATE()))
AND EXISTS (SELECT 1 FROM [product] [cp] WITH ( NOLOCK ) WHERE [cp].[pf_id] = [cri].[sku] AND [cp].[for_sale] = 1)
AND NOT EXISTS (SELECT 1 FROM [dbo].[shopper] [cs] WITH (NOLOCK) WHERE [cs].[IsTesting] = 1 AND [cs].[shopper_bounce] = [cr].[shopper_id])
ORDER BY [shopper_id];
-- Read from temp table to format skus
, STUFF(( SELECT ', ' + [a].[sku]
FROM [#ShopperSku_History_Load] [a]
WHERE [a].[shopper_id] = .[shopper_id]
), 1, 1, '') [skus]
GROUP BY [shopper_id];
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 when I try to configure the OLE DB Source within the Data Flow Task, when I add the SQL Command inside the SQL Command Text box: EXEC ShopperSkuHistory and click OK, then I get:
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.