SSIS: How do I pass string parameter in an update statement in Execute SQL Task?

Question

I have one execute SQL Task in SSIS 2012 which has update statement. I want to pass a string variable in the where clause of this update statement. The update section is as below:

where coalesce(s1.iteration, '') not like '%?%' and s2.Iteration = '?'

Here, ? needs to be replaced with a string variable, which in this case would be 08152017. I have added the variable to the Parameter Mapping. Screenshot is attached.

The task executes successfully but does not updates the value in the intended column. It seems the query is not passing the value.What am I doing wrong? How do I check that the SQL inside the Execute SQL Task is actually getting the value from the variable?

There are a zillion guides to this online.. .like this: https://stackoverflow.com/questions/7610491/how-to-pass-variable-as-a-parameter-in-execute-sql-task-ssis , https://dataqueen.unlimitedviz.com/2012/08/how-to-set-and-use-variables-in-ssis-execute-sql-task/ https://stackoverflow.com/questions/7610491/how-to-pass-variable-as-a-parameter-in-execute-sql-task-ssis — Nick.Mc, Sep 13 '17 at 23:07

SqlKindaGuy · Answer 1 · 2017-09-14T08:16:32.737

First of all, when you set your variable in parameter mapping, make sure the datatype is NVARCHAR and not LONG.

Second you need to write your statement like this:

where coalesce(s1.iteration, '') not like '%?%' and s2.Iteration = ?

You dont need '' because your variable is already set as a string. Try to hardcode your value in your variable to see if it passes. Otherwise, set a breakpoint on pre-execute to see wheter your variable has a value.

If your variable has a value and your SQL is not working, maybe you should look into your SQL. Maybe try it directly in SSMS to see if it actually runs or does anything.

score 1 · Answer 2 · edited Jan 29 '19 at 19:05

1

In scenario ? is Integer type variable, then please use below format:

SELECT ? +' Hello World!'

The above does not require the use of an additional string variable.

edited Jan 29 '19 at 19:05

סטנלי גרונן

2,917
23
46
68

answered Jan 29 '19 at 16:23

P.S

11
1

score 0 · Answer 3 · answered Sep 13 '17 at 23:10

Create a user variable with % on it, for example, variable name is Like_Var with data type String

"%" + @Orig_Var + "%"

Let's say, Orig_Var has a value of 08152017, therefore Like_Var will have %08152017%

Then use Like_Var on your parameter in Execute SQL Task as parameter 0, data type VARCHAR in Parameter Mapping

WHERE COALESCE(s1.iteration, '') NOT LIKE ? 
  AND s2.Iteration = ?

SSIS: How do I pass string parameter in an update statement in Execute SQL Task?

3 Answers3