How to pass input parameters to a workflow

Toad Data Point & Toad Intelligence Central
1

Two related questions:

  1. Is there a way to pass input parameters (numeric, strings, etc) to a workflow (.tas file)?
  2. Is there a way to pass input parameters (numeric, strings, etc) to a workflow initiated through the Toad scheduler?

Thanks!

2

Toad command line supports firing off .TAS (automation script) files, of course, but I'm not sure about passing input parameters/variables via command line along with it.

Two things come to mind right away though.. maybe they get you closer to your objectives?

  1. The SetVariable task has a checkbox to prompt for the input.

  2. The LoopDataSet task can loop through the contents of a file, like an input parameter file, and variables within the automation job can be set for values as each input row gets processed.

3

Thanks for your comment, Gary.

  • Your #1 option doesn't work for my scenario as I want to do this automatically without having to prompt the user.

  • The #2 option is good once I have an input parameter to know what data I need to fetch from the file. For example, if my workflow is running a report, I might need the report id to be passed as a parameter so I can use it to get all the other needed data for that report only (the file may have thousands of reports with their data but I only need to invoke one at this time).

So, I think the solution should be a combination of passing an input parameter to the workflow and then use that to get the other pertinent data, save the data into variables which then will be used by all the activities in the workflow. The goal is to have nothing hardcoded in the workflow. How can one do that?

Toad should probably have a new activity ("Input parameters") to deal with these types of situations whether we get the input data from a flat file or a database and map it to workflow variables.

4

Look at that LoopDataSet task in more detail. That does what you're describing... e.g. being able to read your input parameters as rows in a flat file, assign the values to variables, and use these variable values later in your workflow... especially in queries that create your dynamically defined reports.

5

Thanks Gary. I understand the principle, however I don't know how to pass an input parameter to the workflow so that could be used to retrieve the other data.

For example, think that you have a database table of people with phone number, address, name, favorite color and so on as columns in the table. This table has 1,000 records, each record representing a person. The primary key is their phone number. So, my workflow would need a phone number (as the primary key of the person record) as an input parameter to then use the LoopDataSet construct and retrieve the rest of the attributes for that one, specific person. The misunderstanding is probably in the fact that I want to only process one record and not the entire file. So that one record needs to be identified by a primary key which I would need to be passed as a parameter to my workflow. Another example is the Toad scheduler activity which uses toad.exe with the .tas file as a parameter to invoke.

So how would I pass my input parameter to my scheduled .tas file?

6

You create an input parameter file, and use the "LoopDataset" task to loop through the records in that file, whether only one record, or a variable number. I'm suggesting "LoopDataset" because its likely you'll want to do this flow for more than just one input. "LoopDataset" assigns a variable/s to your input values. Sounds like you only want the file to include phone number/s. Then, in a subsequent step inside the loop, you can execute your query to pick up the person info for each phone number picked up from your input file.

Let's say you create an "input parameter" file that looks like this:

  • 555-123-4567
  • 555-987-6543
    e.g. one phone number per line, etc.

Then, loosely defined, your automation flow would look like:

  • "LoopDataset" Task, in which you will
    • Read your Input Parameter file
    • Assign a variable (call it KEY) for each phone number, for each row being read.
  • Within LoopDataset, place other tasks. A few examples:
    • Task "SelectToFile"
      could execute your query, and possibly (in this same step) export the results to, say, a CSV file. The query would look something like this:
      • SELECT * FROM <yourtable> WHERE PhoneNum = #KEY#
        where #KEY# represents the dereferenced phone number value from your input file's current row.
    • "Zip/Unzip" task
      coould follow your SelectToFile task to compress the query output
    • "E-mail" task
      to send the zip file as an attachment to desired recipients
      etc.

Hope this gives you a better idea. Here's a nice blog/video by our lead development manager that should give you a little more info on Automation concepts:

7

Thanks again Gary for staying with me on this!

To the point: how would you configure the workflow you defined above to be scheduled to run every day at 10am with id 555-123-4567 and weekly on Tuesdays at 2pm with id 555-987-6543? To expand, I have hundreds of those reports that need to be scheduled at different intervals and frequencies.

8

Hi,
The set variable in Toad Automation can use a SQL file, so you should first try using a simple select id from CUSTIDTABLE where id = 555-5987-6543.
If that works, you can then try to create a seperate SQL for each report. As you have hundreds of such reports, it might be easier for you write the select based on the current time \ day of week so that running this SQL will give you a unique ID for each hour \ day.
I use Excel to create such formulas using two or more columns that store my business data that changes every once in a while. You would have one column of customerID and another having a day-time formula that your DB can evaluate, and a third column having the select that uses the other two columns.
If you need additional help writing something like that, give a holler and I'll try to ask someone how this can be done.
Shimon J.

9

Hi Shimon - I actually don't need a different id for the same report, regardless of whether it's running daily, weekly, etc.

All I want is quite simple: schedule my generic workflow using Toad Scheduler and pass a parameter, like a report id, to my workflow. The workflow should have the capability to get the input parameter, save it in a local variable and then use it to get other data pertinent to the specific report from a different location (ex: database table). All those parameters from the external database table would then be saved into local variables to be used throughout my workflow in different activities, as applicable. This way I make my workflow generic without any hardcoded values. Aside from not being able to pass an input parameter to my workflow, another showstopper is the fact that I can't use variables for my file name/path in the "Select to File" and "Loop .." activities.

So, my showstoppers are:

  • no way to pass input parameters to my workflow
  • no way to specify variables in file path/name for "Select to file" and "Loop" activities
  • no way to catch and handle exceptions in the workflow.

