Hey Toad Team,
I’m wondering, is there any way to set up retries for failed FTP attempts using the FTP activity in Toad Data Point? For example if the connection times out or is lost, that it could retry up to a number of times? If not, is there anyone else who’s had experience with this using maybe the if condition or looping activities to check for a file or on success of the previous action to possibly result in using the FTP activity a set number of times before giving up?
Just thinking how we can set up some type of retry process similar to how other FTP programs use.
Thanks!
Chad
hi chad.king,
Try to disable the “Stop on error” setting (checkbox) in the “Activity info” tab for he ftp activity so when it fails it does not fail the whole script and then try to log comment #_ACTIVITY_RESULT# - you will find out what result codes/values are returned which then can be use in an While activity
OK, so interestingly enough, I have another forum ticket logged specifically for the issue with #_ACTIVITY_RESULT# causing errors when the action is actually successful, which keeps me from being able to set up sort of a “retry” through a loop action or an if action. This is actually the route I was trying to go to create this retry type of feature.
So whether the issue on that can be answered on this forum thread or not, I really wanted to discuss the enhancement ability to create a number of retries like other ftp softwares allow you to do. This way, it’s much more up to date and can handle failed connection requests a set number of times.
But to discuss #_ACTIVITY_RESULT# a bit further, basically say you want to set up an if activity and say based on the #_ACTIVITY_RESULT# variable, which tells you the “failure” message, then rerun do another set of actions, else if it does not fail do another set of actions. That’s where my problem is. When the activity before is successful, the #_ACTIVITY_RESULT# variable is essentially blank. This becomes a problem when feeding that variable into an if action because, like mentioned in the other forum post, TDP says that the variable used cannot empty (“Expression can’t be empty is the error it brings back.”) So I’m really stuck at this issue trying to create my own retry procedure from a TDP automation.
Let me know if you need anything else from me.
Thanks!
Generally when something succeeds it returns the value 0 as the result. Anything else is an error number. So set up a variable to hold the return value intiailly set to 999 (just not zero) and your while loop to keep looping until the result value = 0 or a loop counter exceeds maxNoOfTries (I would create a variable for that too). Make sure you use the correct variable tool (Set Variable for intial creation outside of the loop, and Set Variable Value to update the value specially inside the loop, if you use Set Variable again inside the loop it will create it as a completely different variable with a limited scope of inside the loop even though it has the same variable name as the one outside the loop). You can even use an if condition after the loop to test and see if it exited due to reaching max count (send error email message) or if the return value was 0 on exit of the loop (keep processing). If have used this before with a Pause activity inside the loop so it tries again after x number of minutes. This can save you a lot of time manually running a job for files that don’t get generated consistently at the same time every day. Have fun!
Greg, when you say it returns a value, what is “it”? A specific variable? Regarding an FTP action, I don’t see any results variable like select to file, etc might have, so I don’t know what variable value to look for specifically?
Thanks so much!
Chad
I believe this is incomplete documentation issue.
The _ACTIVITY_RESULT variable is result of last executed activity - at least i believe
Chad,
Sorry, I use run program and call WinSCP for my FTP issues and pass the parameters on the arguments line. Just found that easier to use. run program has a return code variable (0 = success, other numbers = error) that you can use. Although WinSCP has a windows front end I just use it for the command line intefrace so I can use Toad to call it in automated jobs. I had too many problems with Toad’s FTP in earlier versions (I am sure it is better now) but I never went back.
Here is a link to an old post for using an external FTP program. I have an example in there if you are interested:
http://www.toadworld.com/products/toad-data-point/f/36/p/7477/19846#19846
Greg you mentioned that our automation variables have scope. IE: You can have the same named variable inside and outside a loop but they are different variables.
We were considering making variables have global scope and also consolidating the 2 variable activities into one. The same variable would define and set the value.
Do you see any issues with this? Do you have a specific need for variables not being global? We think it would be easier for users to have global variables. Not all of use are programmers[:S]
Chad does Greg’s use of Run Activity work out for you? Or do you want me to add enhancement to add a retry? I had someone ask for similar retry for Copy Activity. So perhaps network issues could all be addressed the same way.
Debbie, Greg’s idea is awesome, but unfortunately we only have an outside FTP software that can be run in batch that does not have errors that toad recognizes. Basically, if there is a timeout issue, toad does not recognize that the connection issue is an issue from the batch file and continues with operation as if no problem existed.
Therefore, I really think a “retry” option for the ftp action feature would be incredible. I can see it being something a lot of folks utilize with connections that may have timeouts, especially like my company who sends so many files to different outside sources each day.
***BUT, with that said, I actually think I have a solution that virtually mimics a retry in TDP. As you know, there is an issue with #_ACTIVITY_RESULT# not showing anything in a variable if the previous action was a success. I believe that’s something they are going to try to work on. Therefore, I did further research and found that the Run Automation Script action has a return code variable that will bring back a result of that action on success or failure (0 on success and 111 or 999 on failure). Therefore, what I did was set up the FTP activity in its own stand alone task. I then took my task that is fixing to call the FTP and created a While Condition action. Before that action I created a simple variable of 111 called something like VAR1, which is the error code that comes back if the action does error. Then on the While loop action, I had it evaluate that expression of #VAR1# <> 0, then placed the run automation script activity that calls up the ftp action. Lastly, under that, I have a set variable value action that essentially goes and replaces the VAR1 variable after runtime with the return code variable of the action that was ran. So if it succeeds the first time, it will start because the first variable was an error that I made and run through the action to run the ftp. Then if it succeeds, it will bring back a result of 0, then replace the VAR1 with 0, which ends the loop and it moves on successfully. If the action does error out like with a timeout, it will come back with the 111 and essentially go through the while loop again and try again. Of course you can make that while loop have a limit of whatever before it officially ends with error, or actually works. But hopefully the 2nd or 3rd time of running it will bring back success which discontinues the loop and has the successful ftp upload.
Anyways, I thought that was something I would share. I did some testing and it seems to work great doing this way, plus it keeps me using the existing FTP action in TDP rather than having to call up an outside FTP program, so I like that as well.
Again, I’d still love the retry, but folks are welcome to try this if they need to in the meantime.
Thanks!
Chad,
Excellent! Thank you for sharing.
I am glad you have a solution for now. I did enter https://jira.labs.quest.com/browse/QAT-10497 for adding a retry in the FTP activity.