Hi Filip - I have three asks:
- How to pass input parameter(s) to a workflow (.tas file) through your Toad Scheduler
- How to read input parameters in a workflow (set local variables from the external input parameters)
- How to catch exceptions in a workflow so I can take the necessary actions
Here's my scenario which encompasses the three asks above: I want to create a generic workflow (connect, SQL, Excel, Zip, email) which is driven by local variables (ex: #myVariable1#, etc) without any hard coded values. This should be used for hundreds of reports that I want to automate through this generic workflow. So all activities within my workflow are using workflow variables. These variables are to be set from input parameters (hence my ask #2 above). Based on recommendations from Gary to my previous posts, I'm using a "Loop Dataset" activity to pick up input parameters from a database table and set my local variables from those parameters through the "Set Variable" activity. However, since I have hundreds of reports, I need a primary key like "reportId" to be passed as an input parameter to my workflow so I can retrieve the specific parameters for that report id from the database table. One of those parameters would be the name of the SQL file which has all the SQL statements for that specific report however, I understand that I can't use variables in the file names for either the "Select to File" and "Loop Dataset" activities. In the end, I need to watch and process exceptions in my workflow as needed (hence my ask #3).
Summary: can we create a generic workflow to be scheduled through your Toad scheduler which can take input parameters, map them to local variables and have a catch exception capability (and using variables for file names)? If I can't create this generic workflow as above, I would then need to create an workflow for each of the reports which are in the hundreds.