Import data from Active Directory Security Groups using SSIS

  • I have read a number of help articles, particularly with regard to importing Active Directory data. However, they all deal with importing users from an OU or a DC; what I would like to be able to do is import the members of a Security Group, but when I set the query in my Data Reader Source to be:

    SELECT member FROM 'LDAP://cn=Prod_g_DBA_Admins,OU=IT Support,OU=SecurityGroups,OU=ProductionEnvironment,DC=mydomain,DC=mysubdomain'

    ..I get a System.Object[] returned rather than a list of group members. I've also tried using an LDAP query rather than a SQL query, but I get the same result.

    Is the answer to execute the query in an Execute SQL Task in the Control Flow, store the resultset in a variable of type Object and then use a ForEach loop to process the contents of the variable? Has anyone done that?

    I do have an alternative in that I can create a linked server and write a stored procedure that runs OPENQUERY queries against the linked server, but it would be more elegant if I could do everything in an SSIS package IMO.



  • i'm not an SSIS guy by any means, but a script task that hits the extended stored procedure xp_logininfo might be a little easier:

    EXEC master..xp_logininfo @acctname = 'mydomain\Prod_g_DBA_Admins',@option = 'members' -- show group members


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks for the quick reply Lowell, but xp_logininfo will only return information for Windows Groups that have been added as a login to SQL Server. What I am talking about is querying the Active Directory server, i.e. the Domain Controller, outside of SQL Server using an LDAP-type query.

    Cheers anyway.


  • I'm thinking a script source might be a better approach, you can then use the .net framework to query the AD objects and output a stream of data into a data-flow..


Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply