In my last project I need to capture some data from the Active Directory – especially user from the local security groups. So I decided to use a Power Shell Script. With PS it is very easy to capture all user from the groups and walk through nested groups. because in my interested are only user, not a group which is nested into another group….

Get-ADGroup -Filter {(name -eq  [groupname]) } | Get-ADGroupMember -Recursive | Where { $_.objectClass -eq "user" } | Get-ADUser -properties * | Where-Object { $_.DistinguishedName -notlike '*some comment*' }| select SamAccountName  -unique

The script above select the unique usernaqmes from a given group and excludes user, who have an special comment. The script iterates though any nested group and returns the username. Now I need the list not in PS, I need it into SSIS to transfer the data into a database.

Next I created an SSIS Dataflow with a script task, which is used as a datasource

Next I created a variable who stores the groupname who I want to capture and then I configured the source – I used C# as my preferred programming language:

Next I need the the C# script with my PS snippet:

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
#endregion

#region Custom Namespaces
using System.Management.Automation;
using System.Management.Automation.Runspaces;
#endregion

using System.IO;
using System.Collections.ObjectModel;
using System.Text;

 [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    
    private StreamReader sr = null;
    Collection<PSObject> results;

    public override void PreExecute()
    {
        base.PreExecute();
        string groupname = this.Variables.Gruppenname;
        //string PowerShellScript = "Get-ADGroupMember -Identity \"" + groupname + "\" -Recursive | Select samAccountName";
        string PowerShellScript = "Get-ADGroup -Filter {(name -eq  \"" + groupname + "\") } | Get-ADGroupMember -Recursive | Where { $_.objectClass -eq \"user\" } | Get-ADUser -properties * | Where-Object { $_.DistinguishedName -notlike '*any comment*' }| select SamAccountName  -unique";
        Runspace runspace = RunspaceFactory.CreateRunspace();
        runspace.Open();
        Pipeline pipeline = runspace.CreatePipeline();
        pipeline.Commands.AddScript(PowerShellScript);
        results = pipeline.Invoke();
        runspace.Close();

    }



    public override void CreateNewOutputRows()
    {
        StringBuilder stringBuilder = new StringBuilder();
        foreach (PSObject obj in results)
        {
            Ausgabe0Buffer.AddRow();
            Ausgabe0Buffer.samAccountName = obj.ToString();
        }
    }
}

Thats it, now you have the PS output into an dataflow …