Importing entire Active directory information using SSIS and .net framework 3.5 and above

Goal is to extract complete active directory information with some commonly used attributes using SSIS

There are other ways achieve this using Linked server , Microsoft OLE DB provider for directory services and native TSQL but the limitation is most of the users face is it can only retrieve 100o rows because of the default limitation set by the active directory and OLEDB provider doesnt allow us to over ride the page size option.

We can also use SQL Server CLR to achieve same goal but here there no limit of how many rows you can return in your result set

Last but not least using SSIS and here also there is no limit

We will be using Script component as source with .Net framework 3.5

source type

Source columns

set all the data types to string

AD code


Here is the code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Collections;
using System.Collections.Generic;
using System.DirectoryServices.AccountManagement;
using System.DirectoryServices;
public class ScriptMain : UserComponent

public override void PreExecute()
Add your code here for preprocessing or remove if not needed

public override void PostExecute()
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100

public override void CreateNewOutputRows()

foreach (string s in ACTIVE_DIRECTORY_DOMAINS)
using (PrincipalContext pc = new PrincipalContext(ContextType.Domain, s))
using (PrincipalSearcher sr = new PrincipalSearcher(new UserPrincipal(pc)))

PrincipalSearchResult<Principal> list = sr.FindAll();
foreach (Principal result in list)
DirectoryEntry de = (DirectoryEntry)result.GetUnderlyingObject();
Output0Buffer.AD = de.Properties[“samAccountName”].Value.ToString();
Output0Buffer.objectclass = de.Properties[“objectCategory”].Value.ToString();
Output0Buffer.domain = s;





Credits to Rory


One thought on “Importing entire Active directory information using SSIS and .net framework 3.5 and above

  1. Deepak says:

    Hi SQL Team,

    I need your help on the above Topic.

    I am using SQL server 2008 R2 ; Win XP OS(Home lap top), and have followed the above steps.

    When I have pasted the same code in the script component of the data flow task, the Build seems to be failing.

    Please help.

    Thanks and Regards,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s