Solved

How to access AD in a SSIS script Task

Posted on 2008-10-29
28
1,290 Views
Last Modified: 2013-11-10
Hi,
I had already connect to AD using the script:
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Open "Active Directory Provider"
objCommand.CommandText = "SELECT sAMAccountName FROM 'LDAP://DC=gvt,DC=net,DC=br' " & _
                                              "WHERE objectCategory='Person' " & _
                                              "And distinguishedName = '"+strMember +"'"
Set objRecordSet = objCommand.Execute

But I need to use it in a Script task from SSIS.
Any ideas?

Thanks
0
Comment
Question by:eduf
  • 16
  • 11
28 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22832991
Add the code to a script task in the controlflow.
You will need some extra references for ADODB.
Regards
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22833045
Here you go. This explains it all. I have used this many times.
http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx
 
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22833126
You should also consider going to an OLEDB connection instead of ADO.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1785799&SiteId=1
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22833145
Here is another option where you create an ADO recordset and loop through the results.
http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx
0
 

Author Comment

by:eduf
ID: 22833207
OK.
But where I can open the "Active Directory Provider" ?
And set the command string?
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22833269
Try using the DirectoryServices reference. I will try to help. Here is the start, make sure you have a reference.
In your script task,
Imports System.DirectoryServices
 

add-reference.bmp
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22833298
This is where I am now, good point of reference.
http://www.ondotnet.com/pub/a/dotnet/2003/07/28/activedir.html
0
 

Author Comment

by:eduf
ID: 22833316
OK !
It seems that I don´t have it.
0
 

Author Comment

by:eduf
ID: 22833363
OK !
I Have it.
But when a trie to use the code:
System.DirectoryServices
it get error.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22833416
I am working on some code for you, but here is the start.
Start here. You will probably move faster than me now. Make sure you get the Imports System.DirectoryServices

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.DirectoryServices
 
 

Public Class ScriptMain
 

	' The execution engine calls this method when the task executes.

	' To access the object model, use the Dts object. Connections, variables, events,

	' and logging features are available as static members of the Dts class.

	' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

	' 

	' To open Code and Text Editor Help, press F1.

	' To open Object Browser, press Ctrl+Alt+J.
 

	Public Sub Main()

		'

		' Add your code here

        '

        Dim Directory As DirectoryServices.DirectorySearcher
 

        Dim Result As DirectoryServices.SearchResult
 

        Dim AMAccountName As String
 

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

Open in new window

0
 

Author Comment

by:eduf
ID: 22833536
Ok ! I think it worked. But I need to set the value into a ArrayList.
That´s my code

For Each strUser In ArrUserName

    Dim objCommand As DirectoryEntry = New DirectoryEntry("SELECT Name, member FROM 'LDAP://DC=gvt,DC=net,DC=br' " & _

                                                          "WHERE objectCategory='group' " & _

                                                          "And DisplayName = '" + StrUser + "'")

    Dim objDirectorySearcher As DirectorySearcher = New DirectorySearcher(objCommand)

    Dim objRecordsets As SearchResultCollection

    Dim objRecordset As SearchResult
 

    For Each objRecordset In objRecordsets

        arrMembers.Add(objRecordset.GetDirectoryEntry().Properties("member").Value)

    Next

Next

Open in new window

0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22833564
Very nice! That was fast, you picked that up like nothing. Is there a specific part you still need help with?
0
 

Author Comment

by:eduf
ID: 22833623
Yes, the last part !!
I need to loop the member field in a array ! But it get´s an error.

    For Each objRecordset In objRecordsets

        arrMembers.Add(objRecordset.GetDirectoryEntry().Properties("member").Value)

    Next

Open in new window

0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22833725
OK, sorry, What error are your receiving. Also, please check.
Meanwhile I will post some array code I have.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:eduf
ID: 22833770
Ok.
I populated a Array (ArrUserName)
When I try to read in the for each, it shows only the first letter.
Ex: If I have the word "DOG", it shows "D"
While (rs.Read())

    ArrUserName.Add(rs("UserName").ToString())

End While
 

For Each StrUser In ArrUserName

    MsgBox(StrUser)

Next

Open in new window

