Powershell script multiple output into SSIS variables

Question

Post reply

Powershell script multiple output into SSIS variables

rocky_498

SSCertifiable

Points: 6532
More actions
November 6, 2020 at 9:22 pm

#3805086
Hi Guys,
How can I store multiple output results from Powershell to variables in SSIS?
Thanks.
- This topic was modified 4 years ago by rocky_498.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Cebisa SSC Enthusiast Points: 151 More actions · Answer 1

Are you calling Powershell from the SSIS package?

What about outputting the results into a sql table and populate variables with values from there?

rocky_498 SSCertifiable Points: 6532 More actions · Answer 2

Thanks for your reply. Yes, I am calling PowerShell script from SSIS (Execute Process task).

I have a couple of options that I can take.

Combine two outputs in one Output in Powershell and then use Expression to parse it.
Outputting the results into a SQL table and populate variables with values from there (This is what you suggested)

But my point is, I am sure it must be a way to capture multiple outputs from Powershell in SSIS.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

What is the Powershell script doing - could it be changed to a script task using C#? Maybe you can use a script task and the something like the following?

            using (PowerShell PowerShellInstance = PowerShell.Create())
            {

                string zipArchive = Dts.Variables["User::FullFileName"].Value.ToString();
                string rootDirectory = Dts.Variables["$Project::RootDirectory"].Value.ToString();

                // use "AddScript" to add the contents of a script file to the end of the execution pipeline.
                // use "AddCommand" to add individual commands/cmdlets to the end of the execution pipeline.
                PowerShellInstance.AddScript("param($zipArchive, $destinationFolder) " +
                    "Add-Type -assembly System.IO.Compression.FileSystem; " +
                    "[io.compression.zipfile]::ExtractToDirectory($zipArchive, $destinationFolder)");

                PowerShellInstance.AddParameter("zipArchive", zipArchive);
                PowerShellInstance.AddParameter("destinationFolder", rootDirectory);

                // invoke execution
                PowerShellInstance.Invoke();
            }

This is an example of using Powershell to extract files from a zip archive (note: this is no longer needed since we have access to zip functionality in .NET - but does show how to instantiate an instance of Powershell).

Here is an example of returning data: https://www.codeproject.com/Questions/1206308/Powershell-commands-from-Csharp

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

rocky_498 SSCertifiable Points: 6532 More actions · Answer 4

Thanks for your reply. The PowerShell script is calling a third party .exe file and getting username and PW. I will give a try using C#. However, my biggest concern is to add a "Reference" and other things when moving the Project to Prod.

I am surprised, "Execute Process task" doesn't have the functionality to capture multiple output values.