qube09
asked on
Pull User Info from Active Directory using .net for SSIS package
I am attempting to extract selected info from active directory for all users as a portion of an SSIS package. Initially I began with sql but ran into the pagesize limit (1000). After some searching I leaned that .net may be more appropriate due to its handing of pagesize. I really have little experience with .net but thought that perhaps it would be a decent learning experience.
The end goal is to produce a table containing domain(supplied by me in code), samaccountname,displayname ,distingui shedname,l astloginti mestamp,de partment,d escription .
I believe that I will be able to manipulate lastlogin to be presented in a more usable format. I have been led to believe that description may be difficult to obtainparticularly for a newbie.
At any rate, I have located some code snippets that can produce all samaccountnames but I can not use it to obtain any of the other fields. I have commented out or removed some of it for brevity's sake. I know that using a linked server that an openquery will produce all fields except description as nvarchar. Yet in .net the compiler seems to expect department for instance as cint and when implemented yields a 0. (Granted my sql pull of 1000 records does produce nulls but cint?)
This has been a most confusing experience.
The end goal is to produce a table containing domain(supplied by me in code), samaccountname,displayname
I believe that I will be able to manipulate lastlogin to be presented in a more usable format. I have been led to believe that description may be difficult to obtainparticularly for a newbie.
At any rate, I have located some code snippets that can produce all samaccountnames but I can not use it to obtain any of the other fields. I have commented out or removed some of it for brevity's sake. I know that using a linked server that an openquery will produce all fields except description as nvarchar. Yet in .net the compiler seems to expect department for instance as cint and when implemented yields a 0. (Granted my sql pull of 1000 records does produce nulls but cint?)
This has been a most confusing experience.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.DirectoryServices
Imports System.Collections
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub CreateNewOutputRows()
Dim domain As String = "xyzdomain"
Dim searchRoot As New DirectoryEntry("LDAP://dc=xyz,dc=com", Nothing, Nothing, AuthenticationTypes.Secure)
Dim dirSearch As New DirectorySearcher(searchRoot)
Dim props As ResultPropertyCollection
Dim values As ResultPropertyValueCollection
Dim key As String
Dim accountName As String
Dim department As String
Dim displayname As String
Dim distinguishedName As String
Dim lastlogintimestamp As String
Dim description As String
dirSearch.SearchScope = SearchScope.Subtree
dirSearch.PropertiesToLoad.Add("samaccountname")
'dirSearch.PropertiesToLoad.Add("displayname")
'dirSearch.PropertiesToLoad.Add("department")
'dirSearch.PropertiesToLoad.Add("distinguishedname")
' dirSearch.PropertiesToLoad.Add("lastlogintimestamp")
'dirSearch.PropertiesToLoad.Add("description")
dirSearch.Filter = "(objectclass=user)"
dirSearch.PageSize = 1000
Using searchRoot
Using results As SearchResultCollection = dirSearch.FindAll()
For Each result As SearchResult In results
props = result.Properties
For Each entry As DictionaryEntry In props
key = CType(entry.Key, String)
If key = "samaccountname" Then
values = CType(entry.Value, ResultPropertyValueCollection)
accountName = CType(values.Item(0), String)
If accountName.EndsWith("$") = False Then
OutputBuffer.AddRow()
OutputBuffer.AccountName = accountName
OutputBuffer.Domain = domain
End If
End If
Next
Next
End Using
End Using
End Sub
End Class
ASKER
no that completely defeats the purpose
Not really. It doesn't defeat the purpose at all -- dont' reinvent what others have already done.
but if you must... follow in the footsteps of those that have been successful:
http://agilebi.com/cs/blogs/jwelch/archive/2007/04/06/retrieving-information-from-active-directory-with-ssis.aspx
I wouldn't do it the way you're attempting.
but if you must... follow in the footsteps of those that have been successful:
http://agilebi.com/cs/blogs/jwelch/archive/2007/04/06/retrieving-information-from-active-directory-with-ssis.aspx
I wouldn't do it the way you're attempting.
ASKER
actually that solution is unneccessary since as I stated in the question a simple openquery will also pull 1000 rows using a linked server.the issue is going beyond 1000 rows which appears to require a higher level language. there some hokey solutions using alpha characters etc but they wind up being limited so unfortunately your 'solution' is not helpful.
Reinvent? Perhaps but can not buy a product.
And why not do it via .net? I can pull one column correctly for all records just can't figure out how to pull multiple columns. For instance I can easily pull samaccountname but not it and anything else. I am simply not advanced enough in .net. The description field also appears to be a problem.
Reinvent? Perhaps but can not buy a product.
And why not do it via .net? I can pull one column correctly for all records just can't figure out how to pull multiple columns. For instance I can easily pull samaccountname but not it and anything else. I am simply not advanced enough in .net. The description field also appears to be a problem.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
never got an answer actually directed to the question. The proc was usable with modifications however.
I use a tool for this... It's free for 30 days. Data Sync Design Studio
I hate dealing with LDAP/ActiveX/etc.. etc..
http://sharepoint.simego.com/KB/DataSyncStudio/Wiki/Home.aspx