I have an SSIS package that is using WinSCP a couple of times. The package creates a CSV file and then uploads it to a vendor's SFTP site. This works great. The vendor then screens the data and returns results within 1-3 hours. I've got the SSIS package setup to upload the file and then wait an hour. I'd then like to have it try to download the resulting file - if it succeeds, move on, if it fails, wait 10 minutes and try again.
I'm thinking that a FOR LOOP is the way to go. WinSCP seems to return appropriate ERRORLEVELs, 0 if the GET works and 1 if it doesn't. However, I can't quite figure out how to make it work. I've created a package variable to capture the result and setup the FOR LOOP to look at that variable and loop until it equals 0. I setup the EXECUTE PROCESS task to use that variable for the StandardErrorVariable.
If I set FailTaskIfReturnCodeIsNotSuccessValue to False on the EXECUTE PROCESS, it just flies through. If it I set it to True, it stops the package from running.
Any advice, either on making this work or a better method for achieving the same thing?