June 21, 2012 at 6:55 am
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
June 21, 2012 at 7:06 am
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
June 21, 2012 at 7:50 am
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
July 3, 2012 at 10:40 am
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy