Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-11-08
3
Medium Priority
?
7,882 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1500 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

704 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