Essentially I a SSIS pkg with an Execute SQL statement that dynamically writes a REPLACE function based on some table values. (ie REPLACE(REPLACE(Col1,"*","",),"@@@","")
ExecuteSQL result is put to variable @Cleanse
In my Derived Column conversion Im trying to call @User::Cleanse as an expression in to replace the Value of the Col1 from the DataFlow.
The result appears to be pulling the result of @Cleanse and using it as a string value rather than applying it as the REPLACE function.
When Debugging the @Cleanse value appear to be putting \ in the string, which I dont think should matter as it seems to be appliying this to other values without a problem
The result is that when running out to the CSV this is putting
"REPLACE(REPLACE(Col1" in Col1
"*" in Col2
) in Col3 etc etc
How can I get the Derived column transformation to 'see' the variable as the function, not a string value?
Many thanks in advance
Set the EvaluateAsExpression property of the variable to True.
However, binding variables as parameters to an SQL query using the Variable Mappings pane of the Execute SQL task might be a better solution