I have a client who is importing certain users from Active Directory. The paging on their AD is set to 20,000 records. When trying to pull data using a SQL statement, the query fails because it hits the maximum number of records and is unable to return more. You could work around a problem like this by editing your query filter to ensure that you always retrieve fewer than 20,000 records at a time, for example using the whenCreated field. However, there is no guarantee that whatever filter you use will always limit your return value to a maximum of 20,000 records. And you now need to build a loop construct to retrieve all the records since you want more than 20,000 records.
This is much easier to solve than you might think, judging from the number of forum questions out there on the subject (and how long it took me to piece it together). Here are the steps.
Create an SSIS package.
Add a string variable, scoped to the package, called SqlConn. Populate it with the connection string to the database you want to populate with the AD records.
Add a script task to your package. Open the script task, making sure that the ScriptLanguage is C# and not VB.
Click on the Edit Script button. On the right hand side you should see the Project Explorer window. Right click on the name of the Project File at the top of the tree and select Add Reference.
On the .NET tab scroll down and find System.DirectoryServices. Select it and click OK.
Make sure you see the reference appear in the References folder in the Project Explorer window.
Add these statements at the beginning of your script.
using System.DirectoryServices;
using System.Data.SqlClient;
Paste this script to replace the public void Main(). Edit the ds.Filter and Insert string values to meet your table requirements. Be sure to only select single value attributes of the object. If you try to use this method to import multi-value attributes such as “Description” from AD it won’t work. I’ll be writing about that next.
public void Main()
{
//Set up the AD connection;
using (DirectorySearcher ds = new DirectorySearcher())
{
//Edit the filter for your purposes;
ds.Filter = “(&(objectClass=user)(|(sAMAccountName=A*)(sAMAccountName=D0*)))”;
ds.SearchScope = SearchScope.Subtree;
ds.PageSize = 1000;
//This will page through the records 1000 at a time;
//Set up SQL Connection
string sSqlConn = Dts.Variables[“SqlConn”].Value.ToString();
SqlConnection sqlConnection1 = new SqlConnection(sSqlConn);
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
//Read all records in AD that meet the search criteria into a Collection
using (SearchResultCollection src = ds.FindAll())
{
//For each record object in the Collection, insert a record into the SQL table
foreach (SearchResult results in src)
{
string sAMAccountName = results.Properties[“sAMAccountName”][0].ToString();
string objectCategory = results.Properties[“objectCategory”][0].ToString();
//Replace any single quotes in the string with two single quotes for sql INSERT statement
objectCategory = objectCategory.Replace(“‘”, “””);
sqlConnection1.Open();
cmd.CommandText = “INSERT INTO Users (sAMAccountName, objectCategory) VALUES (‘” + sAMAccountName + “‘,'” + objectCategory + “‘)”;
reader = cmd.ExecuteReader();
sqlConnection1.Close();
} } } }
That’s it. This will iterate through all of the objects in Active Directory, regardless of paging size set on Active Directory.
To learn how to import multi-value fields from AD, read this post:
How to Query Multi-Value Fields from Active Directory using SSIS
