Querying Active Directory using SSIS Script Task Database. Journal. com. One of the most distinguishing characteristics of Control Flow Script Task available in SQL Server 2. 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 2. Integration Services is its. Well I have been working on this problem for awhile now and I just cant seem to figure it out. Here is what is going on A couple of weeks ago as I was doing some. One of the most distinguishing characteristics of Control Flow Script Task available in SQL Server 2008 Integration Services is its flexibility. This article. Member Of Active Directory Query ToolWhile there are several other tasks that offer a similar degree of. Active. X Script Task, none of them matches the. NET. programming model and its underlying framework. In this article, we will. 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. Active Directory functions as a store for a number of identifying. This is justifiable. Windows domain includes a built in employee. ID attribute perfectly suitable. Global Catalogs, so the approach described here is. Our sample script will query. Active Directory domain for its value given an arbitrary user name. The computer you are using should be a member of that or a. 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. ID as the output. In addition, for the sake of simplicity. It is important to note that this. Our. intention is to provide additional information regarding proper exception. Start by launching Business Intelligence Development Studio and. 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 Employee. ID and User. Name respectively, but you are obviously. Set the value of the first one to. Active. Directory alternatively, you can assign the value of a variable dynamically. Implementing. SSIS Package Configurations. Next, drag the Script Task icon. Toolbox onto the Control Flow tab of the Designer area of our default Package. Invoke the Editor dialog box of the Script Task by selecting the Edit. Ensure that Microsoft Visual Basic 2. Script. Language section. Main is listed as its Entry. Point. Use the ellipsis button. Read. Only. Variables to. User User. Name variable. Repeat the same process to add User Employee. ID. to the Read. Write. Variables textbox keep in mind that their values are. Click on the Edit Script. Visual Studio Tools for Applications VSTA window, with an auto generated. Script. Main. vb tab. Before we apply our custom programming changes, we need to add a reference. System. Directory. Services namespace, which is needed in order to interact. Active Directory domain controllers. To accomplish this, in the Project. Explorer window of Visual Studio Tools for Applications interface, right click. SSIS Script Task code whose name. ST prefix followed by a random GUID and select Properties. In the resulting window, switch to the References. Use the Add. command button within its Reference. Add Reference dialog box. Locate System. Directory. Services. assembly on its. NET tab and confirm your choice by clicking on the OK command. No template information. 10 Minute Prenatal Pilates. See the application log in Event Viewer for more detail., execute Dev. Env. exe. Install. VSTemplates from the command prompt while in the Program Files. Microsoft. Visual Studio 9. Common. 7IDE directory. To determine whether the procedure. System. Directory. Services appears in the. References in the VSTA 2. Project Properties window. At this point, we are ready to include extra code that will implement the. Switch to the Script. Main. vb tab in the VSTA project. In order to simplify referencing the newly added assembly, add Imports. System. Directory. Services directive at the top of the General Declarations. Next, scroll down to the beginning of Public Sub Main and paste the. Add your code here comment. Public Sub Main. Add your code here. Dim ds. Searcher As System. Directory. Services. Directory. Searcher. Dim ds. Results As System. Directory. Services. Search. Result. Dim user. Name As String. user. Name Dts. VariablesUser. Name. Value. To. String. Searcher New. System. Directory. Services. Directory. SearcherLDAP DCwidgets,DCcom. Searcher. Filter. Categorypersonobject. Classusers. AMAccount. Name user. Name. ds. Searcher. Search. Scope Search. Scope. Subtree. ds. Searcher. Page. Size 1. 00. Results ds. Searcher. Find. One. Dts. VariablesEmployee. ID. Value. ds. Results. Propertiesemployee. ID. Item0. To. String. Message. Box. ShowDts. Variablesemployee. ID. Value, Employee. ID, Message. Box. Buttons. OK. Dts. Manual Del Soldador Cesol on this page. Task. Result Script. Results. Success. Lets briefly review each of the statements above in order to clarify their. We start by defining three variables. The first one represents a System. Directory. Services. Directory. Searcher. Active Directory query. The second one is. The last of them provides a temporary storage. SSIS User User. Name variable, which value is obtained by referencing Dts. Variables. collection. We initiate the Directory. Searcher object by pointing it to our Active. Directory domain widgets. The filter narrows down the search to user. Categoryperson criterion is required in order to. AMAccount. Name in Active. Directory parlance is equal to the value we assigned to the SSIS. User User. Name variable. The next statement which dictates depth of the. Directory. Searcher. Search. Scope. is Subtree, which perfectly matches our requirements, but if appropriate you. Base or One. Level for more information, refer. MSDN article covering Directory. Searcher. Search. Scope. Property. We also set the Page. Size parameter to 1. At that point, we invoke Find. One method, which. The result takes the form of a collection, so to retrieve the value we are. User Employee. ID SSIS variable. In order to verify the outcome. Show method of Message. Box class, whose characteristics are documented in. Control Serial Port Visual Basic. Message. Box. Show Method article on the MSDN site. Finally, we indicate. Script. Results. Success to the Dts. Task. Result. See All Articles by Columnist. How to query Active Directory for all groups and group members I came up with something similar to marcs using the System. Directory. Services. Account. Management namespace Principal. Context context new Principal. ContextContext. Type. Domain. Group. Principal query. Principal new Group. Principalcontext. Principal. Searcher searcher new Principal. Searcherquery. Principal. Principal. Search. Resultlt Principal all. Principals searcher. Find. All. foreach Group. Principal group. Principal in all. Principals. Of. Typelt Group. Principal. Process group. User. Principal user. Principal in group. Principal. Members. Of. Typelt User. Principal. Process group member. The User. Principal class doesnt appear to expose any members that would allow you to determine if a user is andor has a manager, but you can still do it by obtaining the Directory. Entry for the user Directory. Entry user. Entry user. Principal. Get. Underlying. Object as Directory. Entry. if user. Entry null. Manager user. Entry. Propertiesdirect. Reports. Count 0. Managed user. Entry. Propertiesmanager. Count 0. Perform further processing. Youll need some additional logic, though, to determine if the user is a manager in the group youre currently looking at, rather than some other group. Maybe check the direct. Reports property to see if any of the users contained within are members of the current group.