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: https://jamessummerlin.com/2015/05/04/ssis-using-expressions-to-build-sql-statements-for-ado-net-connections/ http://stackoverflow.com/questions/17071451/ssis-passing-sql-command-as-variable-to-ole-db-source — Nick.Mc, May 01 '17 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, May 02 '17 at 06: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 — , May 02 '17 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, May 03 '17 at 09:58

Hadi · Accepted Answer · 2017-05-15T17:11:49.890

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 '17 at 17:11

answered May 02 '17 at 02:07

Hadi

36,233
13
65
124

Thank you all. I did what Hadi suggested, but when I set MyProjectVariable in a Script Task earlier (instead of hard coding the value), before I run the package, I already get error in the OLE DB Source: "Statement(s) could not be prepared." and "Incorrect syntax near ')'.". In the OLE DB Source Editor, the Variable value is "SELECT cola, colB FROM myTable WHERE myID IN ()" -->this is because MyProjectVariable is set in a Script Task. In summary, i get a result from a query from 1 DB, from that result, I want to query another DB. Is this the best way to do it ? Thank you. – faujong May 02 '17 at 16:55
Set the oledb source `Delay Validation` property to `True` – Hadi May 02 '17 at 18:27
Thank you, Hadi. In the OLEDB Source Connection, I set "DelayValidation" to true, but still getting the error – faujong May 02 '17 at 18:33
i changed it to use User variable, instead of project parameter. This is the expression in the user variable Query: "SELECT ... FROM myTable WHERE Id IN (" + @[User::MyUserVariable] + ")" This works if I set the value by hard coding @[User::MyUserVariable] to '1','2', but gives me the error above when I set the value of @[User::MyUserVariable] in Script Task – faujong May 02 '17 at 19:12
I think this is another issue. Try asking it in a new question and provide the script task code + screenshot of the package. Or retract the answer accept and just upvote it instead of accepting. And edit your question – Hadi May 02 '17 at 19:57
@faujong you retracted the answer accept without editing your question??!! this will not get help – Hadi May 02 '17 at 20:32
ok, now i see it, i will try to help bro. if you find my answer helpful you can upvote it even if it not solved the issue. don't accept an answer if your issue is not solved only if you decided to open a new question :) – Hadi May 02 '17 at 20:40
3

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 – Hadi May 02 '17 at 20:42
1

Sorry for the delay. That works !! Thank you so much for your help ! – faujong May 03 '17 at 13:50
2

@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 – May 06 '17 at 12:00
@faujong why not accepting this answer if it solved ur issue? – Hadi May 12 '17 at 18:55
@Hadi: Sorry for the delay, but did I accept the answer correctly ? – faujong May 15 '17 at 16:04
Yes, read more at [tour page](http://www.stackoverflow.com/tour) to learn more on accepting and upvoting answers – Hadi May 15 '17 at 16:25
Also i am waiting for a reply to this answer http://stackoverflow.com/questions/43746258/query-a-database-based-on-result-of-query-from-another-database – Hadi May 15 '17 at 16:26
1

Thank you, Hadi. Since your solution here works for me, I didn't end up using @TheEsisia solution on http://stackoverflow.com/questions/43746258/query-a-database-based-on-result-of-query-from-another-database. – faujong May 15 '17 at 19:25
I provided three detailed methods to solve ur issue in the other question. Just give a try. They all are working 100% @faujong – Hadi May 15 '17 at 19:27
1

@Hadi, I had accepted and upvoted the answer that you gave on the other link. Thank you so much for your help ! – faujong May 17 '17 at 19:46

SSIS - SQL Command - Parameters

1 Answers1