How to EXEC a stored procedure from SSIS to get its output to text file

Question

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'.".

Sorry, I can not see the image. Can you paste the error in the text? — Chuck, Jul 07 '17 at 16:46

Troy Witthoeft · Accepted Answer · 2017-07-10T17:42:19.620

7

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.

edited Jul 10 '17 at 17:42

answered Jul 07 '17 at 18:01

Troy Witthoeft

2,498
2
28
37

Hi Troy, I am using VS/SSIS 2015, and SQL 2012. I am a bit unsure how to update the stored procedure. to add WITH RESULTS SETS. – erasmo carlos Jul 07 '17 at 19:03
I tried your recommendation, unsure if I am doing it right. Still get an error although a different one. I have updated my code and the error text. – erasmo carlos Jul 07 '17 at 20:12
Your new error is a Syntax error. The SP is now broken. Try it in SSMS and see if it works. Please revert it back and look at the update to my answer. Add the `RESULT SET` clause in SSIS, thanks. – Troy Witthoeft Jul 07 '17 at 20:35
1

`[shopper_id] CHAR(32)`..Is this valid ? Replace CHAR with VARCHAR and run your sp. – Prabhat G Jul 08 '17 at 08:25
@TroyWitthoeft: wrapping the stored procedure with a RESULT SET from SSIS did fix my issue, and now I am able to run the package with the expected results. Thank you so much for your help. – erasmo carlos Jul 10 '17 at 16:46
Great to hear. Glad you have it sorted out! Good luck with your project. – Troy Witthoeft Jul 10 '17 at 17:30
@TroyWitthoeft Hi Troy, I have a SP taking 2 input and 1 output parameters. It returns a result set. When i did the above changes as mentioned in your answer, it is now giving me a different error : "No value given for one or more required parameters". The SP call is as `EXEC [uspName] @startDate, @EndDate, Average OUTPUT` in SSMS. In the result set, it gives 3 columns `[WeekStartDate] DATETIME, [Average] DECIMAL(5,2), [pln] VARCAHR(5)` Do you have any inputs about what may be causing this? – Arpit Chinmay May 20 '21 at 12:26
@ArpitChinmay It sounds like your issue revolves around the use of the `output parameters`? That is different from OPs question. Please open a new question for that. Provide all of the details. As a guess, you probably need to use the `Execute SQL` component and use parameter mapping and variables to capture that output. Have a look here = https://www.sqlshack.com/execute-sql-tasks-in-ssis-output-parameters-vs-result-sets/ – Troy Witthoeft May 21 '21 at 18:48
@TroyWitthoeft I already used the Execute SQL Task and it ran fine. I was unable to find any article that would guide me to store the values from the result set parameter to Excel file. I have now made a For each container with a data flow taskin it. It executes the SP in OLE DB source and tries to store result in excel destination. I would like to point that, the problem was not in OLE DB source, it was in the Excel destination. My packages stores the value in CSV file correctly. – Arpit Chinmay May 21 '21 at 19:20

How to EXEC a stored procedure from SSIS to get its output to text file

1 Answers1

Linked