SSIS Replace SQL Table Nulls using Script Transformation

This SSIS article shows how to replace the SQL Server table NULL values with Empty space using a Script Component Transformation with an example.

The table that we use

Employee Join Example Table

SSIS Replace SQL Table Nulls using Script Transformation

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 Script Component will open the Component Type window. Choose the Transformation option and click OK.

SSIS Replace SQL Server. NULLS using Script Transformation 1

Within the Input Columns tab, choose the DepartmentName as the Read Write Column.

Choose Column in SSIS to Replace SQL Table Nulls using Script Transformation

Click the Edit Script button.

Edit Script Button

Write the following C# code to replace the NULLs in the Department Name column with black spaces.

SSIS Replace SQL Table Nulls using Script Transformation 4

Close the c# script window and click OK to close the Script Transformation Editor.

SSIS Replace SQL Table Nulls using Script Transformation 5

Next, drag the OLE DB Destination and double-click on it to open the Editor. Then, we chose the existing connection and table to store the records.

Ole. DB Destination and Table

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 C# script transformation code and rerun the package.

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

Open the SQL to view the result.

Output