0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22833833
Are you using an ArrayList?
It looks like you have a very good handle on the code, you should post the entire Script Task code here.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22833848
Here is my code example I have saved from ? I can remember. :-)

Get Item FileList
As alluded to above, the Get Item FileList script task will perform the task of retrieving the file list from the directory specified in the FileDir global variable. It will then populate the FileList object global variable with the full list of files. By initially setting the ScriptTask of ReadOnlyVariables with FileDir and ReadWriteVariables with FileList, the Visual Basic .NET script code-below will perform the task of updating the FileList global variable.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Collections

Imports System.IO 
 

Public Class ScriptMain 
 

Private listForEnumerator As ArrayList 
 

   Public Sub Main() 
 

      ' Declarations

      Dim directoryName As String

      Dim localFiles() As String

      Dim localFile As String

      Dim fileFilter As String 
 

      ' Looking for files with the naming convention of "MyFiles*.csv"

      fileFilter = "MyFiles*.csv" 
 

      ' Create an array list that will contain the files.

      listForEnumerator = New ArrayList() 
 

      ' Try/Catch

      Try

         ' Obtain directory name that contains the files from the 

         ' global variable "FileDir"

         directoryName = Dts.Variables.Item("FileDir").Value.ToString() 
 

         ' Create the array of local files which allows you to filter for 

         ' file names of only "MyFiles*.csv" as specified in the above

         ' fileFilter

         localFiles = Directory.GetFiles(directoryName, fileFilter) 
 

         ' Additional logic can be added in the for loop below 

         ' e.g. based on ModifiedDateTime, additional name filters, etc.

         For Each localFile In localFiles

            listForEnumerator.Add(localFile)

         Next 
 

      Catch 
 

      End Try 
 

   ' Transfer the array of files to the "FileList" object variable

   Dts.Variables("FileList").Value = listForEnumerator 
 

   ' Success

   Dts.TaskResult = Dts.Results.Success

End Sub 
 

End Class 

Open in new window

0
 

Author Comment

by:eduf
ID: 22833947
I think the problem is because I still can´t get the value from the query !
Tried a code that I found in internet, but it keep in error.

That´s the correct way to get the value?
objRecordset.GetDirectoryEntry().Properties("member").Value
While X < ArrUserName.Count

    Dim objCommand As DirectoryEntry = New DirectoryEntry("SELECT Name, member FROM 'LDAP://DC=gvt,DC=net,DC=br' " & _

                                                          "WHERE objectCategory='group' " & _

                                                          "And DisplayName = '" + ArrUserName(X).ToString() + "'")

    Dim objDirectorySearcher As DirectorySearcher = New DirectorySearcher(objCommand)

    Dim objRecordsets As SearchResultCollection

    Dim objRecordset As SearchResult
 

    objDirectorySearcher.PropertiesToLoad.Add("member")

    MsgBox(objRecordset.GetDirectoryEntry().Properties("member").Value)

    

    X = X + 1

End While

Open in new window

0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22834170
Compare to this code, I do not see a Find?
objRecordset = objDirectorySearcher.FindAll

Public Function GetAllUsers(ByVal ldapServerName As String) As Hashtable 
 

 'To retrieve list of all  LDAP users 
 

 'This function returns HashTable

 _ldapServerName = ldapServerName
 

 Dim sServerName As String = "mail"
 

 Dim oRoot As DirectoryEntry = New DirectoryEntry("LDAP://" & ldapServerName & _

       "/ou=People,dc=mydomainname,dc=com")

 

 Dim oSearcher As DirectorySearcher = New DirectorySearcher(oRoot)

 Dim oResults As SearchResultCollection

 Dim oResult As SearchResult

 Dim RetArray As New Hashtable()
 

 Try
 

  oSearcher.PropertiesToLoad.Add("uid")

  oSearcher.PropertiesToLoad.Add("givenname")

  oSearcher.PropertiesToLoad.Add("cn")

  oResults = oSearcher.FindAll     
 

  For Each oResult In oResults
 

   If Not oResult.GetDirectoryEntry().Properties("cn").Value = "" Then

    RetArray.Add( oResult.GetDirectoryEntry().Properties("uid").Value, _

      oResult.GetDirectoryEntry().Properties("cn").Value)

   End If
 

  Next
 

 Catch e As Exception
 

  'MsgBox("Error is " & e.Message)

  Return RetArray
 

 End Try
 

 Return RetArray

  

 End Function

