SSIS - Convert a variable value to expression

-
Problem:

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.

Solution:

1st approach - Removing escape character using SSIS expression

(This approach will only remove escape character)
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:
enter image description here

2nd approach - 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
enter image description here
I added a Script task and selected Var1 as Readonly variable and VarResult as ReadWriteVariable:
enter image description here
Inside the script i used the following code to assign the value of Var1 as expression into VarResultafter 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
enter image description here

Comments

Post a Comment

Popular posts from this blog

SSIS - Script Task error: Exception has been thrown by the target of an invocation.

-
Question: When i execute the SSIS package the Script Task throws the following exception: Exception has been thrown by the target of an invocation. at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() Which is meaningless. How can i read the real error message? Answer: Exception has been thrown by the target of an invocation. Is an general exception that is thrown by Script T...
Read more

Don’t install Hadoop on Windows!

-
Image
A few years ago, I was hearing from my colleagues “don’t ever think about installing Hadoop on Windows operating system!” . I was not convinced of this saying because I am a big fan of Microsoft products, especially Windows. In the past three years, I worked on three projects where I was asked to build a Hadoop cluster on Ubuntu. The first time, it was a single-node Hadoop installation with a single Apache Spark worker. The other projects were about building a Big Data ecosystem for radiation data engineering , where the multi-node Hadoop cluster is deployed. Besides Hadoop, we installed and configured Apache Kafka, Spark, Hive, Pig, and Flume installations (I have published some installation guides previously, you can check the links at the end of this article). It was hard to become familiar with those technologies for the first time since they don’t have much documentation online. Each time I had to install Hadoop, I was thinking that why always Hadoop is installed on Linu...
Read more

SSIS - script component DateTime Formats Implicit Conversion

-
General Info These are the default formats of datetimes datatypes (when converting from string) DT_DBDATE yyyy-mm-dd DT_FILETIME yyyy-mm-dd hh:mm:ss:fff DT_DBTIME hh:mm:ss DT_DBTIME2 hh:mm:ss[.fffffff] DT_DBTIMESTAMP yyyy-mm-dd hh:mm:ss[.fff] DT_DBTIMESTAMP2 yyyy-mm-dd hh:mm:ss[.fffffff] DT_DBTIMESTAMPOFFSET yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm] Note: DT_DATE and DT_DBTIMESTAMP has the same SET method And i think that converting string to date is depending also on your current culture info More detailed information are found here https://msdn.microsoft.com/en-us/library/ms141036.aspx http://www.bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations Experiments : I didn't find any related article on this issue so i made the following experiments: SSIS Implicit datetime Conversion i created a SSIS package with a Dataflowtask. in this dataflowtask i created a Script Component (as a Source) and a Flat File Destin...
Read more