Import data from Active Directory Security Groups using SSIS

Question

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

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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