Null -SSIS packages using scripting

NULL Defense to your SSIS packages using scripting

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.