One of the most distinguishing characteristics of Control Flow Script Task available in SQL Server 2008 Integration Services is its flexibility. This article demonstrates some of these capabilities by describing an approach that employs the Script Task to query data residing in an Active Directory domain database.
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
MessageBox.Show Method article on
the MSDN site). Finally, we indicate
successful completion of the task by assigning ScriptResults.Success to the Dts.TaskResult.