Active Directory SSIS Data Source

Want to make one of these? See biml active directory ssis data source

The authoritative source for email addresses in my organization is Active Directory. Pretty much everything else comes from Payroll. When we rolled out the corporate credit card vendor, we had to send them a list of people, their unique identifier (clock number in our case) and their email address. We already had a process that pulled data from the payroll system so we just needed a way to tie in the AD information. We managed to get an existing attribute of AD filled out for all our people, employeeNumber, and that allowed us to build out the following package.

My initial approach was to use a linked server to AD from within SQL Server and my god, I'd rather write complex queries against mysql or Access than go through that again. I eventually ran into issues with certain datatypes not being able to be returned. I've since lost the link but I found a writeup that talked about fields like memberof being of type i8 which means you're humped. I ran into pain with doing any sort of a complex where clause as well. The i8 datatype will be an issue whether you use query from SSIS or SSMS. At any rate, I wanted to document what an approach could be for querying data from Active Directory.

Known issues, from least to most severe if you're going to copy/paste my solution.

• Speed-the group lookup script is dog slow
• MaxPageSize aka number of rows = 1000-How Active Directory Searches Work
• Nested Groups-the script only pulls back the first degree children

With those disclaimers out of the way, the way I designed this package was to dump AD information to files that would be consumed by other processes. In the event something went wrong, I'd be able to have a known state to debug from instead of worrying about a LAN admin made a change while my process was running. The important thing I saw it was getting a list of all the people out and all the groups they belonged to as we had upcoming projects that needed clock number and email address but also needed group membership information.

Create a Connection Manager for ADO.NET . Use the ".Net Providers for OleDb\OLE DB Provider for Microsoft Directory Services" For me, it was the second, non-default, folder. For Server or file name: use your domain controller. It should look something like this.

From World of Whatever

Grab a data reader source and wire it up to your new connection manager. This query will pull back all the items in AD that are Users (sAMAccountType = 805306368)

SELECT
    distinguishedName
,   mail
,   samaccountname
,   Name
,   employeeNumber
,   objectSid
,   userAccountControl
,   givenName
,   middleName
,   sn
FROM
    'LDAP://DC=domain,DC=net'
WHERE
    sAMAccountType = 805306368
ORDER BY
    sAMAccountName ASC

I use the Raw File Destination (binary) to hold all the Active Directory information because they are faster and don't require the hassle of defining connection managers. Data coming out of AD is either integers or unicode text streams. I know my data well enough to know unicode isn't a concern. Converting to strings, I used a length of 28 for objectSid, 64 for sn, givenName, middleName, 255 for samaccountname and Name and 256 for distinguishedName and mail. It's been a few years since I wrote the package so I can't recall the why on those lengths. Your mileage may vary.

From World of Whatever

IsActive - this one bit me in the butt. For the userAccountControl a value of 512 is an active AD account, 514 is a disabled account. I have that in documentation from another team. Unfortunately, that's not quite accurate. That field is really a bitmask. A year or two after we implemented my package, we learned that inactive people were showing up in feeds which was impossible, we filter them (514s) out. I then learned that a 514 is really just a 512 (NORMAL_ACCOUNT) ORed with 2 (ACCOUNTDISABLE). An automated process had been implemented that created accounts with expired passwords or some such nonsense which had a net result of disabled accounts reading as 546 (regular became 544 - Account Enabled - Require user to change password at first logon).

The group lookup is an asynchronous script that writes a row to the output buffer for each group they are a member of.

Imports System
Imports System.Data
Imports System.Collections.Generic
Imports System.DirectoryServices
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Dim User As DirectoryEntry

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Try
            'MsgBox("LDAP://" + Row.distinguishedName)
            User = New DirectoryEntry("LDAP://" + Row.distinguishedName)
            With GroupUserOutputBuffer
                For Each group As String In User.Properties("memberOf")
                    GroupUserOutputBuffer.AddRow()
                    GroupUserOutputBuffer.sAMAccountName = Row.sAMAccountName
                    GroupUserOutputBuffer.employeeNumber = Row.employeeNumber
                    GroupUserOutputBuffer.distinguishedName = Row.distinguishedName
                    GroupUserOutputBuffer.memberOf = group
                Next
            End With
        Catch ex As Exception
            'ErrorOutputBuffer.AddRow()
            'ErrorOutputBuffer.groupdistinguishedName = Row.
        End Try
    End Sub

End Class

Below is an example of the Users and groups file if you want to visualize it

Users.raw

sAMAccountName	userAccountControl	employeeNumber	distinguishedName	isActive	mail	name	firstName	middleName	lastName
AAchoo	512	123456789	CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=net	True	AAchoo@domain.net	Angela Achoo	Angela		Achoo

Groups.raw

sAMAccountName	employeeNumber	distinguishedName	memberOf
AAchoo	123456789	CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=net	CN=Mobile Users,OU=Home,DC=domain,DC=net
AAchoo	123456789	CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=net	CN=REPORT_VIEWER,OU=Prod,OU=ApplicationGroups,DC=domain,DC=net
AAchoo	123456789	CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=net	CN=REPORT_VIEWER2,OU=Prod,OU=ApplicationGroups,DC=domain,DC=net
AAchoo	123456789	CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=net	CN=REPORT_ACCESS,OU=Prod,OU=ApplicationGroups,DC=domain,DC=net
AAchoo	123456789	CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=net	CN=Feed_Exclustion,OU=Prod,OU=ApplicationGroups,DC=domain,DC=net
AAchoo	123456789	CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=net	CN=National_Accounts,OU=SALES,OU=Home,DC=domain,DC=net
AAchoo	123456789	CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=net	CN=Interclass Exchanges,OU=Resource Mailboxes/Distribution Lists,OU=CUST,OU=Home,DC=domain,DC=net
AAchoo	123456789	CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=net	CN=Remote_Users,OU=APPS_GROUPS,OU=Home,DC=domain,DC=net
AAchoo	123456789	CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=net	CN=Primary SMTP Group,OU=SALES,OU=Home,DC=domain,DC=net
AAchoo	123456789	CN=Angela Achoo,OU=SALES,OU=Home,DC=domain,DC=net	CN=Corporate Card,OU=ACCT,OU=Home,DC=domain,DC=in,DC=net

References

• http://www.computerperformance.co.uk/ezine/ezine23b.htm
• http://rajnishbhatia19.blogspot.com/2008/11/active-directory-useraccountcontrol.html
• http://support.microsoft.com/kb/305144
• Active Directory Data from Extract Load and Transform (ETL) perspective Added 2011-05-23