Further to the series of posts on importing data from Active Directory, I’ve run into a new issue. For this client I built the exact same solution as described here Getting Around Active Directory Paging on SSIS Import, but got this lovely error message: “Index was out of range. Must be non-negative and less than the size of the collection.” It turns out there were empty values in some of the single-value fields. I hadn’t run into this previously, but I found a neat solution.

In the original solution I outlined how to create a simple SSIS script task in C# to import single value fields from Active Directory. I’ve added to this code to create a solution to import empty single-value fields.

A For Each statement for single-value fields has been added to the script to check if the field is empty before setting the variable value. Even though there is only one possible value for a single value field, the For Each statement still works nicely to check if it’s empty. Here is the code snippet of the For Each statement:

//If the property is null, set the variable to blank, else set it to the value in the propertystring Mail = "";
ResultPropertyValueCollection valueCollectionMail = results.Properties["Mail"];
foreach (String sField in valueCollectionMail)
{
//Replace any single quotes with two single quotes for SQL Statement