Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to add NULL Defense to your SSIS packages for Operators and Functions


By:   |   Last Updated: 2010-07-01   |   Comments (7)   |   Related Tips: 1 | 2 | 3 | More > Integration Services NULL Management

Problem

In the previous two parts of the NULL Defense series, we saw how to add NULL Defense at the database level for dates and how to add NULL Defense within our package for scripting. We discussed earlier that NULLs can cause undesirable results in data and can cause transformations to fail. Functions and operators are no exception. NULLs will easily by-pass operators and/or functions without any exception until we investigate the results and finally figure out that NULL values were the cause.

In this tip, we will address the issue of how to add NULL Defense to defend our transformations from breaking and shield our operators from generating undesirable results due to NULL values.

Solution

For this discussion, we will leverage the package we built in the first part of the NULL Defense Series. We will edit the DataFlow task and make a few changes to simulate the problem that we are going to discuss.

Follow the steps below after you have created the base package.

Add a Derived Column Transformation after the OLE DB Source Adapter and add a Column "FullName" as shown in the picture below.

 how to add NULL Defense to defend our transformations from breaking and shield our operators from generating undesirable results due to NULL values

Now let's say we are interested only in records that have a FirstName value of "Siddharth". So change the split condition of the Conditional Split transformation as shown in the below picture.  Notice also that I changed the Default output name to "Invalid Records".

Add a Derived Column Transformation after the OLE DB Source Adapter and add a Column "FullName"

Now execute the package and if you have followed the steps as explained above, your package should fail as shown in the below picture.  Also, note that I have changed the Conditional Split output to have Invalid Records go to Invalid Records and Sid go to Valid Records.

note that I have changed the Conditional Split output to have Invalid Records go to Invalid Records and Sid go to Valid Records

There are actually two issues in the package, one which you can apparently see by the red color of the failed transformation and there is another issue which we will discuss shortly.

If you open the Progress Tab, you will find the cause of the exception with a error message. In my package I get the below message.

"[Split valid and invalid rec [31]] Error: The expression "FirstName == "Siddharth"" on "output "Sid" (42)" evaluated to NULL, but the "component "Split valid and invalid rec" (31)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error."

The cause of the error is very apparent from the error message, that for the comparison a boolean value is expected. You might think that NULL is not equal to the value "Siddharth" so the result should be False. But that is not the case, as NULL cannot be compared to a value, because NULL is something that can be thought of as non-existent.

So now edit the Conditional Split transformation as shown in the below picture using the code below.  Basically this is saying if the FirstName is not null and FirstName equals Siddharth then this is True.

!ISNULL(FirstName) && FirstName == "Siddharth"
now edit the Conditional Split transformation as shown in the below picture using the code below

The issue I was pointing out is not apparent, but can be visualized by placing a Data Viewer between the Derived Column transformation and  the Conditional Split transformation. To do this, double click on the line between the Derived Column and Conditional Split transforms, select Data Viewers and add a Grid data viewer.

Execute the package and you should be able to see, that the FullName field that we created in the Derived Column transformation is not providing the desired output as shown below. We can see that two of our FullName columns have NULL values.  You would find similar results when you use functions with fields containing NULL values.

the FullName field that we created in the Derived Column transformation is not providing the desired output

Edit the Derived Column transformation and change it as shown in the below picture using the below code.  This is saying if the FirstName is NULL then make it blank otherwise use the FirstName, then add a space between the FirstName and LastName and then do the same NULL check on the LastName.

(ISNULL(FirstName) ? "" : FirstName + " ") + (ISNULL(LastName) ? "" : LastName)

Edit the Derived Column transformation and change it as shown in the below picture using the below code

 

Now execute the package and if you have made the changes as explained above, your package should execute successfully with the desired results.

Next Steps


Last Updated: 2010-07-01


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, October 27, 2016 - 5:35:09 PM - Dipti Rode Back To Top

HI,

When I run a query through SSMS I see some values in the column as 'NULL' in the Output.

But when same query is run through SSIS  those NULLs are displayed as 'Blanks'.

 

I want to see NULLs in the output through SSIS.

Can you help?

 


Monday, February 29, 2016 - 5:42:53 AM - Dhirendra K DhiraJ Back To Top

Hi Siddharth,

I want to use use "NOT EXIST" function in my SSIS package. How  I can impliment this with my project ? 

My Requirement is that, I have a table who contains Accno and profile details. Now I wants to enter those Accono which is not present in the same table. Means that only new Accno will be insert in to the table and old will be discard. IS there any specific tool given ? I know that it can achived by normal sp with EXIST or NOT EXIST function. But i am looking for any othe way(via NOT EXIST fn with conditional split Tools)....I am waiting for your reply.

Regards,

Dhiraj,

M:8882282364

 

 

 

 

 


Thursday, June 25, 2015 - 11:15:46 AM - SCott Back To Top

Thank you Siddharth.

I couldn't figure out how to exclude nulls in the conditional split transformation until I read you demonstration. !ISNULL . Thanks again!


Thursday, May 16, 2013 - 8:34:45 AM - Siva Prasad Back To Top

Even after making NULLs to Blank values and it doesnt behave the way you want, please trim your Column to remove the unnecessary spaces by using LTRIM(RTRIM(Columnname)). I faced this issue today hence thought of sharing it.


Thursday, July 19, 2012 - 12:09:24 PM - Lakki Back To Top


Thursday, July 19, 2012 - 12:04:07 PM - Lakki Back To Top

 

The simple things eat more time.This is really helpfull.


Thursday, July 01, 2010 - 2:08:48 PM - tskelley Back To Top

Thank you for taking the time to share and demonstrate your findings.  This is definitely an issue that affects any developer working with SSIS data handling.  One little tweak you may want to add is to wrap the statement below with a TRIM to prevent extra spaces if the first name is valid, but no last name exists. 

(ISNULL(FirstName) ? "" : FirstName + " ") + (ISNULL(LastName) ? "" : LastName)

becomes

TRIM((ISNULL(FirstName) ? "" : FirstName + " ") + (ISNULL(LastName) ? "" : LastName))

or even RTRIM would be sufficient in this case.

 


Learn more about SQL Server tools