SSIS handling NULL and blank spaces

Question

hello i am new to SSIS and iam receiving text file created by SSIS iam using wizard to load it to oracle table but in the text file there is columns contain the String NULL and other contain blank string instead of zero length column is there an auto way to make these value to become actual null value in the table or do i have to create derived column for each one of theses cases

thank you,

Answer 1

If you want to convert the value into null if your input value in empty/blank, then you can try (under assumption datatype is of string/varchar) :

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

Answer 2

I don't think there is any way to do this using the standard Flat File Source SSIS provides. To do this I make use of a custom component called Delimited File Source, which can be downloaded here: http://ssisdfs.codeplex.com/. As its name indicates, it's also much better at handling delimited files, plus it has the option of treating empty strings as NULL.

Answer 3

Within an SSIS project in the SQL Server Data Tools for Visual Studio 2015/SQL Server 2016, the way to address the handling of empty columns seems to be via a property of the Flat File Source component (not certain whether space-only columns qualify):

1. Right-click the Flat File Source and choose Show Advanced Editor....
2. Select the Component Properties tab.
3. Set RetainNulls property to True (default is False).

Answer 4

I faced the same issue, you can use a script component and add the code below to loop through all the columns and replace each text null with actual null value...

foreach (PropertyInfo dataColumn in Row.GetType().GetProperties())
{
    if (dataColumn.Name.ToLower().EndsWith("_isnull") == false && dataColumn.PropertyType == typeof(string))
    {
            object objValue = dataColumn.GetValue(Row, null);

            if (objValue != null && objValue.ToString() == 'NULL')
            {
                 dataColumn.SetValue(Row, null, null);
            }
    }
}

Code explanation is here

Answer 5

If you're using SSIS 2008, there's also the Null Manager component from Tactek Data Systems. It isn't free, but it's pretty cheap - like $10 bucks. (www.tactek.com). You can convert empty strings to nulls, nulls to empty strings, and nulls to "filler" values like "Unknown" or "NA".