October 17, 2009 at 9:40 am
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!
October 19, 2009 at 8:20 am
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
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
October 20, 2009 at 4:30 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy