query AD in SSIS

Question

query AD in SSIS

sqlfriend

SSC Guru

Points: 52769
More actions
September 4, 2012 at 5:00 pm

#263810

I would like to use SSIS get data from AD to a SQL table.
I setup the ado.net conneciton manager and in the source connection, I tried to use command like below:
SELECT title, displayname FROM 'LDAP://my-server' WHERE objectCategory='User'
I keep getting the error,
Errormessage: ADsDSOObject failed with no error message available.
If I add a specific another criterior to pull a specific user :
and employeeid='HR2002222' it will pull the only record and without any error,
what could be wrong, thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

I ran into a problem where the LDAP query would only return 1000 rows and then error out. There's an AD setting to increase that but I had to break my query up with criteria that brought back less than 1000 rows at a time and execute it as many times as I had to in order to get all the rows.

http://blogs.technet.com/b/qzaidi/archive/2010/09/02/override-the-hardcoded-ldap-query-limits-introduced-in-windows-server-2008-and-windows-server-2008-r2.aspx

And then again, I might be wrong ...
David Webb

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

I think you are right, if I give a criterior that limits it to a small dataset, there is no error.

How do you make the query to pull only a small number of rows and does it multiple times in this case?

Thanks,

sqlfriend SSC Guru Points: 52769 More actions · Answer 3

Any other recommendations to pull more than 1000 rows.

I guess maybe change AD server setting from 1000 to more is not a recommended one?

Thanks

David Webb-CDS SSCoach Points: 17398 More actions · Answer 4

Changing the settings would be great. My experience is that the AD sysadmins don't usually like to do that.

In my case, I used a filter on a column in our AD called 'department' like so:

''LDAP://xx-dc-x01:xxx/DC=mydomain,DC=adomain,DC=COM''

WHERE objectClass = ''User'' and department = ''0*''

So I had 10 queries, each picking out department numbers that started with the numbers 0-9. It worked in my case because everyone I cared to retrieve had a department number as a part of their AD entry. Also note that the pattern mathing syntax is different from the normal tsql 'like' syntax.

And then again, I might be wrong ...
David Webb

P Jones SSChampion Points: 12353 More actions · Answer 5

Using connection managers etc like that just didn't work for us so I successfully use a script source task to read AD data using Imports System.DirectoryServices

and along these lines:

Try

Dim dEntry As New DirectoryEntry("LDAP://OU=User Accounts,DC=mydomain,DC=mine,DC=LOCAL", "domainaccount", "password", AuthenticationTypes.Secure)

Dim dSearcher As New DirectorySearcher(dEntry)

Dim dResult As SearchResult

dSearcher.Filter = "(&(objectCategory=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))"

dSearcher.PageSize = 5

dSearcher.SearchScope = SearchScope.Subtree

dSearcher.ReferralChasing = ReferralChasingOption.All

For Each dResult In dSearcher.FindAll()

dUser = New DirectoryEntry(dResult.Path)

Try

If (dUser.Properties.Contains("proxyAddresses")) Then

addresses = dUser.Properties("proxyAddresses").Value

For Each address In addresses

If (address.Contains("X400:")) Then

x400 = address

End If

If (address.Contains("SMTP:")) Then

smtp = address

End If

Try

telno = GetValue(dUser.Properties("telephoneNumber").Value)

Catch ex1 As NullReferenceException

telno = ""

End Try

--------- etc etc

Catch ex As Exception

---- etc etc

Finally

dUser.Dispose()

End Try

Investigate the active directory properties you are interested in - some are best described as object arrays rather than just data fields.

We successfully process over 3000 records at a time using this method.