Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8212
  • Last Modified:

SSIS derived column expression to convert empty or blank string to NULL

Hi,

I have a CSV file to load into a SQL table. Two of the columns from the CSV file are typed as float in the corresponding SQL table. The problem I've got is that the CSV file contains blank strings that SSIS cannot convert to the float typed columns of the destination. I believe I can resolve this by creating a derived column task that converts empty or blank strings to a NULL. However, I'm having trouble constructing a SSIS expression in the derived column task. I've tried the following but there must be a syntax problem:
RTRIM([MyColumn]) == "" ? NULL(DT_R4) :  [MyColumn]

Can someone please advise?

Thanks in advance.
0
irb56
Asked:
irb56
  • 2
1 Solution
 
irb56Author Commented:
I just discovered that the following expression works ok in my derived column task:
RTRIM([MyColumn]) == "" ? NULL(DT_WSTR,50) : [MyColumn]
I think this is because the input column data type (coming out of the CSV source) is DT_STR. However, I'm puzzled why the following expression generates an error:
RTRIM([MyColumn]) == "" ? NULL(DT_STR,50,1252) : [MyColumn]

The error message for the second excpression is:
For operands of the conditional operator, the data type DT_STR is supported only for input and cast operators. The expression RTRIM([MyColumn]) == "" ? NULL(DT_STR,50,1252) : [MyColumn] has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitly cast with a cast operator.

Following the lead from the final sentence in the paragraph above, I tried the following expression but this generates the exact same error.
RTRIM((DT_STR,50,1252)[MyColumn]) == "" ? NULL(DT_STR,50,1252) : (DT_STR,50,1252)[MyColumn]

Any ideas?
0
 
vdr1620Commented:
you can double click on CSV Connection Manager and Then click on Advanced Tab and then select the Column and see the data type.. Check the data type, i believe it is WSTR, because the expression is correct and the error is being caused only because of the Datatype mis match.. I am really not sure why the last expression gives you an error..probably bcz of the same reason

If your csv file has a data type of WSTR .change it to STR and see if the last exp works
0
 
irb56Author Commented:
Thanks for your help. I decided that what was really needed was a destination column of type int. The output from the CSV source is string by default and the column contains some blanks that need to convert to NULL. The following Derived Column expression to replace MyColumn works just fine:
RTRIM([MyColumn]) == "" ? NULL(DT_I4) : (DT_I4)[MyColumn

I expect the original problem was probably along the lines you suggested. Cheers! :-)
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now