SSIS - SQL Command - Parameters

Question

I edited the question based on the solution that Hadi gave.

I am using SSIS in VS 2013. I have a user variable called MyVariableList and Query.

This is Expression in user variable Query: "SELECT cola, colB FROM myTable WHERE myID IN (" + @[User::MyVariableList] + ")"

I have a Script Task that set the value of @[User::MyVariableList].

Dts.Variables["User::MyVariableList"].Value = sList;

After that, I have A Data Flow Task with OLE DB Source (from 1 database) to another OLE DB Destination (another database on another server). In the OLE DB Source Editor, I set Data access mode: SQL Command from variable Variable name: User:: Query

In the OLE DB Source connection, I have set the DelayValidation to True

Before I even can run the package, I am getting this error How can I fix this issue ? Thank you

If you want to use a comma seperated string like that, you need to use dynamic SQL, which is achieved using expressions. You can't use parameter to do that. See these references: jamessummerlin.com/2015/05/04/… stackoverflow.com/questions/17071451/… — Nick.Mc
– Nick.Mc, Commented May 1, 2017 at 22:46
MyProjectVariable is of type String, if you use 2 parameters of type int and pass these parameters then it will work. — observer
– observer, Commented May 2, 2017 at 6:04
Man!! you changed the whole question after getting an answer and accepting it?! it was better to ask a new one, this is not the way that stack overflow works!!! @faujong — user7119679
– user7119679, Commented May 2, 2017 at 20:48
@lahmbajin Also the OP removed the original question so now the question is unclear. I reversed my voting to down vote due to this. this is not the way that stack overflow works~ — Yahfoufi
– Yahfoufi, Commented May 3, 2017 at 9:58

Hadi · Accepted Answer · 2017-05-15 17:11:49Z

0

First of all, you are working with a project parameter not a project variable

You cannot achieve this using a parameterized sql command, you have to build the whole query inside a variable, and use it as source (SQL Command from variable)

Parameters are used to specify a single value, not concatenating the query string

Create a SSIS variable (User::Query), change the variable Evaluate As Expression property to True and write the expression in the variable Expression property. Like the following

"SELECT cola, colB FROM myTable WHERE myID IN (" + @[$Project::MyProjectVariable]  +   ")"

Note: to use a project parameter inside an expression use the following syntax : @[$Project::MyProjectVariable]

edited May 15, 2017 at 17:11

answered May 2, 2017 at 2:07

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

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

17 Comments

Hadi Over a year ago

try setting a default value to @[User::MyUserVariable] in the variables window (ex: '1') and set the dataflow task delay validation property to True also

faujong Over a year ago

Sorry for the delay. That works !! Thank you so much for your help !

user7119679 Over a year ago

@faujong if tgis answer solved ur issue you have to accept it and also if you found it helpful upvote it. Not only say thanks

faujong Over a year ago

Thank you, Hadi. Since your solution here works for me, I didn't end up using @TheEsisia solution on stackoverflow.com/questions/43746258/….

faujong Over a year ago

@Hadi, I had accepted and upvoted the answer that you gave on the other link. Thank you so much for your help !

|

Collectives™ on Stack Overflow

SSIS - SQL Command - Parameters

1 Answer 1

17 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related