Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1509
  • Last Modified:

How to access AD in a SSIS script Task

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
eduf
Asked:
eduf
  • 16
  • 11
1 Solution
 
PedroCGDCommented:
Add the code to a script task in the controlflow.
You will need some extra references for ADODB.
Regards
0
 
HoggZillaCommented:
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
 
HoggZillaCommented:
You should also consider going to an OLEDB connection instead of ADO.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1785799&SiteId=1
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HoggZillaCommented:
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
 
edufAuthor Commented:
OK.
But where I can open the "Active Directory Provider" ?
And set the command string?
0
 
HoggZillaCommented:
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
 
HoggZillaCommented:
This is where I am now, good point of reference.
http://www.ondotnet.com/pub/a/dotnet/2003/07/28/activedir.html
0
 
edufAuthor Commented:
OK !
It seems that I don´t have it.
0
 
edufAuthor Commented:
OK !
I Have it.
But when a trie to use the code:
System.DirectoryServices
it get error.
0
 
HoggZillaCommented:
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
 
edufAuthor Commented:
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
 
HoggZillaCommented:
Very nice! That was fast, you picked that up like nothing. Is there a specific part you still need help with?
0
 
edufAuthor Commented:
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
 
HoggZillaCommented:
OK, sorry, What error are your receiving. Also, please check.
Meanwhile I will post some array code I have.
0
 
edufAuthor Commented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
edufAuthor Commented:
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
 
HoggZillaCommented:
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
 
edufAuthor Commented:
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
 
HoggZillaCommented:
Reference for above, apologies please.
http://www.codeproject.com/KB/IP/LDAP_Using_VBnet.aspx
0
 
edufAuthor Commented:
I double ckecked, but the error persist.
0
 
HoggZillaCommented:
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
 
edufAuthor Commented:
No !
I´m getting the same error.
Could the select been made in a wrong way?
0
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
HoggZillaCommented:
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
 
edufAuthor Commented:
Cool HoggZilla.
That code was what I need.
Thanx
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 16
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now