By: Siddharth Mehta | Comments (2) | Related: 1 | 2 | 3 | More > Integration Services Development
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".
Select all the columns for the available input columns.
Add a connection on the connections page, select the ADO.NET Connection we just created and name it ADOTestDBConnection as shown below.
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.
Execute the package now and it should execute successfully and insert 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".
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.
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
- Test your database and script logic again to see if there is a possibility that a NULL value can break your package.
- Implement this solution in Script component when it's used as a "Transformation" type.
- Read the first part of the series: How to add NULL defense for dates in SSIS packages
- Download the sample code
About the author
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
