Active Directory Source Component

Adding SSIS Components to Business Intelligence Development Studio's Toolbox

SSIS Integration Toolkit for Directory Services includes two data flow components for use with Active Directory. They need to be added to the SSIS toolbox before you can use them in a SSIS data flow task.

Note: If you are using SQL Server 2012 development environment, you should not need to do this, as SQL Server 2012 automatically lists all available pipeline components by scanning the system. 

To add the data flow components, create a new data flow task if you do not have one yet and switch to the SSIS data flow page. Right-click on the toolbox area to bring up the context menu, where you can select the "Choose Items..." option as shown below.

You will be presented with a window called "Choose Toolbox Items". Switch to "SSIS Data Flow Items" tab, and select Active Directory Destination and Active Directory Source components from the list. 

Both data flow components should now appear in your SSIS Toolbox, where you can drag and drop any of them to the design surface of your SSIS data flow task. 

Using the Active Directory Source Component

The Active Directory Source Component is an SSIS data flow pipeline component that can be used to read / retrieve data from Microsoft Azure Active Directory and Microsoft Active Directory.

The component includes the following two pages to configure how you want to read data from Active Directory.

• General
• Columns

General Page

The General page of the Active Directory Source Component allows you to specify the general settings of the component. 

Select the connection manager and source object

Connection Manager

The Active Directory source component requires an Active Directory connection in order to connect with Active Directory. The Connection Manager drop-down will show a list of all Active Directory connection managers that have been created in the current SSIS package or project.

Source Object

Based on the Active Directory Connection Manager chosen, the Source Object drop-down lists all of the currently supported objects which you can read from. The available objects include:

• User
• Group
• Computer (On-Premises only)
  
• Organizational Unit (On-Premises only)

Note: you need to have least User.ReadBasic.All permission to read Azure users and a minimum permission of Group.Read.All is required to retrieve Azure Groups.

Directory Services Settings

Container Path (On-Premises only)

The Container Path option lets you specify the Directory path that you want to narrow your search scope to. You can specify, for example, the organization unit.

Paging Size (Azure only)

The Paging Size option allows you to specify how many records you want to retrieve each time. The default value is 100.

Search Criteria

You may use the Search Criteria to filter particular records according to your requirements. The Active Directory Search Criteria filter follows the Lighweight Directory Access Protocol (LDAP) filtering format.

• All expressions should contain parentheses. Ex. (objectCategory=person)
• You may use relational operators to form your expressions (ex. '<', '<=', '=', '>=', '>')
• You may use compound expressions within your filtering such as '&' and '|'

For more information, you may refer to the Directory Searcher Filter Property page.

Expression fx Icon

Click the blue fx icon to launch SSIS Expression Editor to enable dynamic update of the property at run time.

Generate Documentation Icon

Click the Generate Documentation icon to generate a Word document which describes the component's metadata including relevant mapping, and so on.

Columns Page

The Columns page of the Active Directory Source Component shows you all available attributes from the object that you specified on the General page.

Source Object & Container Path Combinations (On-Premises only)

Reading from Custom Attributes (On-Premises only)

It is possible to read data from custom attributes in Active Directory. By default, custom attributes cannot be detected in the Source component. In version 2.x and above, in the Columns page of the AD Source Component, there is an 'Add' button, to manually add the custom field. If you select this button, you can add custom fields by entering the following information:

Field Name

Specify the Field Name of the custom field that you would like to read from.

Data Type

Select the Data Type drop down of the field type.

Length

Choose the Length of the Data Type for your custom field.

With previous versions, 1.1 and 1.2, if you open the Advanced Editor for the Active Directory Source component, and go to the `Input And Output Properties` tab, you can add columns for your custom attributes. The `Name` field can be used as the display name for your custom attribute, and most importantly of all, the `Description` field needs to be populated with the actual name of your custom attribute. The Active Directory Source Component reads the value saved in the Description field to determine what to query your Active Directory with. Also note, the column should be added to the `Output Columns` list.

Reading from Array Attributes

It is possible to read from attributes that contain multiple values and are stored as array objects. For example, the `Telephone` attribute can store multiple entries. When attempting to read from array attributes, these values will be concatenated into delimited text using a semicolon (;) as delimiter. If your attribute value contains the semicolon delimiter character, your attribute value will be escaped with double quotes (").