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
SSIS Replace SQL Table Nulls using Script Transformation
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.
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. For more Data Loading options >> Click Here.
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 Script Component will open the Component Type window. Choose the Transformation option and click OK.
Within the Input Columns tab, choose the DepartmentName as the Read Write Column.
Click the Edit Script button.
Write the following C# code to replace the NULLs in the Department Name column with black spaces. Close the c# script window and click OK to close the Script Transformation Editor.
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.
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 replaces by empty or blank spaces.
Let me change the C# script transformation code and rerun the package.
Open the SQL to view the result.
