Solved

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

Posted on 2010-11-08
3
6,483 Views
Last Modified: 2012-05-10
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
Comment
Question by:irb56
  • 2
3 Comments
 

Author Comment

by:irb56
ID: 34083452
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
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 34085980
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
 

Author Closing Comment

by:irb56
ID: 34131838
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now