title: Execute Stored Procedure with parameters in SSIS as OLE DB Source
body: <p>I want to execute an Stored Procedure that requires parameters as an OLE DB source to later export it a Excel file from SSIS.</p>
  <p>The SP generates a set of data that I would like to export to the Excel file</p>
  <p>This is the code that I run for the SP. (running it like this produces the result I want)</p>
  <pre><code>DECLARE @RC int
  DECLARE @startweek varchar(20)
  DECLARE @endweek varchar(20)
  DECLARE @payroll varchar(30)
  DECLARE @job varchar(25)
  DECLARE @job_to varchar(25)
  DECLARE @manager varchar(30)
  DECLARE @office varchar(100)
  DECLARE @pu varchar(6)
  DECLARE @pu_to varchar(6)
  DECLARE @task varchar(25)
  DECLARE @task_to varchar(25)
  DECLARE @Prj_pu varchar(6)
  DECLARE @Prj_pu_to varchar(6)
  
  SET @endweek = dateadd(d, -((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate());
  SET @startweek = DATEADD(WEEK, -25, @endweek)
  
  EXECUTE @RC = [dbo].[TIME_lynx_extract] 
     @startweek
    ,@endweek 
    ,@payroll
    ,@job
    ,@job_to
    ,@manager
    ,@office
    ,@pu
    ,@pu_to
    ,@task
    ,@task_to
    ,@Prj_pu
    ,@Prj_pu_to
  GO
  </code></pre>
  <p>I'm not sure if the formatting for the run is the proper one though.</p>
  <p>This is a picture of the setup:</p>
  <p><a href="https://i.stack.imgur.com/hb4sm.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hb4sm.png" alt="enter image description here" /></a></p>
  <p>these are the errors of the yellow background section:</p>
  <blockquote>
  <p>Exception from HRESULT: 0xC020204A</p>
  <p>Error at Data Flow Task [OLE DB Source [37]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.</p>
  <p>An OLE DB record is available.  Source: &quot;Microsoft SQL Server Native Client 11.0&quot;  Hresult: 0x80004005  Description: &quot;The metadata could not be determined because statement 'EXECUTE SP_EXECUTESQL @STR_SQL' in procedure 'TIME_lynx_extract'  contains dynamic SQL.  Consider using the WITH RESULT SETS clause to explicitly describe the result set.&quot;.</p>
  <p>Error at Data Flow Task [OLE DB Source [37]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.</p>
  </blockquote>
  <p>Also, if I try executing basic queries on the OLE DB source there it works, so the connections with database seems to be ok.
  The main problem now is to get to execute this SP.</p>