I have an SSIS package that I've had to alter to call a powershell script to do SFTP. We have other SSIS packages that do the exact same thing and they work fine. But for some reason, when trying to run this package in the VS 2010 shell on the Windows 2012 R2 server, it pretends to run the execute process task but doesn't.
The package says the script succeeds but it runs in under a minute, checks it off as successful, then errors out on the next step which unzips the file pulled down from the vendor site. The unzip fails because the zip file doesn't exist on the destination.
This is driving me nuts. The call is almost exactly the same as in other SSIS packages except for the powershell script name.
Executable: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Arguments: -windowstyle Hidden -ExecutionPolicy Bypass -ExecutionPolicy Unrestricted -Command '\\My\nas\Share\App\Folder\My Powershell File Get.ps1'
Any idea what I might be missing here?
EDIT: It doesn't work when trying to run the package from the job either. But the powershell script works fine when I run it in a powershell window or right click the script and run it.
July 6, 2021 at 5:30 pm
SSIS will execute external processes in a disconnected fashion, so what is happening here is the following:
The other similar packages are most likely succeeding because there are either no follow-up tasks that need the script to complete execution, or the execution of those scripts complete so quickly that it's not an issue.
The best way around this is to either build in a delay, or set some metadata from within the PowerShell script so that you can check successful completion before proceeding with the rest of the package.
July 6, 2021 at 5:31 pm
Are there any clues in the All Executions report (All Messages)?
What is different about this package? Software versions? Different instance?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 6, 2021 at 5:44 pm
Your subsequent tasks execute, but fail because the PowerShell script has not finished running yet.
I don't disagree with this comment, but... There is a precedence constraint between this task and the unzip task. Why would the Execute Process task mark "successful" and act like it's finished when it's not?
This file is larger than other files by magnitudes, definitely, but even a different powershell task that only connects to the website to get the date off the file (which happens first) has the same behavior. It pretends to complete but doesn't actually pull any data and the vendor (at one point) said they didn't even see it connect except during the times when I know I was running it manually.
July 6, 2021 at 5:47 pm
Are there any clues in the All Executions report (All Messages)?
What is different about this package? Software versions? Different instance?
I looked at output and didn't see any messages in that. I'll go back and check the All Executions report.
Regarding differences, instances are the same but other packages were developed on my old PC which had BIDS 2012 on it. Then I got a new PC and wasn't allowed to put anything 2012 on it because it's all out of support at MS. So I've been having to use the VS 2010 shell on the server to work on this.
Hopefully by the end of 3rd quarter we'll be working with SQL 2019, but there have been red tape related issues delaying our upgrade colliding with corporate IT's need to remove all our vulnerable software. Which has been really fun to navigate.
July 6, 2021 at 5:48 pm
First step just add some simple file logging to the powershell script, just a hello at the start and good by eat the end to a temp file. That will let you know if the script is even executing.
July 6, 2021 at 5:50 pm
First step just add some simple file logging to the powershell script, just a hello at the start and good by eat the end to a temp file. That will let you know if the script is even executing.
That is an excellent idea. Thank you. I'll definitely do that.
July 6, 2021 at 6:10 pm
I don't disagree with this comment, but... There is a precedence constraint between this task and the unzip task. Why would the Execute Process task mark "successful" and act like it's finished when it's not?
The precedence constraints are doing exactly what they are supposed to do, but because the execution is disconnected it doesn't indicate successful completion of the script...only successful initiation.
It's not you, it's SSIS :-/
July 6, 2021 at 7:58 pm
Brandie Tarvin wrote:I don't disagree with this comment, but... There is a precedence constraint between this task and the unzip task. Why would the Execute Process task mark "successful" and act like it's finished when it's not?
The precedence constraints are doing exactly what they are supposed to do, but because the execution is disconnected it doesn't indicate successful completion of the script...only successful initiation.
It's not you, it's SSIS :-/
Actually - it is the execute process task and not really SSIS. Personally - I don't use the execute process task at all - because of issues like this. Instead, I will use a script task where I can then control the processing as well as implement error trapping.
And example would be something like this:
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 calls out to Powershell to extract data (which has since been replaced with direct .NET code), but shows how you can call out to a script or build a script in code and call it directly.
I have moved away from using Powershell for sFTP and unzipping files - I now use WinSCP's .NET wrapper for sFTP in a script and .NET in a script directly to unzip files.
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
July 6, 2021 at 9:45 pm
Brandie Tarvin wrote:I don't disagree with this comment, but... There is a precedence constraint between this task and the unzip task. Why would the Execute Process task mark "successful" and act like it's finished when it's not?
The precedence constraints are doing exactly what they are supposed to do, but because the execution is disconnected it doesn't indicate successful completion of the script...only successful initiation.
It's not you, it's SSIS :-/
Seems to be working fine for me? Execute Process Task waits for the process to complete, then depending on the return code you can branch to a failure or success path.
July 6, 2021 at 9:57 pm
Seems to be working fine for me? Execute Process Task waits for the process to complete, then depending on the return code you can branch to a failure or success path.
Add "sleep 60" as last line in your PowerShell script, and let us know what happens then 🙂
July 7, 2021 at 8:01 am
powershell has a quirk - exit status is not the one of the last command executed - been bitten by this a few times.
whatever you do within your scripts you need to ensure you trap any error (or return code) from any executable/function called by your script and at the end of your top level powershell do a "exit 0" --(zero) or "exit errorlevel" so it returns an error to the process that executed powershell.exe
and as a must have either implement a good logging process within your powershell and/or redirect everything to a fill "powershell.exe .. >output.log"
July 7, 2021 at 1:48 pm
ZZartin wrote:Seems to be working fine for me? Execute Process Task waits for the process to complete, then depending on the return code you can branch to a failure or success path.
Add "sleep 60" as last line in your PowerShell script, and let us know what happens then 🙂
Sleep 3000 and it's still waiting patiently after i went for coffee 🙂
July 7, 2021 at 1:52 pm
Sleep 3000 and it's still waiting patiently after i went for coffee 🙂
Interesting...that's not the behavior I'm seeing. If I use the Execute Process Task the way described by Brandie, it immediately comes back with success without waiting for the PS script to complete.
July 14, 2021 at 1:49 pm
ZZartin wrote:First step just add some simple file logging to the powershell script, just a hello at the start and good by eat the end to a temp file. That will let you know if the script is even executing.
That is an excellent idea. Thank you. I'll definitely do that.
I had forgotten that I already did this prior to posting. No log generates at all when I run the Execute Process task inside the SSIS package, whether its run as part of the package run or run by itself. I didn't do the hello or goodbye but I do have logging which only generates if I run the script manually.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply