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 using scripting


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

Problem

As we discussed in the first part of this NULL Defense series, NULL values in data is one of the most common reasons that can make your SSIS package fail. The reason for the same is that many controls are vulnerable to interpret NULL undesirably, which can effectively cause the transformation to either fail or return unexpected results. In this part of NULL Defense series, we will discuss how to create NULL Defense in Script Transformation.

Solution

For the discussion of the problem in question, we will leverage the same package that we built in the first part of the NULL Defense Series.

In order to simulate the issue, create another table "NULLDefenseReplica" in the "TestDB" database. This table should have the same definition / structure as that of the NULLDefense table. Basically we are creating a destination table that we would use to insert records from the NULLDefense table which we are using as a source in our package.

Create a new ADO.Net connection to the "TestDB" database and name it MyADOConn.

Add a Script Transformation to the package, select the type as "Destination", and name it "Insert Records". Add the datapath from ValidRecords multicast output and join it to this Script Transform. Now configure the Script component as follows.

Rename the input stream from "Input 0" to "MyInputStream".

NULL values in data is one of the most common reasons that can make your SSIS package fail

Select all the columns for the available input columns.

Create a new ADO.Net connection to the "TestDB" database and name it MyADOConn

Add a connection on the connections page, select the ADO.NET Connection we just created and name it ADOTestDBConnection as shown below.

select the ADO.NET Connection we just created

Now we need to configure this script transform to insert records into our NULLDefenseReplica table. I will not go into the details of explaining the entire code, but if you would like to learn more you can read about it on MSDN BOL. Add the code as can be seen in the below. You can download it from here.

 configure this script transform to insert records into our NULLDefenseReplica table

Execute the package now and it should execute successfully and insert one record in the NULLDefenseReplica table.

nsert one record in the NULLDefenseReplica table

Now for testing, remove the Script destination from the "Valid Records" multicast transformation and add the output from "Invalid Records" multicast to the input of the script transformation. Execute the package and you should encounter an error as shown in the picture below. The reason for this is what Microsoft states in MSDN BOL as "If the Script component contains a script that tries to read the value of a column that is NULL, the Script component fails when you run the package. We recommend that your script use the IsNull method to determine whether the column is NULL before trying to read the column value".

remove the Script destination from the "Valid Records" multicast transformation and add the output from "Invalid Records" multicast to the input of the script transformation

To build our NULL Defense for this problem there are two parts to this solution. One part is validating if the field in the row being processed contains a null value and the second part is assigning NULL or valid values to parameters based on values found in the fields. Edit the script and change the code as shown below and execute the package. The package should execute successfully.

To build our NULL Defense for this problem there are two parts to this solution


we have successfully built a NULL Defense for one of the most common scenarios in a Script transformation where NULL values in data can make your package fail

So finally we have successfully built a NULL Defense for one of the most common scenarios in a Script transformation where NULL values in data can make your package fail.

Next Steps


Last Updated: 2010-06-22


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.



    



Wednesday, October 08, 2014 - 2:42:53 PM - James Back To Top

 

Thank you so much for taking the time to post that. I looked and looked trying to find a solution to this issue and your posting helped me solve it. Thank you!


Friday, July 05, 2013 - 8:36:10 AM - Hirva Back To Top

Very helpful post. Thank you very much.


Learn more about SQL Server tools