Trying To Query 'Active Directory' In An SSIS Package.

Hi All

I am trying to run a query against our 'Active Directory' and I keep getting errors. I have done the following:

1. Set up Linked Server (using the OLE DB For Directory Services) and also set the login information:


EXEC sp_addlinkedsrvlogin 'ADSI', False, 'SQL_Usr','DDEMOJillSmith', 'PassWord'

2. Created a connection in SSIS
3. In My SSIS Package I have created a dataflow select the AD connection 'ADSI'.
4. Tried to run the following query:

SELECT samaccountname, adspath, sapUsername, mail, info, costCenter, pager, mobile, facsimileTelephoneNumber, telephoneNumber, company,
department, title, sn, givenName, name, employeeNumber FROM
''LDAP://OU=Main_Users, DC=DemoAD,DC=com'' WHERE objectCategory = ''Person'' AND objectClass= ''user''

And it fails - I can get this working in SQL 2000 DTS by using an Open Query statement!

The Error message I get when trying to test the query is:

Error at Export From AD [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00000000.

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


I have event tried the working OPEN QUERY in SSMS and I get the following:


Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "SELECT samaccountname, adspath, sapUsername, mail, info, costCenter, pager, mobile, facsimileTelephoneNumber, telephoneNumber, company,

department, title, sn, givenName, name, employeeNumber FROM

'LDAP://OU=DEMO_Users, DC=DEMO,DC=com' WHERE objectCategory = 'Person' AND objectClass= 'user'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

Am I missing something?

Thanks Gopher