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:
I am using 2008 R2 SSIS (Data Flow) and It seems that when you try to pull from a very large directory you get this error:
[ADO NET Source ] Error: The component "ADO NET Source" (1) was unable to process the data. 'ADsDSOObject' failed with no error message available, result code: -2147016669(0x80072023).
The preview of my source connection shows up correctly, but when i execute the task it fails with the above error. Any ideas?
I see a few other questions on the web with this as well. One in particular I see is to Check for the A.D. setting: DAPAdminLimits: MaxPageSize=1000
I have been trying desperately to find a means in which to extract information from active directory and dump the results to a table. I cannot retrieve more than 1,000 rows when I do. This article goes beyond anything I have tried and I believe it would work until I get to step 5 of the Control Flow example.
Any way you could finish the For Each part of the article? That would make this Gold article Platinum!
There is just not enough foundational information for this to be useful. ie: Setting up an OLE DB Connection Manager. Need a bit more detail on steps to repeat please :)
hi there, i having problem on the connection manager. it keep on show me theres an error as below
Execute SQL Task: Executing the query "select * from " ldap://x500.bund.de:389/cn=PCA-1-Verwaltung,o=PKI-1-VERWALTUNG,c=DE"" failed with the following error: "Exception from HRESULT: 0x80040E14". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
i not sure is it the server site problem or my connection manager problem because the connection manager always test succeded, help pls ><
Any query of AD will be limited to 1000 records unless you turn on paging. With ADSI and ADO you do this by assigning a value between 100 and 1000 to the PageSize property of the command object. However, paging is not supported in an SQL distributed query. You can modify the Active Directory server limit for maxPageSize, but this is not recommended.
kpchong, there are a few problems with your query. First, there should not be a leading space before "LDAP:". Second, the LDAP: moniker is case sensitive. Next, your ADsPath does not look like Active Directory. AD has "cn=" components, but not "o=" or "c=". Also, your query is quoted, but you have quotes embedded in the string. The embedded quotes should be single quote characters. Also, there should be no need to specify a server (domain controller), unless you are retrieving attributes that are not replicated. And I've never seen a need to specify a port. If your domain is bund.de, I would expect:
"select * from 'LDAP://dc=bund,dc=de'"
The filter "WHERE objectClass='user'" will retrieve both user and computer objects from AD. This is because both user and computer objects have class "user". When using ADSI and ADO, you should use "WHERE objectCategory='person' AND objectClass='user'". This is also the filter used above when using LDAP syntax, but the same holds when you use SQL syntax, because AD is LDAP compliant, not SQL, so any SQL syntax query is converted into LDAP. However, if you use a linked server and the OPENQUERY method, the OPENQUERY method converts the SQL syntax filter "WHERE objectClass='user' into the LDAP filter "(&(objectCategory=person)(objectClass=user))", at least in my tests. I think to be safe, a query for all users should include the objectCategory clause.
I'm trying to import data in an AD following the 3rd part of the article. I'm getting this error on the script destination component in my data flow :
Script component runtime error :
at System.DirectoryServices.DirectoryEntry.Bind(Boolean throwIfFail)
at System.DirectoryServices.DirectorySearcher.FindAll(Boolean findMoreThanOne)
at ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
at UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
I catched the error and it seems to be on the line :
SearchResultCollection results = deSearch.FindAll();
Do you have any idea ?