asked on

Work around for issue retrieving description field from AD via LDAP

working on an ETL project where we need to tie information from the AD server to the ERP system to get SSO logins. I need to relate the Employee ID found in the ERP system to an Employee ID in the AD. I was trying to do this from SQL running in a SSIS package.

The problem is that the Employee ID in the AD is stored in the description attribute... i know... couldn't have been easy and stored in the EmployeeID or EmployeeNumber attribute right? I can connect to the AD and read the other attributes, but I get the "Could not convert the data value due to reasons other than sign mismatch or overflow" when trying to read the description attribute. After some research, I understand it is a common problem because LDAP reports that value as a multi-value. I found a supposed workaround on the Experts Exchange site, but that doesn't appear to work as it is also accessing via an OpenQuery (ADSI, ...... LDAP://..... query.

Just wondering if there is a work-around with SQL? or if it is possible to pull a list of the description and mail fields via .NET? if .NET, could you point me to some examples? While I do understand some C#/VB, I'm not an expert in that regard, especially when accessing AD.

Thanks

ASKER CERTIFIED SOLUTION

Vadim Rapp

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

larryh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wayne Rushing

ASKER

I want to thank Vadim and larryh for their input. Vadim, thanks for the complete script and links. larry, looking at your snipet would be helpful as to how to pick out the specific field. I greatly appreciate your help.

I actually ended up doing something similar, but slight different, so just wanted to share in case that helps others:

Public Sub Main()
	Dim vars As Variables = Nothing
	Dim adRawFile As String = Nothing

	Dim employeeID As String = Nothing
	Dim mail As String = Nothing
	Dim sn As String = Nothing
	Dim givenName As String = Nothing
	Dim initials As String = Nothing
	Dim description As String = Nothing

	'Get the variable values from the package
	Dts.VariableDispenser.LockForRead("ADRawFile")

	Dts.VariableDispenser.GetVariables(vars)

	adRawFile = CType(vars("ADRawFile").Value, String)

	vars.Unlock()

	'Get the employee information from the AD using a DirectorySearcher
	Using dirEntry As New DirectoryServices.DirectoryEntry("LDAP://TEST.com/DC=TEST,DC=com")

		Using dirSearcher As New System.DirectoryServices.DirectorySearcher(dirEntry)

			Dim results As SearchResultCollection
			Dim result As SearchResult

			If File.Exists(adRawFile) Then
				File.Delete(adRawFile)
			End If

			' filtering: only users where sn AND (EmployeeID OR description) is populated AND AD account is enabled
			dirSearcher.Filter = "(&(objectCategory=person)(objectClass=user)(mail=*)(sn=*)(|(EmployeeID=*)(description=*))(!(userAccountControl:1.2.840.113556.1.4.803:=2)))"

			dirSearcher.PropertiesToLoad.Add("EmployeeID")
			dirSearcher.PropertiesToLoad.Add("sn")
			dirSearcher.PropertiesToLoad.Add("initials")
			dirSearcher.PropertiesToLoad.Add("givenName")
			dirSearcher.PropertiesToLoad.Add("mail")
			dirSearcher.PropertiesToLoad.Add("description")

			dirSearcher.PageSize = 1000

			dirSearcher.SearchScope = SearchScope.Subtree

			results = dirSearcher.FindAll()

			Using strWriter As New StreamWriter(adRawFile)

				'strWriter.WriteLine(DateTime.Now.ToString())

				strWriter.WriteLine("EmployeeID,mail,sn,givenName,initials,description")

				For Each result In results

					employeeID = Nothing
					mail = Nothing
					sn = Nothing
					givenName = Nothing
					initials = Nothing
					description = Nothing

					employeeID = CType(result.GetDirectoryEntry().Properties.Item("EmployeeID").Value, String)
					mail = CType(result.GetDirectoryEntry().Properties.Item("mail").Value, String)
					sn = CType(result.GetDirectoryEntry().Properties.Item("sn").Value, String)
					givenName = CType(result.GetDirectoryEntry().Properties.Item("givenName").Value, String)
					initials = CType(result.GetDirectoryEntry().Properties.Item("initials").Value, String)
					description = CType(result.GetDirectoryEntry().Properties.Item("description").Value, String)

					If Not String.IsNullOrEmpty(employeeID) Then
						employeeID = employeeID.Replace(",", "").Replace(vbCr, "").Replace(vbLf, "").Trim()
					End If

					If Not String.IsNullOrEmpty(mail) Then
						mail = mail.Replace(",", "").Replace(vbCr, "").Replace(vbLf, "").Trim().ToLower
					End If

					If Not String.IsNullOrEmpty(sn) Then
						sn = sn.Replace(",", "").Replace(vbCr, "").Replace(vbLf, "").Trim().ToUpper
					End If

					If Not String.IsNullOrEmpty(givenName) Then
						givenName = givenName.Replace(",", "").Replace(vbCr, "").Replace(vbLf, "").Trim().ToUpper
					End If

					If Not String.IsNullOrEmpty(initials) Then
						initials = initials.Replace(",", "").Replace(vbCr, "").Replace(vbLf, "").Trim().ToUpper
					End If

					If Not String.IsNullOrEmpty(description) Then
						description = description.Replace(",", "").Replace(vbCr, "").Replace(vbLf, "").Trim()
					End If

					strWriter.WriteLine(employeeID + "," + mail + "," + sn + "," + givenName + "," + initials + "," + description)
				Next

				'strWriter.WriteLine(DateTime.Now.ToString())

			End Using
		End Using
	End Using

	Dts.TaskResult = ScriptResults.Success
End Sub

Open in new window

larryh

Glad you figured out a solution. It looks like you are writing to a flat file and then I imagine you will import that file into a table using a separate SSIS step. We just did the INSERT statements in the C# script directly rather than a bulk import. I can see how using a file and importing it might be faster. However, I think that most of the performance issues are in querying Active Directory rather than inserting records.