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.