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

  • itortu

    SSCommitted

    Points: 1912

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

  • Kenny Jozi

    SSCrazy

    Points: 2004

    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

  • Phil Parkin

    SSC Guru

    Points: 244562

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Phil Parkin

    SSC Guru

    Points: 244562

    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 data

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • itortu

    SSCommitted

    Points: 1912

    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?

  • itortu

    SSCommitted

    Points: 1912

    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.

  • itortu

    SSCommitted

    Points: 1912

    Phil Parkin - Friday, July 7, 2017 10:28 AM

    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.

    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

    SSCommitted

    Points: 1912

    itortu - Friday, July 7, 2017 11:22 AM

    Phil Parkin - Friday, July 7, 2017 10:28 AM

    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.

    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.

    Is it?:

    EXEC dbo.ShopperSkuHistory
    WITH RESULT SETS
      (
        shopper_id char(32) NOT NULL,
        skus            varchar(max) NULL
      );

  • Phil Parkin

    SSC Guru

    Points: 244562

    itortu - Friday, July 7, 2017 11:25 AM

    Is it?:

    EXEC dbo.ShopperSkuHistory
    WITH RESULT SETS
      (
        shopper_id char(32) NOT NULL,
        skus            varchar(max) NULL
      );

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • itortu

    SSCommitted

    Points: 1912

    Phil Parkin - Friday, July 7, 2017 12:47 PM

    itortu - Friday, July 7, 2017 11:25 AM

    Is it?:

    EXEC dbo.ShopperSkuHistory
    WITH RESULT SETS
      (
        shopper_id char(32) NOT NULL,
        skus            varchar(max) NULL
      );

    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.

    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];

  • Phil Parkin

    SSC Guru

    Points: 244562

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • itortu

    SSCommitted

    Points: 1912

    Phil Parkin - Friday, July 7, 2017 1:48 PM

    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.

    Thank you! That worked perfectly!

  • This was removed by the editor as SPAM

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply