How to convert blank into null in ssis

问题:

I am extracting data from excel in ssis. One of the excel column contain blank values. so i need to replace blank value with null otherwise my package is going to fail. Any suggessions?

回答1:

You might also want to check the option to Keep Null values on both source and destination connection (if available)

回答2:

In DataFlow use DerivedColumn component.

Replace your column and in expression put this line of code

ColumnName == "" ? NULL(DT_WSTR,50) : ColumnName

It will make sure to return null if column is empty

回答3:

Did you tried something like (under assumption datatype is of string/varchar) :

LEN(TRIM([ColumnName]))==0 ? NULL(DT_WSTR, 10) : [ColumnName] 

回答4:

Fix for the above problem :::::

Source and destination must checked with the option allow null values and it will work.

回答5:

I had a similar instance where I wanted to replace a string in a date column to NULL, and I used the following code:

LEN(your_data) < 2 ? NULL(DT_WSTR,50) : your_data

In this case, the string had a length of only 1. I then used the data conversion transformation to get it to the dt_dbtimestamp type.

Hope that helps!

回答6:

You should use the Derived Column component, and a conditional expression. Since this is a conditional statement data types will need to be the same to create a Boolean result.

Try using this:

ColumnName == (DT_DATE)"" ? NULL(DT_DATE) : ColumnName