I'm having problems when executing a stored procedure in an OLE DB source in SSIS,
I have set the OLE DB source to SQL command and put the following:
This is the code for the SQL command text:
exec timesheet_r12..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(100),
hours_worked NVarchar(100),
rate_description NVarchar(100),
rate_per_hour NVarchar(10),
job NVarchar(100),
work_stage NVarchar(100),
project_name NVarchar(100),
location NVarchar(100)
)
)
The first 2 parameters that I need to send are @startweek and @endweek for these,I need to find out how to send the last friday as "@endweek" and the friday of 4 weeks ago as "@startweek" (4 fridays before).
if I want to execute with hard coded @startweek and @endweek the Stored Procedure in the Database, I can do it with the following command:
exec timesheet_r12..TIME_lynx_extract
'19 Jul 2018',
'16 Aug 2019',
null,null,null,null,null,null,null,null,null,null,null
this is the error I'm getting when executing the package in SSIS:
SSIS package "C:\Users\AGUIRRG2\source\repos\Integration Services Project3\Integration Services Project3\Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.
Error: 0xC0207014 at Data Flow Task, OLE DB Source [49]: The SQL command requires a parameter named "@startweek", which is not found in the parameter mapping.
Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: OLE DB Source failed the pre-execute phase and returned error code 0xC0207014.
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "Excel Destination" wrote 0 rows.
Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "C:\Users\AGUIRRG2\source\repos\Integration Services Project3\Integration Services Project3\Package.dtsx" finished: Failure.
The program '[24756] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).
So right now what I would need is to know how to pass the parameters to SSIS, and how to make them dynamic, @startweek and @endweek can be calculated.
Thank you.
