I am extracting information from an excel sheet and loading it into a database. Before I load it into the database, I must transform all of the "-" in the excel sheet to NULL values.

I am currently trying to do this with the derived column transformation in a data flow object. It works if the data type is a Unicode string [DT_WSTR], however it will not work with a double-precision float [DT_R8].

The expression that works for the unicode strings is below.

[EquipmentID]  == "-" ? (DT_WSTR,50)NULL(DT_WSTR,50) : [EquipmentID]

The expression that is not working for the double-precision floats is below.

[SideDoorType] == "-" ? (DT_R8)NULL(DT_R8) : [SideDoorType]
  • I'm confused. A column in a data flow is always going to be the same data type, they can't be mixed. A float won't hold the value - because that's not a number (or a NULL) – billinkc Mar 30 '17 at 19:20
  • Sometimes zeros are formatted as - in Excel. But they are still a 0 under the covers. I would expect SSIS will see the 0 not the - on the numeric column. Can you check this? – GregGalloway Mar 31 '17 at 1:25

First of all, there is no need to cast NULL() function (DT_R8)NULL(DT_R8) you can use it without casting. Only you will need to cast when working with DT_STR. Read more at:

The problem is that you are using a Case statement that returns 2 datatypes

[SideDoorType] == "-" ? NULL(DT_R8) : [SideDoorType]

You are comparing [SideDoorType] column to a string value "-", if it is equal, you are trying to return a double-precision floats NULL , else you are returning [SideDoorType] column.

you can try using the following expression it might be working

if [SideDoorType] type is DT_WSTR or DT_STR, you have to cast the column in the false part expression

[SideDoorType] == "-" ? NULL(DT_R8) : (DT_R8)[SideDoorType]

if [SideDoorType] type is DT_R8, you have to cast the column in the comparaison expression

 (DT_WSTR,50,1256)[SideDoorType] == "-" ? NULL(DT_R8) : [SideDoorType]

And if these two expressions are not working, you have to use a string dataType like the first expression you provided, because it looks like your column contains values that cannot be converted to DT_R8, or use a Script Component instead of Derived Column and write a script to clean your data before casting it to float

  • 1
    Also, there's no need to cast the result of NULL function to the target data type: (DT_R8)NULL(DT_R8) should be replaced with NULL(DT_R8) – Adán Bucio Apr 24 '17 at 5:39
  • @AdánBucio i fix it. Thx – Hadi Apr 24 '17 at 5:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.