Solved

How to access AD in a SSIS script Task

Posted on 2008-10-29
28
1,378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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
 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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