Null in SQL Server - SSIS

Dealing with NULLs

In SSIS, variables can’t be set to NULL. Instead, each variable data type maintains a default value in the absence of a value. For strings, the default value is an empty string, rather than the default of NULL that you might be used to in database development. However, Data Flow components can most certainly contain NULL values. This creates problems when variables are intermixed within Data Flow components. This mixture occurs either within a Script Task or within an expression.

However, if a value in the Data Flow needs to be set to NULL or even tested for a NULL value, this is another matter altogether and can be accomplished rather easily with the ISNULL() expression function and the NULL (type) casting functions. Just understand that variables are going to behave a little differently

NULLs and Variables

The reason you can’t set variables to NULL values is related to the COM object variant implementation of variables in the SSIS engine. Regardless of the technical issue, if you are testing a variable for the absence of a value, you have to decide ahead of time what value you are going to use to represent the equivalent of a NULL value, so that you can test for it accurately. For example, the DateTime variable data type defaults to 12/30/1899 12:00:00 a.m. if you purposely set it to NULL. You can test this out yourself by creating a DateTime variable and setting it equal to an expression defined using the casting function NULL(DT_DBTIMESTAMP). 

It helps to get a handle on the default values for the SSIS variable data types. You can find them in this table:

Using this table of default values, the following expression could be used in a precedence operation after testing for the absence of a value in a string variable MyNullStringVar:

@[User::MyNullStringVar]==””

If the value of the user variable is an empty string, the expression evaluates to a True value and the step is executed.

A frequent logic error that SSIS developers make is to use a variable to set a value from an expression that will be used within a multiple instance looping structure. If the value is not reset in a way that enables clean retesting, the value of the variable will remain the same for the life of the package. No error will be raised, but the package may not perform multiple iterations as expected. Make sure a variable is reset to enable retesting if the test will be performed multiple times. This may require additional variables to cache intermediate results.

Frequently Asked SSIS Interview Questions & Answers

NULLs in Data Flow

Using the NULL function in Data Flow Transformations is a different matter because values in a Data Flow can actually be NULL. Here you can use the expression function to test for NULL values in the data stream. Trouble usually stems from a misunderstanding of either how the ISNULL() function works or what to do after a NULL value is found. First, the ISNULL() expression function tests the expression in the parentheses for the value of NULL. It does not make a substitution if a NULL value is found, as the same-named function does in TSQL. To emulate the T-SQL function ISNULL(), build an SSIS expression in a Data Flow, as shown here:

IsNull(DATA_COLUMN) ? YOUR_DEFAULT_VALUE : DATA_COLUMN

If instead you want to set a column to NULL based on some attribute of the data in the incoming data stream, the logical structure is similar. First, provide the testing expression followed by the actions to take if the test is true or false. Here is a function that sets a data column in a Data Flow to NULL if the first character starts with “A”:

SUBSTRING([MyColumn], 1, 1)==”A” ? NULL(DT_WSTR, 255) : [MyColumn]

A typical issue that occurs when handling NULLs doesn’t actually have anything to do with NULL values themselves but rather with string expressions. When creating data streams to punch back into RDBMS data destinations, you will often want to send back a column with NULL values when a test on the data can’t be completed. The logic is to either send the column data back or replace the column data with a NULL value. For most data types, this works by sending the results of the NULL function for the data type desired. For some reason, this works differently when you want to save non- Unicode data with a NULL value. You’d expect the following expression to work, but it doesn’t:

SUBSTRING([MyColumn] , 1, 1)==”A” ?
NULL(DT_STR, 255, 1252) : [MyColumn]                              (This doesn’t work in SSIS)

The preceding example won’t work because of how SSIS handles NULL values for the non-Unicode string type as parameters. The only way to fix this is to cast the NULL function as follows:

SUBSTRING([MyColumn] , 1, 1)==”A” ?
(DT_STR, 255, 1252)NULL(DT_STR, 255, 1252) : [MyColumn]

This section should have clarified the common issues you are likely to encounter when dealing with NULL values, especially as they relate to strings. However, there are still some tricks to learn about dealing with strings, which we cover next.

List of Related Microsoft Certification Courses: