2

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:

enter image description here

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.

4
  • 1
    Create 2 SSIS variables of date data type and set them using an expression, then supply these to the EXEC script. Since SSIS expressions aren't the best, I'd recommend creating a calendar table and a scalar funcion on your SQL Server and using it to find the last friday (the last friday 4 weeks ago is just substracting 4 weeks). Commented Aug 20, 2019 at 15:06
  • Also if you have to supply dates as varchar, make sure to use YYYY-MM-DD format and avoid language-specific names at all costs. Commented Aug 20, 2019 at 15:10
  • I have just tried to do it: i.imgur.com/uJX7ovB.png I get the same error Commented Aug 20, 2019 at 15:11
  • it doesn't work by passing the parameter as my stored procedure would take it in sql server? Also, I don't see the varchar as data type in SSIS Commented Aug 20, 2019 at 15:12

1 Answer 1

1

I had the same issue once working on a project.I think you need to do a specific mapping for your parameters

You can follow all steps mentioned in this article : Mapping Stored Procedure Parameters in SSIS OLE DB Source Editor

Sign up to request clarification or add additional context in comments.

1 Comment

I was able to make it work, yes, it was related to mapping

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.