Get User Group From Active Directory

In order to retrieve the list of users and their groups from Active Directory, we will need to write some .NET code.  The following method shows the steps to write out every user and their group memberships to a tab delimited file:

 

The main points about the above code are:

• Step 1 sets up the parameters for the Active Directory search.  DirectoryEntry is a class in the System.DirectoryServices namespace that you use to specify where in Active Directory to begin the search.  In my case I used "LDAP://dc=vs,dc=local" as the path variable value to get all users in the domain since my domain is vs.local.  DirectorySearcher is used to perform the actual search; it is also in the System.DirectoryServices namespace.  The SearchScope property is set to search objects and their hierarchies.  You specify the attributes you want to retrieve by calling the PropertiesToLoad Add method.  The Filter property is set to return any object that represents a person.  The PageSize property sets the chunk size for retrieving items from the Active Directory.  Without specifying a PageSize you will only get the first 1,000 items.
  
  
• Step 2 executes the search by calling the FindAll method on the DirectorySearcher object, which returns a collection of objects.
  
• Step 3 creates the flat file to output the results.
  
• Step 4 iterates through the result collection.  Each item is a collection itself.
  
• Step 5 iterates through each item collection pulling out either a single value (e.g. samaccountname) or multiple values (e.g. memberof).
  
• The samaccountname attribute is the user's login name.
  
• The memberof attribute is a multi-valued collection which contains each Active Directory group that the user is a member of.
  
• The above code is part of a class called User contained in a class library called ADHelper.DLL.  By packaging the code in a class library rather than embedding it in the SSIS package, we can call it from the SSIS package as well as any .NET code.

Note that there are two requirements for deploying the above code so that it can be called from an SSIS package:

• ADHelper.DLL must be deployed to the Global Assembly Cache (GAC).  You can use the GACUTIL utility to do this or simply drag and drop the DLL to the \Windows\Assembly folder.
  
  
• ADHelper.DLL must be copied to the folder \Program Files\Microsoft SQL Server\90\SDK\Assemblies

SSIS Package Control Flow

We will use the following SSIS package control flow to synchronize our security tables with Active Directory: