title: How to EXEC a stored procedure from SSIS to get its output to text file
body: <p>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. </p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <p><a href="https://i.stack.imgur.com/vZaRx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vZaRx.png" alt="enter image description here"></a></p>
  
  <p>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. </p>
  
  <p>I hope through this post I can learn how to fix this error.</p>
  
  <p>Thank you much in advance. </p>
  
  <p>Here is my sproc:</p>
  
  <p><strong>UPDATED CODE</strong></p>
  
  <pre><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] &gt; 0
                  AND [cri].[IsInitialPurchase] = 1
                  AND LEFT([cri].[sku], 3) = 'MN0'
                  AND ([cr].[date_entered] &gt; 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;
  </code></pre>
  
  <p><strong>UPDATED ERROR</strong></p>
  
  <pre><code>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'.".
  </code></pre>