SSIS ISNULL Function

The SSIS ISNULL function is a NULL function that will check whether the given expression is a NULL value or not and returns boolean True or False. This article explains how to use the ISNULL function with an example and the syntax to check for the NULL values in a column is shown below.


ISNULL(<<expression>>)

To demonstrate the SSIS ISNULL 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.

Source Table

SSIS ISNULL function

To check/find 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 because it helps to write ISNULL function expressions and create new columns.

Data Flow to add Derived Column

Next, double-click on it to open the Derived Column Transformation Editor. Drag and drop the SSIS ISNULL function to the row cell under the Expression and replace the expression with the Name column to return boolean true or false. Either you can manually delete the complete expression or drag and drop the Name column will automatically replace it.

The below ISNULL expression will check the NULL values in Name, Education, Occupation, Yearly Income, and Sales columns and returns True if they are NULL. Otherwise, it returns False.

ISNULL( [Name] )

ISNULL( [Education] )

ISNULL( [Occupation] )

ISNULL( [YearlyIncome] )

ISNULL( [Sales] )
SSIS ISNULL Function Expression

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 ISNULL function package to see the NULL values in Name, Education, Occupation, Yearly Income, and Sales.

SSIS ISNULL Function to check column value is NULL or not