May 17, 2010 at 1:20 pm
Is there a good overview of extracting ALL data from Active Directory, utilizing either SQL or VB.NET?
I have been searching for the past two weeks, off and on, and the examples I have found are usually incomplete, overy simple, or do not work.
Perhaps there is something I could use to query AD that would map out the way the data is structured that would enable me to intelligently track things down?
Thanks very much.
May 20, 2010 at 3:47 pm
I've done something similar, however it was just to view the contents. The problem you will find is the 1000 record limit on pulling data from AD. I also didn't have a link server. What I ended up doing was running a script component in SSIS using a list (0-9/A-Z) in a for-each container. It pulls the data and loads it into a SQL table. Which I then query.
Let me know if this seems helpful. I'm sure I can get you more detail.
May 21, 2010 at 7:47 am
An SSIS solution to tables would be great. Specifics would be appreciated. thanks.
May 21, 2010 at 8:44 am
Alright here goes.
The SSIS package starts by deleting the Active Directory table I created in SQL. That leads to a For Each container that uses a hand entered list as its collection (a-z,0-9). Inside this container is a vb script that builds a SQL statement (to query LDAP). It Looks like this:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dts.Variables("varsql").Value = "SELECT useraccountcontrol,cn,co,company,department,description,displayname,employeeID,givenname,l,mail,mailnickname,name,postalcode,samaccountname,sn,st,streetaddress,telephonenumber,title,lastlogontimestamp,pwdlastset,whenchanged,whencreated,extensionattribute1 FROM 'LDAP://DC=<youdomain>,DC=<>,DC=<>' WHERE samaccountname = '" & CStr(Dts.Variables("varforeach").Value) & "*' and objectClass='person' and not (objectClass='computer')"
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
You see that that's setting a variable using the for each collection. If you have more than 1000 entries in a particular letter, you may have to break them up. Okay, the script component leads to a sequence container. Inside this sequence container is a single object, a Dataflow object. This has a datareader source. This data reader source has a sql command in it. This is the sql command that you will change, it's get set using the sequence container it sits in. So you'll use expressions to set the sql command to the value of the variable that you set earlier using the script component. This data reader source leads to an OLE DB Destination. (still in the dataflow). This data flow is the destination table that you may have cleared out earlier.
I realize that there is more to it than this. I apologize that I am unable to send you my SSIS package. I will be around today to help you out with the little things that will inevitably come up. I hope this helps, I know it took me a while to come to this as a solution.
May 22, 2010 at 7:16 am
Thanks very much for the help. I will be working on this next week.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply