Solved

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

Posted on 2010-11-08
3
7,007 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Detach & Attach 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.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

713 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