Derived Column Transformation not filtering as expected in SSIS 2008 Data Flow

Question

Post reply

Derived Column Transformation not filtering as expected in SSIS 2008 Data Flow

roryoflynn1

Ten Centuries

Points: 1281
More actions
April 1, 2014 at 9:10 am

#305897

Hi All,
I have the following Expression working on a Derived Column in an SSIS Data Flow Transformation
[Date_Start] == " 0 0- 0- 0" ? NULL(DT_WSTR,10) : SUBSTRING([Date_Start],5,4) + "-" + SUBSTRING([Date_Start],3,2) + "-" + SUBSTRING([Date_Start],1,2)
However
0 0- 0- 0
values are not being replaced with NULLs by this expression.
I have been trying to resolve this for the best part of a day so at this point any suggestions/advice would be appreciated...Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

In your expression, there is a space at the start of the string. Is that how your data looks?

John

roryoflynn1 Ten Centuries Points: 1281 More actions · Answer 2

Hi John,

I have tried with and without spaces at the start but made no difference

roryoflynn1 Ten Centuries Points: 1281 More actions · Answer 3

The current string in the expression is

"0 0- 0- 0"

roryoflynn1 Ten Centuries Points: 1281 More actions · Answer 4

Hi All,

I have the following Expression working on a Derived Column in an SSIS Data Flow Transformation

[Date_Start] == " 0 0- 0- 0" ? NULL(DT_WSTR,10) : SUBSTRING([Date_Start],5,4) + "-" + SUBSTRING([Date_Start],3,2) + "-" + SUBSTRING([Date_Start],1,2)

However

0 0- 0- 0

values are not being replaced with NULLs by this expression.

I have been trying to resolve this for the best part of a day so at this point any suggestions/advice would be appreciated...Thanks

OK,

I have investigated this further . THe issue is not with the string

" 0 0- 0- 0" as I said previously.

The string I am trying to filter out is "0 0 0 0"

what is outputted is not NULL for this value but "0 0- 0- 0"

The attachment may help with this...

I have tried applying a number of expresions to this column

examples below

[MSTUB-xInt_Only-Rev-Dt] == "00000000" || [MSTUB-xInt_Only-Rev-Dt] == "0 0 0 0" || [MSTUB-xInt_Only-Rev-Dt] == "0 0- 0- 0" ? NULL(DT_WSTR,10) : SUBSTRING([MSTUB-xInt_Only-Rev-Dt],5,4) + "-" + SUBSTRING([MSTUB-xInt_Only-Rev-Dt],3,2) + "-" + SUBSTRING([MSTUB-xInt_Only-Rev-Dt],1,2)

[MSTUB-xInt_Only-Rev-Dt] == "0" + " " + "0" + " " + "0" + " " + "0" ? NULL(DT_WSTR,10) : SUBSTRING([MSTUB-xInt_Only-Rev-Dt],5,4) + "-" + SUBSTRING([MSTUB-xInt_Only-Rev-Dt],3,2) + "-" + SUBSTRING([MSTUB-xInt_Only-Rev-Dt],1,2)

There seems to be an issue recognizing the spaces between the 0s ?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Not all spaces are created equal. Maybe they're tabs or something else that could look like a space. There's a function (ASCII or something like that) that will return the ASCII value for a given character. It may be worth using that to check that the character you're checking for is the same as the ones in your string.

John

roryoflynn1 Ten Centuries Points: 1281 More actions · Answer 6

Hi,

using TRIM solves the issue

TRIM([MSTUB-xInt_Only-Rev-Dt]) == "0 0 0 0" ? NULL(DT_WSTR,10) : SUBSTRING([MSTUB-xInt_Only-Rev-Dt],5,4) + "-" + SUBSTRING([MSTUB-xInt_Only-Rev-Dt],3,2) + "-" + SUBSTRING([MSTUB-xInt_Only-Rev-Dt],1,2)