1

I have a SSIS package that executes the WinSCP.exe via execute process task and download the set of files to some common folder location.

Files names are:

Text1.csv
Test2.csv

and so on..

After that I am running another set of Execute process tasks which will use the C:\Windows\System32\cmd.exe which start copying downloaded files to different respective locations.

Below is my command line arguments to first download the files at some common location:

/log=G:\USER_DATA\USER_USER_SYNC\SFTP_LOG\user_sync_winscp.log /command "open sftp://bisftp:*UFVy2u6jnJ]#hU0Zer5AjvDU4#K3m@stransfer.host.com/ -hostkey=""ssh-rsa 2048 9b:63:5e:c4:26:bb:35:0d:49:e6:74:5e:5a:48:c0:8a""" "cd /DATA" "get -filemask=">=today" Test1.csv Test2.csv G:\USER_DATA\USER_USER_SYNC\Download\" "exit"

Below are my command line arguments to copy the files to different locations:

/C copy /b G:\USER_DATA\USER_USER_SYNC\Download\Test1.csv G:\USER_DATA\USER_USER_SYNC\Testing1

/C copy /b G:\USER_DATA\USER_USER_SYNC\Download\Test1.csv G:\USER_DATA\USER_USER_SYNC\Testing2

I have few questions here.

Since I am using filemask to download the latest updated files only. If first Execute process task run successfully then it will set the exit code returned as '0' and then next Execute process task gets executed to copy the files to different locations. Everything runs fine till this end.

However there can be a case when first execute task is not able to download the files to common folder location. Reasons of failure could be that other party has not put the latest files on SFTP server and due to date mismatch, it was not able to download the files in first place or could be some other network issues as well.

So it throws an exception that like "The process exit code was "1" while the expected was "0"." due to when my next subsequent execute process task run and it's getting failed as it has nothing to copy or move.

Can we capture the exit code returned to some variable, so that we can use it further in some expression to decide whether to execute another task or not?

Also I am using different execute process task to copy the files. Can we combine separate copy steps into one and use one Execute process task?

vikrant rana
  • 4,509
  • 6
  • 32
  • 72
  • 1
    Would this help? [If there is an error, how can I exit from SSIS package](https://stackoverflow.com/q/35297351/850848) – Martin Prikryl Aug 30 '19 at 08:39
  • 1
    Also note, that as I have suggested before, you can download the files directly to the folders you want. No need for additional `copy` commands. – Martin Prikryl Aug 30 '19 at 08:40
  • Yeah. I am also thinking to remove extra copy commands and to have them in same execute process task. That will work. Still I need to capture the exit code returned to decide whether to carry on the transformation on that data or not. since we cannot proceed further until we have all the files in place. like no files should be missed in the downloading process. I am having a look at link mentioned above. Thanks for referring it. – vikrant rana Aug 30 '19 at 09:07
  • @Martin Prikryl . Thanks Martin. Above linked helped me a lot and solved half of my problem. Thanks for referring me to the link. I was able to capture Exit code in a variable. Winscp is returning exit code as '1' in a cases when there's file name mismatch or something gone wrong with command line arguments. I can use this return code further to handle other things. – vikrant rana Aug 31 '19 at 13:14
  • However it's was returning exit code as '0' in a case when it's not able to find the updated files on server since we are using file mask. That also will not be a problem, as I have created a file watcher in C# to see whether any file was downloaded or not at respective locations. And I have removed my extra copy commands and have included them in same Execute process task. Everything is working fine as expected. Many many Thanks for all your support. You are quite a humble and supportive man. – vikrant rana Aug 31 '19 at 13:14
  • 1
    If you want WinSCP to fail, when file mask does not match anything, use command [`open failonnomatch on`](https://winscp.net/eng/docs/scriptcommand_option#failonnomatch) at the begging of your script. See [Fail SSIS task when file to be uploaded to FTP server is missing (file extensions)](https://stackoverflow.com/q/44798761/850848). – Martin Prikryl Sep 01 '19 at 07:11
  • ohh. Yeah. That's what I was looking for. Thanks. You can post this as an answer I will accept that. Below is my cmd line arguments. (/log=G:\USER_DATA\USER_USER_SYNC\SFTP_LOG\user_sync_winscp.log /command "open sftp://bisftp:*UFVy2u6jnJ]#hU0Zer5AjvDU4#K3m@stransfer.host.com/ -hostkey=""ssh-rsa 2048 9b:63:5e:c4:26:bb:35:0d:49:e6:74:5e:5a:48:c0:8a""" "cd /DATA" "get -filemask=">=today" Test1.csv G:\USER_DATA\USER_USER_SYNC\Testing1\" "get -filemask=">=today" Test2.csv G:\USER_DATA\USER_USER_SYNC\Testing2\" "exit"). I am not sure where exactly I need to put this option in above command. – vikrant rana Sep 01 '19 at 07:53
  • 1
    It should have been `option`, not `open` - Use it like: `... "cd /DATA" "option failonnomatch on" "get ...` – Martin Prikryl Sep 01 '19 at 08:12
  • @ Martin Prikryl.. Thanks Martin. Your suggestion works like a charm. It was my fault to understand it bit late. I was little confused with exit code returned and error code. – vikrant rana Sep 03 '19 at 11:25

0 Answers0