June 18, 2021 at 9:18 pm
Hi,
I'm trying to modify my SSIS packages to use sftp and since it cannot be done using the FTP Manager Connection within SSIS, I am calling a PowerShell script to connect to our sftp server and move the files as needed. The PowerShell script works okay and copies the file with no errors, but when I try and configure the Process Task Editor argument section, I am getting an error. Here is the script that runs successfully in PowerShell:
.\sftp.ps1 -Hostname "172.16.24.88" -Username "sftptest" -Password "sftp3st!" -RemotePath "/sftptest/bea/" -FileName "test.txt" -LocalPath "c:\BEA\" -SmbStoragePath "\\ffdata2\departments\IT\BEA\TST\STARKFRIED\ORDERS"
I understand the syntax is different in the Arguments section of the Execute Process Task Editor and that is what I'm struggling with. After many attempts and referencing examples on the internet, this is what I have and it still gives an error in SSIS:
-ExecutionPolicy Unrestricted .\sftp.ps1 -Hostname 172.16.24.88 -Username sftptest -Password sftp3st! -RemotePath /sftptest/bea/ -FileName test.txt -LocalPath c:\BEA\ -SmbStoragePath \\ffdata2\departments\IT\BEA\TST\STARKFRIED\ORDERS
I have tried with " and without and I keep getting the same generic error in SSIS:
Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" "-ExecutionPolicy Unrestricted .\sftp.ps1 -Hostname 172.16.24.88 -Username sftptest -Password sftp3st! -RemotePath /sftptest/bea/ -FileName test.txt -LocalPath c:\BEA\ -SmbStoragePath \\ffdata2\departments\IT\BEA\TST\STARKFRIED\ORDERS" at "K:\SSIS\StarkfriedEDI", The process exit code was "1" while the expected was "0".
Task failed: Execute Process Task
Anyone have experience in using this in SSIS that might be able to point in the right direction?
Thanks!
Bea Isabelle
June 19, 2021 at 4:12 pm
Don't use a process task to call this - use a script task and invoke Powershell in that script task. Or - do what most others have done and use something like WinSCP inside a script task.
Here is an example Powershell to unzip files - you just need to modify it to call the SFTP code:
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();
}
You can review WinSCP setup on there website.
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
June 21, 2021 at 3:40 pm
Hi Jeffrey,
Thank you for your reply! 🙂 WinSCP is what I was looking to use when I started this whole process but I was asked to look into the PowerShell option because it gives us more options and ease to make changes and add additional features. When I looked into how to call PowerShell from SSIS, the articles I came across showed the setup using the Process Task but I will look at calling it from the Script Task and see how that works. I'm just trying to understand the syntax in the Arguments part of the Process Task Editor so I can see why it is failing.
Thanks.
Bea
Thanks!
Bea Isabelle
June 21, 2021 at 4:09 pm
I don't see how Powershell would give you more options - you are looking at secure FTP which is simple FTP over a secured connection. Even with PS - you still have to connect to the sFTP server, issue a GET to download a file or a PUT to upload a file.
You can use the .NET controls from WinSCP in a script task - this is what I use and it works quite well.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply