SSIS LDAP query using .net providers for OleDB for Microsoft Directory Services

• pmetcalf

  SSC Enthusiast

  Points: 184

  More actions

  January 27, 2017 at 12:25 pm

  #334539

  I'm trying to load 3 tables in SQL Server 2012, Users, Groups and UserGroups using SSIS and .Net for MDS.

  The SQL Server 2012 "User" table:
  CREATE TABLE [dbo].[AD_AccountData](
   [UserName] [nvarchar](max) NULL,
   [AccountName] [nvarchar](max) NULL
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  Groups table:
  CREATE TABLE [dbo].[AD_Groups](
   [mail] [nvarchar](max) NULL,
   [cn] [nvarchar](max) NULL
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  UserGroups table:
  CREATE TABLE [dbo].[AD_Members](
      [UserName] [nvarchar](max) NULL,
      [ADGroup] [nvarchar](max) NULL
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  For the connection manager I'm using the provider ".Net Providers for OleDb\OLE DB Provider for Microsoft Directory Services".
  In the data flow source, the LDAP query for Users is:
  SELECT sAMAccountName, displayName
  From 'LDAP://MyLdapAddress'
  WHERE objectClass='user' and objectCategory = 'Person'

  In the data flow source, the LDAP query for Groups is:
  Select SAMAccountName,cn
  From 'LDAP://MyLdapAddress'
  WHERE objectClass = 'group'

  These run successfully.  I want to populate the AD_Members with sAMAccountName and all AD Groups that sAMAccountName is in.  For example, 
  Username   ADGroup
  pmetcalf    SSIS Developers
  pmetcalf    SQL Server Admins
  pmetcalf    Domain Users

  What is the TSQL for the AD_Members?