Execute Stored Procedure with parameters in SSIS as OLE DB Source

I want to execute an Stored Procedure that requires parameters as an OLE DB source to later export it a Excel file from SSIS.

The SP generates a set of data that I would like to export to the Excel file

This is the code that I run for the SP. (running it like this produces the result I want)

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

I'm not sure if the formatting for the run is the proper one though.

This is a picture of the setup:

these are the errors of the yellow background section:

Exception from HRESULT: 0xC020204A

Error at Data Flow Task [OLE DB Source [37]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "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.".

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.

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.

It's not too difficult to use parameters with your EXEC in the DFT.

First, you'll want to make sure you have SSIS variables for all of your input parameter values.

Specifically, make all of these [User::{var_name}] and populate them (populating the values is out of the scope of this answer):

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)

Also, set your @endweek and @startweek in an Execute SQL Task before your DFT.

Now that you have all of your SSIS variable prepared, go back into your OLE DB Source task, and edit your query. You'll want to remove the return variable, and change all of your input parameters, like this:

EXECUTE [dbo].[TIME_lynx_extract] ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
WITH RESULT SETS(
(
worker_reference NVARCHAR(50),
placement_reference NVARCHAR(10),
worker_name NVARCHAR(50),
job_title NVARCHAR(100),
authorising_line_manager NVARCHAR(100),
timesheet_date DATETIME,
company_agent_name NVARCHAR(100),
type_of_worker NVARCHAR(100),
week_number NVARCHAR(10),
hours_worked NVARCHAR(10),
rate_description NVARCHAR(100),
rate_per_hour NVARCHAR(10),
job NVARCHAR(50),
work_stage NVARCHAR(100),
project_name NVARCHAR(100),
location NVARCHAR(100)
))

Once you do that, hit the Parameters button next to the query window. SSIS should prepopulate the list with Parameter0 through Parameter12. What you want to do is go through and change all those ParameterX names to your input parameter names. Then choose the corresponding SSIS variable to use for each parameter.

Here's what my Set Query Parameters window looks like when I do this for my test proc: