Set up an OLE DB connection manager using the Native OLE DB/OLE DB Provider for Microsoft Directory Services pointing to the domain controller against which you will issue your queries.
NOTE: When you click the “Test” button to test your connection, it may indicate that the connection was successful even though Directory Services is not running on the server you are pointed to. However;
in order for your package execution to succeed, the connection manager must be pointing to a computer where directory services is running.
NOTE: Although the option exists to set the user name and password rather than using “NT Integrated Security”, connections will fail for Active directory queries if “NT Integrated Security” is not used.
NOTE: Some references instruct you to set the ADSI flag to 1 to make this work. However; I have been able to make this work with the default setting of -2147483648. If the default does not return results,
try setting the ADSI flag to 1 in the “All” settings page.
Figure 1: An OLE DB Connection Manager for Microsoft Directory Services set up to point to a domain controller named home-server
Create a variable of type “Object” scoped at the package level.
Set up an Execute SQL control flow task using this connection manager. Set the Result Set type to “Full” and set the result set to be stored in the Object variable you created in step 2.
For the SQL Query in the Execute SQL task, use your LDAP SQL query. An example to retrieve the names of all user accounts from Active Directory is:
SELECT cn FROM 'LDAP://my-server' WHERE objectClass='User'
Note that the single quotes are required around the object you are selecting “FROM.” In addition, you can query from a server name as is done in this example, or you can specify the DCs are are specified in directory services.
Running a “SELECT *” instead of the “SELECT cn” will return results in a single column (ADsPath) from which you can determine what DC values are valid in your domain. An example of the query using DC is:
SELECT cn FROM 'LDAP://home-server/DC=steve,DC=homeoffice' WHERE objectClass='User'
NOTE: Directory services can also use the ADSI LDAP query syntax. For example, in place of the select query above, a query to get just user names using the ADSI LDAP syntax would be:
Figure 2 An Execute SQL task using the connection manager pointing to Directory Services. The SQL Query is entered directly in this example, and the ResultSet is set to “Full.” An ADSI LDAP query can be used
in place of the SQL query.
Once the results have been retrieved into a variable in the control flow, you can use the results the same way you would results from any other query type. As an example, you can use this with
a Foreach loop with an ADO Enumerator to process the results a row at a time. For more information on using the Foreach Loop with an ADO Enumerator, see
Create an ADO.Net connection manager. Under the “Provider” drop down, expand the “.NET Providers for OleDB” folder and select the “OLE DB Provider for Microsoft Directory Services” provider. For “Server or file name” enter the name of your domain
controller, and ensure the “Use Windows NT Security” radio button is selected.
Figure 4 The ADO.NET connection manager configured to point to Directory Services on the home-server domain controller.
Drag a “Datareader Source” (SQL 2005) or an ADO .NET Source (SQL 2008) onto the data flow design surface. Configure the source to use the ADO.NET connection manager you just configured pointing to directory services.
For SQL 2008, set the Data Access Mode to “SQL Command” and put your LDAP SQL query, or ADSI LDAP query in the “SQL Command Text” box. For SQL 2005, select the “Component Properties” tab, and enter the LDAP SQL query in the SQLCommand box.
Figure 5 The Component Properties tab of the Advanced Editor for a SQL 2005 SSIS Datareader Source. The SQL query is the Directory Services query to be executed against
the LDAP server. In this illustration, it is a SQL query, but an ADSI LDAP query can be used in place of the SQL query.
Once the source is set up, you can pull the Active Directory data into SSIS to process through the data flow like from any other source.
NOTE: The data output from the query may be of type DT_NTEXT which is a streaming data type. This may not be compatible with all destinations. You may need to add a “Data Conversion” transform to your data
flow to convert this data type into DT_WSTR or other appropriate data type to be able to output to destinations such as text files.
Choose to make the appropriate rows available in your script component destination.
Design your script.
Add a reference to System.DirectoryServices.
For Visual Basic, add an “Imports System.DirectoryServices” line in your imports section. For C#, add a “Using System.DirectoryServices to the Using section of your script.
Use the following snips as an
example for VB and for C# for creating ActiveDirectory accounts and configuring them. Modify the examples for your needs as this may not work exactly with your data and component configurations.
View the following for a list of attributes that can be configured in LDAP Active Director account: