{"id":607,"date":"2019-02-24T14:06:21","date_gmt":"2019-02-24T12:06:21","guid":{"rendered":"http:\/\/www.flip-design.de\/?p=607"},"modified":"2019-02-24T14:06:21","modified_gmt":"2019-02-24T12:06:21","slug":"get-powershell-data-into-an-ssis-dataflow","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=607","title":{"rendered":"Get PowerShell Data into an SSIS Dataflow"},"content":{"rendered":"\n

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….<\/p>\n\n\n\n

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<\/code><\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
Next I created an SSIS Dataflow with a script task, which is used as a datasource<\/p>\n\n\n\n
\"\"
The dataflow …<\/figcaption><\/figure>\n\n\n\n
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:<\/p>\n\n\n\n
\"\"
The variable gruppenname stores the groupname – the screens are from a german project :-)<\/figcaption><\/figure>\n\n\n\n
Next I need the the C# script with my PS snippet:<\/p>\n\n\n\n
#region Namespaces\nusing System;\nusing System.Data;\nusing Microsoft.SqlServer.Dts.Pipeline.Wrapper;\nusing Microsoft.SqlServer.Dts.Runtime.Wrapper;\n#endregion\n\n#region Custom Namespaces\nusing System.Management.Automation;\nusing System.Management.Automation.Runspaces;\n#endregion\n\nusing System.IO;\nusing System.Collections.ObjectModel;\nusing System.Text;\n\n [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]\npublic class ScriptMain : UserComponent\n{\n    \n    private StreamReader sr = null;\n    Collection<PSObject> results;\n\n    public override void PreExecute()\n    {\n        base.PreExecute();\n        string groupname = this.Variables.Gruppenname;\n        \/\/string PowerShellScript = \"Get-ADGroupMember -Identity \\\"\" + groupname + \"\\\" -Recursive | Select samAccountName\";\n        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\";\n        Runspace runspace = RunspaceFactory.CreateRunspace();\n        runspace.Open();\n        Pipeline pipeline = runspace.CreatePipeline();\n        pipeline.Commands.AddScript(PowerShellScript);\n        results = pipeline.Invoke();\n        runspace.Close();\n\n    }\n\n\n\n    public override void CreateNewOutputRows()\n    {\n        StringBuilder stringBuilder = new StringBuilder();\n        foreach (PSObject obj in results)\n        {\n            Ausgabe0Buffer.AddRow();\n            Ausgabe0Buffer.samAccountName = obj.ToString();\n        }\n    }\n}\n<\/code><\/pre>\n\n\n\n
\"\"
The samaccountname in the output columns stores the username from the AD<\/figcaption><\/figure>\n\n\n\n
Thats it, now you have the PS output into an dataflow …<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/607"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=607"}],"version-history":[{"count":2,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/607\/revisions"}],"predecessor-version":[{"id":612,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/607\/revisions\/612"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=607"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=607"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=607"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}