If thousands of my reports follow an identical flow (connect, SQL, Excel, Zip, Email) why would I need to clone and create thousands of workflows just to put in a different SQL file (for each report), a different email distribution list (for each report) and so on when these can be passed in as parameters in one and only one generic workflow? This is a very basic request, in my opinion. Am I way off course here??!!!

10

Hi,
I think that in Data Point you can have a file as input to a Automation workflow. I'm not 100% sure, as when I needed it in Toad for DB2, I realized that it only exists in some other product of Toad.
Nonetheless, I see no dificulty in doing what you want.
You can pass a input parameter to the workflow, by having a simple SQL file match the result that you want to feed to the Workflow. This result is then available in the workflow.
Have you tried seting a variable using a sql?
Select to file CAN use variable names, so the saved file can include the variable name. I do this all the time.
Loops might not be able to use variable names, but that is probably not neccessary if you use data-set-loops. I think that you are right that you can't use the single result of a data-loop-set out of the loop, and therefore you cannot save each report with its own ID / Name.
I have to check on that, and as it seems now I won't have the time for that in the near future.
You might be able to set a new Variable to the value of a data-loop-result and use that variable. I think that I started to test that, but never got it working. Still it might be worth a try.
I don't know anything about exceptions, as I have some system issue sending mail from Toad automation, and therefore never used the mail on error feature.
Shimon

11

Yes, I'm using "Loop Dataset" construct to select values from a database table and store them in workflow variables. I can also use "Set Variable" to set variables using SQL but if I have 10 variables I will end up with 10 SQL Select statements, one for each variable which is less efficient than the "Loop Dataset" construct which brings the full record at once.

I have a few posts relatively similar in regards to passing parameters to a workflow through the scheduler, using variables for file path/name in some constructs and exception handling. I tried examples but I just can't get the point across so let me try one last example before I go home!

Imagine a factory that's automated to build shoes. The ONLY thing it needs is to be told the color of the shoe to build, dynamically upon invocation. All steps are identical for building shoes and the workflow is automated through a scheduler. The scheduler needs to invoke the workflow with different frequencies for different colored shoes. So, as the automation person, I'm scheduling the following jobs:

  • toad.exe (scheduler) my_automated_workflow_to_build_shoes.tas (the workflow) -color="blue" (the input parameter) - weekly, Tuesdays at 4pm
  • toad.exe (scheduler) my_automated_workflow_to_build_shoes.tas (the workflow) -color="red" (the input parameter) - daily at 10am
  • toad.exe (scheduler) my_automated_workflow_to_build_shoes.tas (the workflow) -color="green" (the input parameter) - monthly, first Wednesday at 6am

I need the workflow (tas file) to capute the "color" input parameter into a variable so it can get additional data based on that value. If the color is "green" it will go get the pertinent data for green shoes from another data source (ex: "select all the data I need from some table where color = 'green'). I can do this part of additional data but first I need to capture the "color" input parameter which is passed to the workflow from the scheduler. Can this be done?

12

I think the problem is that you are trying to do 2 different things dynamically. You can easily read in a parameter file as discussed above and then use variables to accomplish what you need for any one run. You can not have a job schedule itself based on the color. Normally you would set up multiple schedules for the job (Tuesdays at 4 PM, daily at 10 AM, monthly first Wednesday at 6 PM). They can all run the exact same .tas file. Your .tas file would have to have logic to determine what color to use based on the day and time. This could be accomplished reading that in from a table that is set up to hold day of week, time or time range (I would recommend expected start and end time) , frequency, and color. With something like that you could set up a master .tas job that calls the actual job. The master job could be scheduled to run every hour. Each hour it reads the table to see if there is a color scheduled to run if so it calls the main .tas job. The main job will know which color to run by reading that same table the master job used to call it. You just have to manually maintain the table for each new color added or schedule change. You will need a condition object in your master .tas file to test the return value from the table query and branch accordingly.

13

Thanks for your answer Greg. So, in essence you're recommending creating my own scheduler (master workflow) and keeping a table with all the scheduling information: day, frequency, primary key for each report ("color" - to keep with our current example) and so on. Then I have to schedule my scheduler to run every hour or so to check which jobs to run based on the information in the scheduling table.

To me (and I'm a totally beginner in Toad so, I could be wrong) this looks inefficient and prone to errors. Why not use the Toad scheduler or the OS (Windows in this case) scheduler which has a quite simple interface and extracts all the scheduling information away from my workflows? I'm Ok with creating 300 schedulers one for each of my 300 reports with the idea being to invoke the one only generic workflow (illustrated in my "my_automated_workflow_to_build_shoes.tas" example above). I would need to pass one parameter only to my workflow which would be the primary key of my report (ex: report id) and with that, I can use activities within my workflow to get the rest of the needed report data from whatever source I want. The scheduler allows you to pass parameters but it's become apparent to me that the workflow cannot take input parameters. I know some activities have a "prompt for input" feature but since I want this to be fully automated (no human intervention) I need the batch way to pass parameters not the interactive way.

Even the most rudimentary programming language has a "Hello " example where is a parameter passed in to the program from the command line (batch/script invocation), the program reads it and then outputs the name. Can Toad workflow do that, meaning getting a lousy input parameter recognized? If not, I think an "Input" activity is needed as part of basic constructs for creating Toad workflows.

1 Like
14

I totally agree, you should be able to add parameters to the command line call to the toad.exe and then use them inside the automation. I need this capability too.

1 Like
15

So, does anybody know if any improvement has been made to allow the passing of input parameters to a workflow?

Also, can we now specify variables in the file path/name for the "Select to file" and "Loop" activities?