Active Directory Data from Extract Load and Transform (ETL) perspective

Ensuring consistency of the data stored in Active Directory should be one of the top priorities in achieving the overall security of an enterprise. By consistency in this context I imply how well the organization structure of a company is represented inside Active Directory, i.e. how accurately group memberships are mapped to the business tasks assigned to the employees. This task could only be accomplished by instituting regular and vigorous data analysis procedures.

Any information analysis project starts with gaining access to the relevant data, and more importantly data in the format which lends itself to a comprehensive examination. Hence the topic of this presentation - Active Directory from the ETL process perspective.

This presentation will explore the following subjects:

  • ·         Current challenges around performing data analysis against Active Directory data
  • ·         Advantages of converting Active Directory data into relational format
  • ·         Potential advantages of leveraging Microsoft Business Intelligence tools when analyzing AD data
  • ·         Overview of SQL Server Integration Services (SSIS) and how it could be utilized for data extraction from Active Directory
  • ·         Demo which demonstrates how to build an SSIS project from scratch for the purposes of detecting permission creep conditions within Active Directory



To take full advantage of SSIS's extraction and transformation capabilities when working with AD data, I developed a custom source component for Active Directory Domain. Link below will provide you with source code, installation instructions, and pre-build dlls, should you be interested in exploring this solution further.


Get source, setup, and documentation at