SSIS Loop Until Download Successful

Advertisement

mitchgibbs
Joined:
Posts:
2
Location:
Chicago, IL

SSIS Loop Until Download Successful

I've followed the tutorials and discussions on this and other websites and they've been very helpful - automating uploading and downloading works great.

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?

Thanks!

Mitch

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,253
Location:
Prague, Czechia

What version of WinSCP are you using? Can you try 4.2.7?
_________________
Martin Prikryl

Reply with quote

mitchgibbs
Joined:
Posts:
2
Location:
Chicago, IL

Not a problem with WinSCP

I'm using the latest version, 4.2.8. However, I don't think WinSCP is a problem. At least in my tests with batch files, it seems to be returning the correct status. If I try to GET a specific file that exists or *.*, it returns a status of 0 and if it try to GET a specific file that doesn't exist, it returns a 1.

What I'm seeking guidance from anyone else on is harnessing this within SSIS. I can't figure out how to get SSIS to loop until it gets a 0 status code from WinSCP.

Thanks for a writing a great program.

Mitch

Reply with quote

Advertisement

You can post new topics in this forum