How to EXEC a stored procedure from SSIS to get its output to text file.
-
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]
AS
BEGINIF 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
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
SELECT
[shopper_id]
, STUFF(( SELECT ', ' + [a].[sku]
FROM [#ShopperSku_History_Load] [a]
WHERE [a].[shopper_id] = .[shopper_id]
FOR
XML PATH('')
), 1, 1, '') [skus]
FROM [#ShopperSku_History_Load]
GROUP BY [shopper_id];END;
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.
Ale.
-
Use Execute SQL Task instead of OLE DB SOURCE , make sure you have a variable for every column so that you can map your Exec SQL task result set to those variable ,
also use Execute SQL Task to insert your data to the destination .
SQL 2000/2005/2008/2012 DBA - MCTS/MCITP
-
You are on the right path.
Instead of
EXEC proc
You need
EXEC proc with result sets
as suggested by the error message. You need to describe the expected result set's DDL, so that SSIS knows how to deal with the output.See part 3 of this link for an example.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Also, I hope that you and your users know and accept that (NOLOCK) means that you may experience the following symptoms:
1) Missing data
2) Reading the same data more than once
3) Reading uncommitted dataThe absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hello, thank you for your suggestion. I came to find this information on how to export the results of a stored procedure to an XML file: http://www.sql-server-performance.com/2013/export-to-xml-using-ssis/
I am following it but I need to make some adjustments in the script. The example writes to an XML file, and what I need is to write to a flat tab delimited file.
Do you think you can offer some insight on how to modify the script to be able to do this? -
Regarding this post: "You need to describe the expected result set's DDL, so that SSIS knows how to deal with the output."
So in my case how that would look like? Do I need to modify my stored procedure and add 2 output parameters?
Many thanks.
- Phil Parkin - Friday, July 7, 2017 10:28 AM
Regarding this post: "You need to describe the expected result set's DDL, so that SSIS knows how to deal with the output."
How that syntax would look like in my case? Do I need to modify my stored procedure? I am not sure as to what to put inside the with results set area.Many thanks.
- itortu - Friday, July 7, 2017 11:22 AM
Is it?:
EXEC dbo.ShopperSkuHistory
WITH RESULT SETS
(
shopper_id char(32) NOT NULL,
skus varchar(max) NULL
); - itortu - Friday, July 7, 2017 11:25 AM
When your proc runs, it produces a result set. If you were defining a table to contain this result set, what would it look like? That is what you are aiming for, however, you need only include Column Name and Data Type in your definition. No constraints.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Friday, July 7, 2017 12:47 PM
I changed the stored procedure at the bottom, please tell me if it looks correct.
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 VARCHAR(8000)
)INSERT INTO [#HistoryOutput]
( [shopper_id], [skus] )
SELECT
[shopper_id]
, STUFF(( SELECT ', ' + ISNULL([a].[sku], '')
FROM [#ShopperSku_History_Load] [a]
WHERE [a].[shopper_id] = .[shopper_id]
FOR
XML PATH('')
), 1, 1, '') [skus]
FROM [#ShopperSku_History_Load]
GROUP BY [shopper_id];SELECT
[shopper_id]
, [skus]
FROM
[#HistoryOutput]; -
Sorry, but you misunderstood my meaning. I was not suggesting changing the proc, but the EXECUTE WITH RESULT SETS part.
Change this
EXEC dbo.ShopperSkuHistory
WITH RESULT SETS
(
shopper_id char(32) NOT NULL,
skus varchar(max) NULL
);To this
EXEC dbo.ShopperSkuHistory
WITH RESULT SETS
((
shopper_id char(32),
skus varchar(max)
));
Note the additional brackets.The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Friday, July 7, 2017 1:48 PM
Thank you! That worked perfectly!
-
This was removed by the editor as SPAM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply