vb.net - How to populate an SQL Server table with users information from Active Directory? -

vb.net - How to populate an SQL Server table with users information from Active Directory? -

using vb.net in ssis package, how populate sql server table users in multiple active directory domains (in same forest)?

script component (vb.net) system.directoryservices

here sample logic import active directory users info 1 domain database table help of vb.net in script component configured source. sample tested in ssis 2012 should work in ssis 2008 , above. logic not work in ssis 2005 because namespace system.directoryservices.accountmanagement introduced in .net framework 3.5 , ssis 2005 uses .net framework 2.0

create ssis package. sample uses ssis 2012.

create oledb connection manager connect sql server database. if created info source, add together info source package's connection manager tab.

drag , drop data flow task onto control flow tab.

double-click data flow task switch data flow tab.

drag , drop script component onto data flow tab.

check source on select script component type dialog , click ok.

double-click script component open script transformation editor. click inputs , outputs tab page.

rename output activedirectory give meaningful name.

select output columns , click add together column add together each of below mentioned columns. illustrate example. might need add together columns of preference.

column definition within script component name info type length ----------------- ------------------------ ------ firstname unicode string [dt_wstr] 255 lastname unicode string [dt_wstr] 255 samaccountname unicode string [dt_wstr] 255 userprincipalname unicode string [dt_wstr] 255

after defining columns, click script tab page

change scriptlanguage microsoft visual basic 2010

on solution explorer, right-click script component project , click add reference.... add together references next namespaces.

namespaces referenced in script component system.directoryservices system.directoryservices.accountmanagement paste below vb.net code script component. replace section <your domain name goes here> appropriate domain name. code initializes principalcontext , principalsearcher objects in preexecute method , disposes them in postexecute method. createnewoutputrows method loops through each of row found in advertisement fetch user attributes information. script component code (vb.net) #region "imports" imports scheme imports system.data imports system.math imports microsoft.sqlserver.dts.pipeline.wrapper imports microsoft.sqlserver.dts.runtime.wrapper imports system.directoryservices.accountmanagement imports system.directoryservices #end part <microsoft.sqlserver.dts.pipeline.ssisscriptcomponententrypointattribute()> _ <clscompliant(false)> _ public class scriptmain inherits usercomponent dim principalcontext principalcontext = nil dim principalsearcher principalsearcher = nil public overrides sub preexecute() principalcontext = new principalcontext(contexttype.domain, "<your domain name goes here>") principalsearcher = new principalsearcher(new userprincipal(principalcontext)) mybase.preexecute() end sub public overrides sub postexecute() principalcontext = nil principalsearcher = nil mybase.postexecute() end sub public overrides sub createnewoutputrows() each principal principal in principalsearcher.findall() dim entry directoryentry = trycast(principal.getunderlyingobject(), directoryentry) activedirectorybuffer .addrow() if entry.properties("givenname").value isnot nil .firstname = entry.properties("givenname").value.tostring() else .firstname = "unknown" end if if entry.properties("sn").value isnot nil .lastname = entry.properties("sn").value.tostring() else .lastname = "unknown" end if if entry.properties("samaccountname").value isnot nil .samaccountname = entry.properties("samaccountname").value.tostring() else .samaccountname = "unknown" end if if entry.properties("userprincipalname").value isnot nil .userprincipalname = entry.properties("userprincipalname").value.tostring() else .userprincipalname = "unknown" end if end next end sub end class

close script transformation editor.

drag , drop ole db destination onto info flow tab. connect script component ole db destination redirect source output. select appropriate ole db connection manager , table info should inserted into.

ways improve approach:

this sample provides loading info 1 domain. if have multiple domains, stored them in table. fetch info of domain lists , utilize foreach loop container available on command flow loop through each domain , users info using above mentioned approach. may there improve way within vb.net.

active directory user attributes

you can find finish list of active directory user attributes in below msdn link. need click links find ldap-display-name.

all attributes (windows)

here link might help user object attributes

user object attributes (windows)

vb.net ssis active-directory