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

result

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

Advertisements

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,
    Maroli.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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