SSIS variable expression from SQL Execution task incorrectly set

Question

I have a SSIS package where I am running a SQL execution task to get some records, Then I am running a foreach loop iterating over ADO object returned and setting few variables values.

These variables are used for data flow task queries. I need to set expression for the variable.

In rows I have value something similar to "foo" + @[User::Bar] + "baz" (I set it using this expression directly, it works. I want to set it from sql output)

But when I get this value from SQL task to Variable expression, SSIS is escaping the quotes, I do not want to escape these for my task to work.

Saved Value : "foo" + @[User::Bar] + "baz"

Variable set : \"foo\" + @[User::Bar] + \"baz\"

Can anyone help me with this. I want expression as I have saved, due to this forceful escaping, my query is not getting set properly.

Does the provided answer solve the problem?

Yahfoufi
– Yahfoufi

2019-07-22 08:14:32 +00:00
Commented Jul 22, 2019 at 8:14 — Yahfoufi
– Yahfoufi, Commented Jul 22, 2019 at 8:14
Haven't tried it yet, will update tomorrow

Chaitanya Gadkari
– Chaitanya Gadkari

2019-07-22 11:21:32 +00:00
Commented Jul 22, 2019 at 11:21 — Chaitanya Gadkari
– Chaitanya Gadkari, Commented Jul 22, 2019 at 11:21
Nope... It didn't

Chaitanya Gadkari
– Chaitanya Gadkari

2019-07-24 18:23:28 +00:00
Commented Jul 24, 2019 at 18:23 — Chaitanya Gadkari
– Chaitanya Gadkari, Commented Jul 24, 2019 at 18:23

Hadi · Accepted Answer · 2019-07-26 14:48:01Z

1

Removing escape character using SSIS expression

If the following value is stored within a variable (example @[User::Var1]:

\"foo\" + @[User::Bar] + \"baz\"

Add an Expression task with the following expression:

@[User::Var1] = REPLACE(@[User::Var1],"\\","")

Or create an new variable with the following expression:

REPLACE(@[User::Var1],"\\","")

Experiments

I created a variable @[User::Variable] of type string with the following value:

\"foo\" + @[User::Bar] + \"baz\"

Then i created another variable @[User::Variable1] of type string with the following expression:

REPLACE( @[User::Variable],"\\","")

The value escape characters are gone:

Update 1 - Convert Value to expression

Since you are looking to treat the following value \"foo\" + @[User::Bar] + \"baz\" as an expression and not a fixed value. You cannot achieve that using SSIS expression. You have to assign this value as expression to another variable as mentioned below:

I added 3 variable into my package: Bar, Var1, and VarResult

I added a Script task and selected Var1 as Readonly variable and VarResult as ReadWriteVariable:

Inside the script i used the following code to assign the value of Var1 as expression into VarResult after removing escape character:

public void Main()
{
    string Var1 = Dts.Variables["Var1"].Value.ToString();

    Dts.Variables["VarResult"].Expression = Var1.Replace("\\", "");
    Dts.Variables["VarResult"].EvaluateAsExpression = true;

    Dts.TaskResult = (int)ScriptResults.Success;
}

Result

edited Jul 26, 2019 at 14:48

answered Jul 18, 2019 at 20:07

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

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

3 Comments

Chaitanya Gadkari Over a year ago

It didn't solve my problem.... It did remove slashes from value for initially set values but it did not at run time... Also to note, I want to set expression using that, not value.... I mean if @[User:Bar] is coming in value it's of no use to me... I want Bar variables value to be used in expression to get the value

Hadi Over a year ago

@ChaitanyaGadkari If the string @[User::Bar] is stored into a variable value then it cannot be converted to this variable value since it is not used in expression. You need to achieve that using a Script Task. I will provide an answer in a while and let you know

Hadi Over a year ago

@ChaitanyaGadkari I updated my answer, check it out

Collectives™ on Stack Overflow

SSIS variable expression from SQL Execution task incorrectly set

1 Answer 1

Removing escape character using SSIS expression

Experiments

Update 1 - Convert Value to expression

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Removing escape character using SSIS expression

Experiments

Update 1 - Convert Value to expression

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related