Solved

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

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 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