CODEWITHSUNDEEP
sqlparameter-passingssis-2012
Harshit Shah
Harshit Shah

Reputation: 1

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

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?

Upvotes: 0

Views: 3309

Answers (3)

P.S
P.S

Reputation: 11

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.

Upvotes: 1

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

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.

Map your Variable

Set Breakpoint

Watch your variable has a value

Upvotes: 1

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

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 = ?

Upvotes: 0

Related Questions