Import data from Active Directory Security Groups using SSIS

• Lempster

  SSCoach

  Points: 15651

  More actions

  June 21, 2012 at 6:55 am

  #263125

  I have read a number of help articles, particularly http://social.technet.microsoft.com/wiki/contents/articles/processing-active-directory-information-in-ssis.aspx 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.

  Thanks

  Lempster

• Lowell

  SSC Guru

  Points: 323524

  More actions

  June 21, 2012 at 7:06 am

  #1503907

  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

  Lowell

  ---

  --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!

• Lempster

  SSCoach

  Points: 15651

  More actions

  June 21, 2012 at 7:50 am

  #1503931

  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.

  Lempster

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  July 3, 2012 at 10:40 am

  #1508325

  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..

  CEWII