Open in new window

0
 

Author Comment

by:eduf
ID: 22834205
When I insert the line, i get the error:
objRecordsets = objDirectorySearcher.FindAll

Unknown error (0x80005000)

   at System.DirectoryServices.DirectoryEntry.Bind(Boolean throwIfFail)
   at System.DirectoryServices.DirectoryEntry.Bind()
   at System.DirectoryServices.DirectoryEntry.get_AdsObject()
   at System.DirectoryServices.DirectorySearcher.FindAll(Boolean findMoreThanOne)
   at System.DirectoryServices.DirectorySearcher.FindAll()
   at ScriptTask_73e8dddaae714e6d9e7ec0b14fbbcbe9.ScriptMain.Main()
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22834217
Reference for above, apologies please.
http://www.codeproject.com/KB/IP/LDAP_Using_VBnet.aspx
0
 

Author Comment

by:eduf
ID: 22834309
I double ckecked, but the error persist.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22834903
Sorry, I am just not much help with the Array and getting the values. Here is some code I put togethe based on your objCommand. It might help.
    Public Sub Main()
 

        'I am assuming you have the values for strUsername

        Dim strUserName As String
 

        Dim objCommand As DirectoryEntry = New DirectoryEntry("SELECT Name, member FROM 'LDAP://DC=gvt,DC=net,DC=br' " & _

                                                              "WHERE objectCategory='group' " & _

                                                              "And DisplayName = '" + strUserName.ToString() + "'")

        Dim oSearcher As DirectorySearcher = New DirectorySearcher(objCommand)

        Dim oResults As SearchResultCollection

        Dim oResult As SearchResult

        Dim RetArray As Collections.ArrayList
 
 

        oSearcher.PropertiesToLoad.Add("member")

        oResults = oSearcher.FindAll
 

        For Each oResult In oResults
 

            RetArray.Add(oResult.GetDirectoryEntry().Properties("member").Value)

            MsgBox(oResult.GetDirectoryEntry().Properties("member").Value.ToString)
 

        Next
 

        Dts.TaskResult = Dts.Results.Success

    End Sub

Open in new window

0
 

Author Comment

by:eduf
ID: 22835404
No !
I´m getting the same error.
Could the select been made in a wrong way?
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22835627
Possibly the select statement is incorrect. I do not pretend to be an expert on accessing the AD through Directory Services. Have you tried starting over, a new script breaking out into the simplest of task. Just search the AD and produce a MsgBox with returned values? How about this site, could you try to duplicate this approach - to some degree?
We know the DirectoryServices will allow you access to the information, let's focus on that piece by itself and see if we can knock that out.
http://www.codeproject.com/KB/IP/LDAP_Using_VBnet.asp
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 500 total points
ID: 22835972
Can you execute the following script. My permissions won't allow. I get this error.
        Dim objSearch As New DirectorySearcher()

        objSearch.SearchRoot = New DirectoryEntry("LDAP://DC=gvt,DC=net,DC=br")

        objSearch.Filter = "(&(objectclass=user)(objectcategory=person))"

        objSearch.SearchScope = SearchScope.Subtree

        objSearch.PropertiesToLoad.Add("cn")
 

        Dim colQueryResults As SearchResultCollection

        colQueryResults = objSearch.FindAll()
 

        Dim objResult As SearchResult

        For Each objResult In colQueryResults

            Console.WriteLine(objResult.Properties("cn")(0))

        Next

Open in new window

runtime-error.bmp
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22848868
Did you figure this out? Beyond the use of DirectoryServices I don't think you can get much more help from me. I certainly understand if you want to cancel this question and open a new one, probably in another zone - not SSIS. Sorry I could not be of more help. I am afraid there are so many responses related to this question that no new Experts will pick it up. Good luck!
Steve
0
 

Author Comment

by:eduf
ID: 22849681
Cool HoggZilla.
That code was what I need.
Thanx
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now