Using package variable in OLE DB Source

Question

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:

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.

How to resolve? Am I missing something?

Community · Accepted Answer · 2020-07-29 18:41:17Z

2

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

edited Jul 29, 2020 at 18:41

CommunityBot

11 silver badge

answered Dec 9, 2014 at 20:09

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

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

Collectives™ on Stack Overflow

Using package variable in OLE DB Source

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related