SSIS Replace SQL Table Nulls using Derived Column

This SSIS article shows how to replace the NULL values in SQL Server table rows or data with Empty space or any text using a Derived Column Transformation with an example.

The below screenshot shows the records in the Employee Join Example table.

Employee Join Example Table

SSIS Replace SQL Table Nulls using Derived Column

Drag and drop the Data Flow Task into the control flow region.

Data Flow Task

Double-click to open the SSIS Data Flow Region. Then, drag the OLE DB Source and double-click on it to open the Editor.

Next, click the New button to configure the OLE DB Connection Manager. Here, we have chosen the existing one, but you can create a new one by clicking the New button.

OLEDB Source to configure the OLE DB Connection Manager

In this example, we have chosen the existing table from the list. Next, go to the columns tab to check the columns and click the Ok button.

Choose the Table for SSIS Replace SQL Table Nulls using Derived Column

Drag the Derived Column Transformation and double-click on it to open the editor.

Derived Column Transformation Editor

Integration Services has a REPLACENULL() function to work with NULL values. First, drag the Department Name column and change it from add as a new column Replace DepartmentName.
Next, write/build the below expression.

REPLACENULL() function
REPLACENULL(DepartmentName, "")
SSIS Replace SQL Server Table Nulls using Derived Column 7

Next, drag the OLE DB Destination and double-click on it to open the Editor. Then, we have chosen the existing connection. Next, click the New button to create a new table.

OLE DB Destination

Go to the Mappings tab to check the input and available destination column mapping.

check the input and available destination column mapping

Run the SSIS Replace SQL Server Database Table Nulls using the Derived Column transformation package.

Run the SSIS Replace SQL Server Database Table Nulls using the Derived Column transformation package

As you can see from the SQL Management Studio, the NULLS replaces by empty or blank spaces.

Result

Let me change the Derived Column expression and rerun the package.

REPLACENULL(DepartmentName, "Hello")
Run the SSIS Replace SQL Server Database Table Nulls using the Derived Column transformation

Open the SQL to view the result.

Result