Active Directory connection from SSIS

I'm trying to query against AD to grab some data. I've done this setup and got it to work at my location but can't get it working at one of my customers.

Per information I've found via this site I did the following:

Task: from SS2005, select data elements from Active Directory and populate in ODS (using an

SS2005 Package in SSIS)

1. I created a linked server on the MS2005
   1. EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
2. I then created the following View (in the Master DB):

CREATE VIEW viewADContacts

AS

SELECT [NAME],MAIL

FROM OPENQUERY( ADSI,

'SELECT NAME, MAIL

FROM ''LDAP://#######/ DC=####,DC=###''

')

The View created w/ no errors

3. When I execute

select * from viewADContacts

I get this error:

Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".

Any thoughts on this error? Again, I did the exact same thing at my office (against my local AD) and it worked fine.

Thanks in advance

Tom

Still stuck on this - I did install ldapbrowser on the Sql Server 2005 box and was able to query the Active Directory using my windows login (which is how I connect to the sql server). I'm thinking it's something w/ the Provider - any suggestions on how to debug?

thanks

|||

I've changed directions and am trying a Script task using the example from the SS online books

The code is this:

Public Sub Main()

Dim directory As DirectoryServices.DirectorySearcher

Dim result As DirectoryServices.SearchResult

Dim email As String

email = Dts.Variables("email").Value.ToString

Try

directory = New _

DirectoryServices.DirectorySearcher("(mail=" & email & ")")

result = directory.FindOne

Dts.Variables("name").Value = _

result.Properties("name").ToString()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

Dts.Events.FireError(0, _

"Script Task Example", _

ex.Message & ControlChars.CrLf & ex.StackTrace, _

String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

My problem is I'm not getting a value for 'Name' instead when I display in a dataflow task (using dataviewer)following the script task the value of Name = 'System.DirectoryServices.ResultPropertyValueCollection'

It's seems like it is telling me its property not the value. I'm not a VB/.Net developer so I'm just guessing as to what the value means.

Any help would be appreciated.

thanks