How do I use the OLE DB provider for Directory Services in a SQL Server Integration Services project?

Question

I can setup a connection manager that uses the Directory Services OLE provider and points to one of our domain controllers. Then, in the Data Flow area, I create a OLE DB Source and set the Data Access Mode to "SQL Command".

I then use this query to pull data from Active Directory:

Select employeeid, sAMaccountName
From 'LDAP://MyCompany.com'
Where objectClass = 'user'
  and objectClass = 'Person'
  and objectClass <> 'Computer'

If I parse the query, it says that it parses correctly. If I open the Query Builder, it gives me this error.

Error in FROM clause: near 'WHERE'. Unable to parse query text.

I then click OK and it opens the Query Builder where I can successfully run the query and get results from AD. However, when I try to click OK to apply the changes or go into the Columns view, I get this error:

Error at Data Flow Task [OLE DB Source 1 [941]]: An OLE DB error has occurred. Error code: 0x80040E21.

ADDITIONAL INFORMATION: Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

I have also tried setting up the DB source using the "SQL command from variable" Access Mode. I set up a string variable with the value being the query... but that just gives me the same error.

Because the error is so generic, I can't seem to find a description of what is actually wrong.

Does anyone know how to successfully use the OLE DB Provider for Microsoft Direcotry Services? And/or does anyone know a better way to do this inside of SSIS?

Answer 1

Assuming you named the linked server (your Active Directory OLE DB provider) "ADSI", here's essentially what you'd need:

SELECT samAccountName,
       employeeID
FROM OPENQUERY(ADSI,
  'SELECT samAccountName, employeeID
   FROM ''LDAP://MyCompany.com''
   WHERE objectClass=''Person''
   AND objectClass = ''User''
   AND NOT objectClass = ''Computer''')