The SSIS REPLACENULL function is a NULL function that will replace the nulls in the first expression with the expression in the second argument. It is very helpful to replace bulk NULLs with the default values. This article explains how to use the REPLACENULL function and the syntax to replace the NULL values in a column with default expression is shown below.
REPLACENULL(<<Actual_expression>>, <<Rep_expression>>)
Actual_expression: A Column name where the search for NULL values will happen.
Rep_expression: An expression to replace the NULL values in Actual_expression.
To demonstrate the SSIS REPLACENULL function, we use the Employee table below, which has 15 records. Please refer to the Union All Transformation, Derived Column Transformation, Built-in Functions, and SQL Server articles in SSIS.
SSIS REPLACENULL function
To replace the null values, add a Data Flow Task and double-click on it to enter the Data Flow region. Next, add the OLE DB Source to read data from the above SQL database table. Please join the OLE DB Source to Derived Column Transformation to write REPLACENULL function expressions and create new columns.
Next, double-click on it to open the Derived Column Transformation Editor. Drag and drop the SSIS REPLACENULL function to the cell under the Expression and replace the first expression with the Name and the second with default text. Either you can manually delete the complete expression or drag and drop the Name column will automatically replace it.
The below expression will replace the NULLs in the Name column with Hello, NULLS in the Education column with Self, and NULLS in the Sales column with 999.
REPLACENULL( [Name], "Hello" )
REPLACENULL( [Education], "Self" )
REPLACENULL( [Occupation], "Coding" )
REPLACENULL( [YearlyIncome], 100000 )
REPLACENULL( [Sales], 999 )
Click OK to close the Editor window. Drag and drop the Union All Transformation and connect the Derived Column to it. Next, right-click on the connection node and choose the Enable Data Viewer.
Please run the SSIS REPLACENULL function package to see the replaced NULLs in the Name, Education, Occupation, YearlyIncome, and Sales.
