Querying Active Directory using SSIS Script Task

November 8, 2010

One of the most distinguishing characteristics of Control Flow Script Task available in SQL Server 2008 Integration Services is its flexibility. While there are several other tasks that offer a similar degree of customization (for example, ActiveX Script Task), none of them matches the level of extensibility delivered in a consistent and seamless manner by the .NET programming model and its underlying framework. In this article, we will demonstrate some of these capabilities by describing an approach that employs the Script Task to query data residing in an Active Directory domain database.

To facilitate this goal, we will make a fairly likely assumption that our implementation of Active Directory functions as a store for a number of identifying properties of its users, such as an employee number. This is justifiable, considering that the schema, which defines all entities and their characteristics that can exist in a Windows domain includes a built-in employeeID attribute perfectly suitable for this purpose (note, however, that by default this attribute is not replicated to Global Catalogs, so the approach described here is applicable when operating within a single domain). Our sample script will query a designated Active Directory domain for its value given an arbitrary user name as an input. The computer you are using should be a member of that (or a trusting) domain and you should be logged on with that domain's credentials. To allow for interaction between the SSIS package and a code incorporated into the Script Task, we will utilize two SSIS variables - one containing the name of a target user (serving as an input parameter) and the other returning a corresponding employeeID as the output. In addition, for the sake of simplicity, we will temporarily forgo any error checking. It is important to note that this does not constitute the recommended approach (on the contrary, all your production code should take into account the possibility of run-time exceptions) and is used here strictly to focus on the primary objective. Our intention is to provide additional information regarding proper exception handling in an upcoming article.

Start by launching Business Intelligence Development Studio and creating a new project based on the Integration Services Project template. Once this is completed, activate the  Variables window (via View->Other Windows menu) and define two variables of Package scope and String data type. We will call them EmployeeID and UserName respectively, but you are obviously free to choose your own naming convention. Set the value of the first one to match the name of a user whose identifier you want to retrieve from Active Directory (alternatively, you can assign the value of a variable dynamically during package invocation, as described in "Implementing SSIS Package Configurations"). Next, drag the Script Task icon from the Toolbox onto the Control Flow tab of the Designer area of our default Package.dtsx. Invoke the Editor dialog box of the Script Task by selecting the Edit... option from its context sensitive menu. Ensure that Microsoft Visual Basic 2008 (which we will be using to write our sample code) appears in its ScriptLanguage section and that Main is listed as its EntryPoint. Use the ellipsis button (...) next to the ReadOnlyVariables to select the User::UserName variable. Repeat the same process to add User::EmployeeID to the ReadWriteVariables textbox (keep in mind that their values are case-sensitive). Click on the Edit Script... command button to trigger display of Visual Studio Tools for Applications (VSTA) window, with an auto generated code automatically included on the ScriptMain.vb tab.

Before we apply our custom programming changes, we need to add a reference to the System.DirectoryServices namespace, which is needed in order to interact with the Active Directory domain controllers. To accomplish this, in the Project Explorer window of Visual Studio Tools for Applications interface, right click on the top level node representing the SSIS Script Task code (whose name consists of the ST_ prefix followed by a random GUID) and select Properties from its context sensitive menu. In the resulting window, switch to the References section. Use the Add... command button within its Reference... cascading menu to activate the Add Reference dialog box. Locate System.DirectoryServices assembly on its .NET tab and confirm your choice by clicking on the OK command button (in case you encounter an error stating No template information found. See the application log in Event Viewer for more detail., execute DevEnv.exe /InstallVSTemplates from the command prompt while in the Program FilesMicrosoft Visual Studio 9.0Common7IDE directory). To determine whether the procedure completed successfully, verify that System.DirectoryServices appears in the list of References in the VSTA 2.0 Project Properties window.

At this point, we are ready to include extra code that will implement the desired functionality. Switch to the ScriptMain.vb tab in the VSTA project window. In order to simplify referencing the newly added assembly, add Imports System.DirectoryServices directive at the top of the (General) (Declarations) section. Next, scroll down to the beginning of Public Sub Main() and paste the code listed below (starting with the line following 'Add your code here comment):

Public Sub Main()

' Add your code here

Dim dsSearcher As System.DirectoryServices.DirectorySearcher 
Dim dsResults As System.DirectoryServices.SearchResult
Dim userName As String

userName = Dts.Variables("UserName").Value.ToString()
dsSearcher = New _
System.DirectoryServices.DirectorySearcher("LDAP://DC=widgets,DC=com")

dsSearcher.Filter = _
"(&(objectCategory=person)(objectClass=user)(sAMAccountName=" & userName & "))"
dsSearcher.SearchScope = SearchScope.Subtree
dsSearcher.PageSize = 1000

dsResults = dsSearcher.FindOne

Dts.Variables("EmployeeID").Value = _
dsResults.Properties("employeeID").Item(0).ToString
MessageBox.Show(Dts.Variables("employeeID").Value, "EmployeeID", MessageBoxButtons.OK)

Dts.TaskResult = ScriptResults.Success

End Sub

Let's briefly review each of the statements above in order to clarify their purpose. We start by defining three variables. The first one represents a System.DirectoryServices.DirectorySearcher object, which will handle our Active Directory query. The second one is intended to store its results. The last of them provides a temporary storage for SSIS User:UserName variable, which value is obtained by referencing Dts.Variables collection.

We initiate the DirectorySearcher object by pointing it to our Active Directory domain (widgets.com). The filter narrows down the search to user objects (applying objectCategory=person criterion is required in order to exclude computers from the result set), whose logon name (sAMAccountName in Active Directory parlance) is equal to the value we assigned to the SSIS User::UserName variable. The next statement (which dictates depth of the search) is actually redundant, since the default value of DirectorySearcher.SearchScope is Subtree, which perfectly matches our requirements, but (if appropriate) you have an option of changing it to Base or OneLevel (for more information, refer to the MSDN article covering DirectorySearcher.SearchScope Property). We also set the PageSize parameter to 1000, which becomes relevant when dealing with larger result sets (and is included here primarily for the sake of completeness). At that point, we invoke FindOne method, which returns the first object matching the criteria we outlined.

The result takes the form of a collection, so to retrieve the value we are interested in, we reference its first item and return it as a string, which is assigned to the User::EmployeeID SSIS variable. In order to verify the outcome, we display content of the variable via a message box (more specifically, we invoke Show method of MessageBox class, whose characteristics are documented in the MessageBox.Show Method article on the MSDN site). Finally, we indicate successful completion of the task by assigning ScriptResults.Success to the Dts.TaskResult.

» See All Articles by Columnist Marcin Policht