CODEWITHSUNDEEP
ssis
Khanh Hua
Khanh Hua

Reputation: 1

Using package variable in OLE DB Source

In the Control Flow, I created a Execute SQL Task in SSIS. In the General tab, under SQLStatement I am able to write my query. Something like this:

select config_value from config_table where name ='ArchiveDays'

In the Parameter Mapping tab, I am able to create variable: User::ConfigValue

In the Result Set tab, I have the following: https://dl.dropboxusercontent.com/u/12421846/ssis/result_Set.JPG

I am able to run the Execute Data Flow Task without issue.

How can I check the value in the variable after I run the dataflow?

In the Control Flow, Data Flow task, I have a OLE DB Source, I try to use the variable created. The query parses, but I get the following Error message.

https://dl.dropboxusercontent.com/u/12421846/ssis/error_msg2.JPG

How to resolve? Am I missing something?

Upvotes: 0

Views: 732

Answers (1)

billinkc
billinkc

Reputation: 61201

That's not how you map parameters in SSIS. Documentation is going to be helpful in your career and books online is the place to start.

Parameterization in an OLE DB Source is accomplished through the use of ? which is an ordinal, zero-based replacement scheme.

Your query would need to be rewritten to use the parameters. And it should also be re-written to not be lazy. Something like

WHERE CAST(CreatedOn as date) < dateadd(DAY, ?, CURRENT_TIMESTAMP)

And that is where you'd map in your @User::carepro_ConfigValue

Upvotes: 2

Related Questions