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

set all the data types to string

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;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}

public override void PostExecute()
{
base.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()
{
string[] ACTIVE_DIRECTORY_DOMAINS = new string[] { “WEISCORP”, “WEISSTORE” };

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.AddRow();
Output0Buffer.AD = de.Properties[“samAccountName”].Value.ToString();
Output0Buffer.objectclass = de.Properties[“objectCategory”].Value.ToString();
Output0Buffer.domain = s;

}

}
}

}
}

}

Credits to Rory