Facing issue in Execute PowerShell Script in Execute Process Task in SQL Server Integration Service(SSIS)

Question

Hi All,

I created a package in SSIS and using Execute Process Task for executing Power shell Script. My Package also include password which is sensitive Parameter.

Everything work fine and it run successfully but when Package failed it give error including password details. Now I want password should not come in error Message.

please help me.

Answer 1

Instead of using an Execute Process Task to run your powershell script - I suggest you use a script task and execute the powershell script in that task. This will give you the ability to control what happens when you get an error from that script.

Here is an example of calling out to Powershell from a C# script task. You can add a try/catch to this to capture any errors - and return a specific error message as needed.

            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();
            }

In this example - I am building the script in the code...if you have a script file built already - you can call out to that script, however - I would pull that code into the script task to keep everything in one place and easily modifiable. Either way...you can control what happens when it fails.

Answer 2

Hi @Abhishek Sharma ,

Could you please share the error messages in detail when your package failed?

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?