Pull User Info from Active Directory using .net for SSIS package

qube09
qube09 used Ask the Experts™
on
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,distinguishedname,lastlogintimestamp,department,description.
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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
how often do you need to do this?

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

Author

Commented:
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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.
We had the same problem and could not find a work-arround -even with our .NET folks.
We chose the 'hokey' solution and wrote a stored procedure to loop through. It works fine and fast, scheduled in SSIS. For what it is worth, I attached the code.
Good Luck,
HoggZilla
 

ALTER PROCEDURE [dbo].[spActiveDirectoryTable]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
 
---------------------
-- Declare Variables
---------------------
DECLARE @chvAlphaChars VARCHAR(60)
,@chvSearch VARCHAR(10)
,@chvSearchLevel1 VARCHAR(1)
,@chvSearchLevel2 VARCHAR(1)
,@chvSearchLevel3 VARCHAR(1)
,@intcountLevel1 INT
,@intcountLevel2 INT
,@intcountLevel3 INT
,@intRowCount INT
,@strSQL NVARCHAR(4000) ,@strADSISQL NVARCHAR(4000)
 
------------------------------------
-- Delete existing data from table
------------------------------------
TRUNCATE TABLE dbo.ActiveDirectoryUsers
 
-------------------------------------------------
-- Search letters to cycle through
-- any chars, but the first char must be a space
-------------------------------------------------
SET @chvAlphaChars = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ'
 
---------------------------
-- Start on non space char
SET @intcountLevel1 = 2
---------------------------
 
--------------------
-- First level loop
--------------------
WHILE @intcountLevel1 <= LEN(@chvAlphaChars) BEGIN
-- Get first level char
SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)
-- Reset start on space
SET @intcountLevel2 = 1
-- Second level loop
WHILE @intcountLevel2 <= LEN(@chvAlphaChars) BEGIN
-- Reset start on space
SET @intcountLevel3 = 1
-- Third level loop
WHILE @intcountLevel3 <= LEN(@chvAlphaChars) BEGIN
 
-------------------------------------------------------------------------------------------------------------
-- Setup the string to search for. By using the trim function we can form each level depending on no records
-- eg A 99, B 1000 > BA 9, BB 20 etc
-- Trim the spaces forming just A, B, C ; AA, AB for search etc
-------------------------------------------------------------------------------------------------------------
SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)
SET @chvSearchLevel2 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel2,1))
SET @chvSearchLevel3 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel3,1))
SET @chvSearch = @chvSearchLevel1 + @chvSearchLevel2 + @chvSearchLevel3
 
SET @strADSISQL = 'SELECT samaccountname,sn,givenname,mail,mobile,telephonenumber,homephone,facsimiletelephonenumber,physicaldeliveryofficename, useraccountcontrol '
+ CHAR(13) + 'FROM ''''LDAP://xx.ssss.kkkkkk.com'''' '
+ CHAR(13) + 'WHERE objectCategory = ''''Person'''' '
+ CHAR(13) + 'AND objectClass = ''''user'''' '
+ CHAR(13) + 'AND sAMAccountName = ''''' + @chvSearch + '*'''' '
 
SET @strSQL = 'INSERT INTO dbo.ActiveDirectoryUsers (EID,LastName,FirstName,EmailAddress,MobilePhone,WorkPhone,HomePhone,FaxNumber,OfficeLocation, UserControlAccount) '
+ CHAR(13) + 'SELECT samaccountname AS EID,sn AS LastName,givenname AS FirstName,mail AS EmailAddress,mobile AS MobilePhone,telephonenumber AS WorkPhone,homephone AS HomePhone,facsimiletelephonenumber AS FaxNumber,physicaldeliveryofficename AS OfficeLocation, useraccountcontrol as UserControlAccount '
+ CHAR(13) + 'FROM OPENQUERY(MVCIADADSI,''' + @strADSISQL + ''' ) '
 
EXEC SP_EXECUTESQL @strSQL
 
SET @intRowCount = @@ROWCOUNT
-- Prints what string is being searched for : no of inserts. DEBUG CODE
-- PRINT @chvSearch + ' : ' + CONVERT(VARCHAR,@intRowCount)
 
-- If searched on @chvSearchLevel1 and under 1000 then everything is fine so skip search2 to next search1 eg A > B
IF @intRowCount < 1000
AND @chvSearchLevel2 = ''
SET @intcountLevel2 = @intcountLevel2 + 100
 
-- If searched on @chvSearchLevel2 and under 1000 then everything is fine so skip to next search2 eg AA > AB
IF @intRowCount < 1000
AND @chvSearchLevel3 = ''
SET @intcountLevel3 = @intcountLevel3 + 100
 
-- Else over 1000 so increment third level
SET @intcountLevel3 = @intcountLevel3 + 1 END
 
------------------------------------
-- Increment next second level char
------------------------------------
SET @intcountLevel2 = @intcountLevel2 + 1 END
 
------------------------------------
-- Increment next first level char
------------------------------------
SET @intcountLevel1 = @intcountLevel1 + 1
END
END

Open in new window

Commented:
Definitely not 'hokey'. That is a fairly sophistocated proc. It does indeed work. Modified it to suit. Although I must admit that I was looking forward to struggling thru with .net as a kind of tutorial. Perhaps the most direct path is best (at least for now).

Author

Commented:
never got an answer actually directed to the question. The proc was usable with modifications however.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial