ssis replace null with empty string

Question

I am using conditional split on SSIS and I am getting errors as some of the rows contain NULL values. I know this can be changed by using the Derived Column and the REPLACENULL function. However, my table has about 200 columns and this would be very tedious to carry out.

I was wondering if there is anything I can do using Script Component which would loop through all the rows and change any NULLS to an empty string?

Can you not do this at the Extract or Load stage? What's the data source? db table, flat file, web service? — Mazhar, Sep 17 '18 at 09:05
The data source is a SQL table. I have managed to do it like this `SELECT ,ISNULL([Col1], '') AS [Col1] FROM [DBO].[MyTable]`. I just wanted to know if there was a better way to do it? @Mazhar — theJ, Sep 17 '18 at 09:27

score 0 · Answer 1 · answered Sep 17 '18 at 13:47

If you can not do the data conversion in your source's select You can do that in on Script Component in your `DataFlow.

Before passing your data flow to your conditional split you can add a Script Component as below image :

Then in your script component you need to have below code :

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    public override void ProcessInput(int InputID, Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer Buffer)
    {
        while (Buffer.NextRow())
        {
            var cols = Buffer.ColumnCount;
            //loop through each column
            for (var i = 0; i < cols; i++)
            {
                //get datatype of the column
                var type = Buffer.GetColumnInfo(i).DataType;
                //only operate on columns of type DT_STR or DT_WSTR
                if (type == DataType.DT_STR || type == DataType.DT_WSTR)
                {
                    //grab the data from the column and trim it
                    var colData = Buffer[i].ToString().Trim();
                    if (string.IsNullOrEmpty(colData))
                    {
                        //if the trimmed data is blank or null, set the output to null
                        Buffer.SetNull(i);
                    }
                    else
                    {
                        //if the column has data, set the output to the trimmed version
                        Buffer.SetString(i, colData);
                    }
                }
            }
        }
    }


}

But as i told and it is mentioned in comment the best way if it is possible is to do that in select when you want to read data from your source.

Select 
    ISNULL(A.Field1,'') AS Field1,
    ISNULL(A.Field2,'') AS Field2,
    ....
FROM A

ssis replace null with empty string

1 Answers1