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.
SSIS Replace SQL Table Nulls using Derived Column
Drag and drop the Data Flow Task into the control flow region.
Double-click to open the SSIS Data Flow Region. Then, drag the OLE DB Source and double-click on it to open the Editor. For more Data Loading options >> Click Here.
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.
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.
Drag the Derived Column Transformation and double-click on it to open the 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(DepartmentName, "")
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.
Go to the Mappings tab to check the input and available destination column mapping.
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 are replaced by empty or blank spaces.
Let me change the Derived Column expression and rerun the package.
REPLACENULL(DepartmentName, "Hello")
Open the SQL to view the result.
