Active Directory connection from SSIS

Hot Topic

Dynamic Columns
What are user instances?
SSIS Connection to Oracle view
Assembly ReportingServicesWebServer problem
Database Mirroring webcast on Friday April 28.
Using another app to store reports
Force to Bottom of page
wmi event watcher task
VS.NET 2005 and CLR stored procedure permission sets.
Information for Column-Level Merge Replication

SQL Server

database engine giving problems
timing out on downloading snapshot ?
SSIS Performance 32 Bit V 64 Bit
"Windows can not open this file: splash.hta" while trying to install SQL Server STD2005 through EN_SQL_2005_STD_X86_DVD.ISO fil
Develop multiusers applications with SQL Server Express
why doesnt this work?
SQL 2005 Standard edition fails during installation
Problems saving edited Functions in SQL server database using Visual Studio .Net 2003
Cannot connect to SQL Server 2005 June CTP
XML Meta Data suggestions

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

  1. 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


Answer this question

Active Directory connection from SSIS

  • Queen

    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



  • bcristian

    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



  • Active Directory connection from SSIS
Answer this question