Extracting data from LDIF file

  • Has anyone worked with importing data from LDIF (LDAP Data Interchange Format) file from Active Directory in to SQL Server? If so can you please provide some input on how best can this be achieved? Especially how to go about handling delta data which will be coming in from Active Directory. Any ideas will be greatly appreciated.

    Thanks in advance!

  • If you have access to SQL Server Magazine, they just did a feature on this very thing in the September issue:

    http://www.sqlmag.com/Articles/ArticleID/102200/102200.html?Ad=1

    hth,

    Tim

  • Yes I've done this with a script source in the dataflow which uses the system.directoryservices facilities.

    Code below:

    Change your server, domain, DC, login and password of course. Add other properties as needed remembering some are string array objects, not just strings.

    If your references don't seem to work and you get lots of red squiggle underlines go into the code then do Project, Add reference and add the system.directoryservices and anything else it thinks is missing. Took me ages to find that twist!

    Option Strict Off

    Imports System

    Imports System.Data

    Imports System.Math

    Imports System.DirectoryServices

    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    Public Class ScriptMain

    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()

    '

    ' Add rows by calling AddRow method on member variable called "<Output Name>Buffer"

    ' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"

    '

    Dim DE As New DirectoryEntry("LDAP://server/OU=User Accounts,DC=x,DC=y,DC=z", "login", "password", AuthenticationTypes.Secure)

    Dim searcher As New DirectorySearcher

    Dim foundUser As SearchResult

    Dim uservar, showvar As String

    searcher.SearchRoot = DE

    searcher.SearchScope = SearchScope.Subtree

    searcher.PageSize = 1000

    searcher.SizeLimit = 1000

    searcher.Filter = "(&(objectClass=user)(objectCategory=person)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))"

    'searcher.Filter = "(&(objectClass=user)(objectCategory=person))"

    For Each foundUser In searcher.FindAll()

    Dim user As New DirectoryEntry(foundUser.Path(), "login", "password", AuthenticationTypes.Secure)

    Try

    uservar = "N/K"

    OutputLineBuffer.AddRow()

    uservar = GetProp(user, "cn")

    OutputLineBuffer.dn = uservar

    OutputLineBuffer.distinguishedName = GetProp(user, "distinguishedName")

    OutputLineBuffer.sAMAccountName = GetProp(user, "sAMAccountName")

    OutputLineBuffer.cn = GetProp(user, "cn")

    OutputLineBuffer.Mail = GetProp(user, "mail")

    OutputLineBuffer.sn = GetProp(user, "sn")

    OutputLineBuffer.givenName = GetProp(user, "givenName")

    OutputLineBuffer.employeeID = GetProp(user, "employeeID")

    OutputLineBuffer.whenCreated = GetProp(user, "whenCreated")

    OutputLineBuffer.whenChanged = GetProp(user, "whenChanged")

    Catch ex As Exception

    ErrorOutputBuffer.AddRow()

    ErrorOutputBuffer.dn = uservar

    ErrorOutputBuffer.message = ex.Message

    ErrorOutputBuffer.trace = ex.StackTrace

    End Try

    user.Close()

    user = Nothing

    Next

    OutputLineBuffer.SetEndOfRowset()

    ErrorOutputBuffer.SetEndOfRowset()

    End Sub

    Private Function GetProp(ByRef u As DirectoryEntry, ByVal fname As String) As String

    Dim resvar As String

    Try

    resvar = u.Properties(fname).Value.ToString

    Catch ex As Exception

    resvar = ""

    End Try

    Return resvar

    End Function

    End Class

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply