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.