GianniAb


Hi, i'm new in this forum and in SSIS, so I apologize if mine is a stupid question and also for my english.

I have this problem: i have to develop an ETL package and i have to set the transformation between input and output columns using a configuration file, i.e. my client want to change the transformation simply modifying a config file( for example from UPPER to LOWER ).

I tried using dtsconfig and setting EvaluateAsExpression with value 1( true ) and Expression UPPER[Column 2] but i get an error saying that Column 2 is not a variable.

There is a way to do it

I don't know if i explained the problem well.

Thanks in advance



Re: Setting expressions in dtsConfig

DarrenSQLIS


Try this -

Use the config file to set a variable. Don't set EvaluateAsExpression, this just wants to be a literal value that you set. Then set the expression at the Data Flow Task level to be @myvariable. So when evaluated the derived column expression is the literal value of the variable, which should be a valid expression syntax, as entered into the dtsConfig.







Re: Setting expressions in dtsConfig

GianniAb

sorry, it doesn't work; maybe i'm doing something wrong, but doing so it doesn't evaluate the expression but use it as a literal, i.e. setting UPPER( "pippo") as variable value it puts UPPER( "pippo") into the results file.

I tried this:
Code Snippet


<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="GPM\terzinho" GeneratedFromPackageName="Test" GeneratedFromPackageID="{60B8F833-52BD-42D6-8BA9-71ED125F5B70}" GeneratedDate="12/06/2007 12.40.57"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package\Data Flow Task.Properties[[Derived Column].[Derived Column Output].[NewCol].[FriendlyExpression]]" ValueType="String">
<ConfiguredValue>UPPER("pluto")</ConfiguredValue>
</Configuration>
</DTSConfiguration>


doing so it put the right value PLUTO into the output file, but when i use UPPER( [Column 2] ) it doesn't work at all





Re: Setting expressions in dtsConfig

Phil Brammer

Gianni,

You aren't understanding what Darren wrote.

In the config file, set a value of a variable. JUST A VARIABLE. Then, back in the package, right click on the data flow, select properties, select expressions and find the derived column expression you are trying to set. In that box, type in the name of the variable you are setting with the config file.






Re: Setting expressions in dtsConfig

GianniAb

Phil, i'm sorry but i think i wasn't able to explain myself clearly, i already did what you say, i created a variable, i put it in the expression of derived column and i set his value into the dtsconfig.

Doing so it works well if i set the variable value to "pippo", but if i set this value to UPPER( "pippo" ) it wrotes literally UPPER( "pippo" ) into the results file.
Then i tried not to use the value of the variable but the expression property, setting it to UPPER( "pippo" ) but it doesn't work at all until i set the EvaluateAsExpression property to 1( true ); doing so it wrotes PIPPO into the output files... then i set the expression property to UPPER( [Column 2] ) where Column 2 is the second column of the input file, but it doesn't work at all.





Re: Setting expressions in dtsConfig

Phil Brammer

Let's try this...

Setup three variables: controlCASE, inputString, outputString. Set both to be string variables.

In the config file, you'll specify controlCASE and inputString. (I realize that inputString is likely coming from a file inside your data flow, but this should better illustrate what I'm proposing.)

In the expression for outputString (set EvaluateAsExpression to true), use this:
Code Snippet

@[User::controlCASE] == upper("UPPER") UPPER(@[User::inputString]) : LOWER(@[User::inputString]


You'll want to then specify either UPPER or some other value (LOWER) in the config file to determine which function to use in the expression.

Now you can use the above variable (@[User:SurpriseutputString]) wherever you need it. OR, you can use the above expression in a derived column, replacing "@[User::inputString]" with the input column name.





Re: Setting expressions in dtsConfig

MatthewRoche

I think the part you're missing is what to put in the expression for the derived column. Use the SSIS conditional operator in your derived column, using the variable you're setting through the configuration as the "base" of the condition:

http://technet.microsoft.com/en-us/library/ms141680(SQL.90).aspx