Thursday, April 27, 2006 4:08 AM
I have a derived column shape in which I replace a column with an expression.
The expression is an IF statement - a true result sets a value for the column and a false result just uses the existing value of the column (ie it replaces it with itself)
ColumnName DerivedColumn Expression
ColumnA Replace 'ColumnA' ColumnB != ColumnC ? "y" : ColumnA
But whenever, the condition is NOT met, ColumnA is set to NULL!!!!
Does this mean that the column value is deleted before the Expression is applied?
If this is how it is meant to work, then does anyone have a way of doing what I want to do without creating extra columns in the dataset??
Thursday, April 27, 2006 6:14 AMModerator
I'm wondering if there are some data typing issues here. Wha is the type of ColumnA?
Thursday, April 27, 2006 6:34 AM
It's just a unicode string.
Thursday, April 27, 2006 6:49 AMModerator
I mean in the pipeline. Is it a DT_WSTR?
if so, ensure you don't have any implicit conversions going on. i.e. explicitly cast "Y" as a DT_WSTR.
Also, first try to get this working as a new column rather than replacing ColumnA. See if you exhibit the same problems in that scenario.
Thursday, April 27, 2006 7:11 AM
in my example setting Column A to the value "y" works fine. It's setting Column A to itself (ie II just want it to retain it's original value) which is the problem.
Do you suggest I explicitly cast the ColumnA in the expression? So with my example....
ColumnName DerivedColumn Expression
ColumnA Replace 'ColumnA' ColumnB != ColumnC ? "y" : CAST(ColumnA...)
Thursday, April 27, 2006 7:34 AMModerator
No, that's not quite what i meant.
Replace 'ColumnA' ColumnB != ColumnC ? (DT_WSTR)"y" : ColumnA
My second suggestion was to see if this worked first:
Add as new column ColumnB != ColumnC ? "y" : ColumnA
I'm clutching at straws a little bit but if I were you I would definately try to recreate the problem by adding it as a new column rather than replacing ColumnA.
Thursday, April 27, 2006 7:50 AM
adding a column works fine. It's replacing an exising column that has the "problem".
I should point out as well that the issue only arises when you use an IF condition in the expression.
So this is OK
Replace ColumnA ColumnA+"Hello"
This will replace ColumnA with what was in ColumnA + "Hello"
This is NOT OK
Replace ColumnA ColumnB != ColumnC ? "y" : ColumnA
So if ColumnB = ColumnC, then ColumnA is set to NULL - NOT what ColumnA was before the expression was applied.
I reckon its a bug.
Thursday, April 27, 2006 7:56 AMModerator
Can you post a simple repro that doesn't reply on external data sources (i.e. just create the same data using a script source component) and then post it up here?
To post up here, just copy the contents of your .dtsx file into your reply.
Thursday, April 27, 2006 7:58 AM
I'll do it tomorrow.
Thursday, April 27, 2006 8:28 AMModeratorHave you checked if Column B or C is NULL? If so you will always get NULL out. Example here http://wiki.sqlis.com/default.aspx/SQLISWiki/Expressions.html
Thursday, April 27, 2006 8:43 PM
You can quickly test for what Darren is saying by changing your expression like this:
false ? "y" : ColumnA
If ColumnB or ColumnC are NULL, and you want to fail the comparison in that case,you can make your expression like this:
!ISNULL(ColumnB) && !ISNULL(ColumnC) && ColumnB != ColumnC ? "y" : ColumnA
If you want the condition to do NULL comparisions (that is, you want ColumnB != ColumnC to return true if one is NULL and the other is not, you might need something more like:
(ISNULL(ColumnB) != ISNULL(ColumnC)) || !ISNULL(ColumnB) && !ISNULL(ColumnC) && ColumnB != ColumnC ? "y" : ColumnA
Thursday, April 27, 2006 8:50 PM
Your right Darren - it was the NULL values. aaargh!!!
Thanks for everyone's input.