Options for Complex Job

Hello

I'm looking for suggestions on how to set up 24x7 automation job that's a bit complex.

The job needs to be initiated with a file found or email received trigger. The job runs an oracle query via batch file. The SQL query is built on a number of dynamic criteria: 1) channel to process - contents of the file found or email received (there could be 4 or more channels), 2) effective date range & applied date calculated within the JAL script - dates and date range is consistent for the month for all channels, and 3) sales role as read in from db connection for each channel.

There are 4 independent channels that could be processed at different times or at the same time depending on when the email is received or when the file is found.

I was considering having a 'template SQL file' where the JAL script would run and replace certain values within the SQL script then save the sql stmt as that channels SQL Stmt. To be executed later. So template job would be updated with values either read in or calculated and update the template and save as channel_A.sql. The next one would be channel_B.sql, etc.

The dynamic values are: Where SalesChannel ='CCCCC' where CCCCC would be replaced with A- the updated criteria stmt would be Where Channel = 'A'. The Value A would be read in from the File found or the email received contents.
DateRange ->Calculated within JAL Script and would replace EffectiveDateStart, EffectiveDateEnd, and AppliedDate represented by values SSSSS, EEEEE, and AAAAA.
SalesRole -> Read in from DBConnection within JAL script. There could be 1 to multiple sales roles and would replace value RRRRR as an example:
and SalesRole in ('RRRRR') would be replaced with and SalesRole in ('SalesRole1','SalesRole2','SalesRole3','SalesRole4',......) The sales role is different for each channel and could change multiple times during the year.

Ideally, I would like to see only one Task/Job that would create the multiple SQL scripts specific for each channel. This would reduced the number of JAL jobs that would need to be maintained: i.e. have Job 1 - All SQL scripts instead of having Job 1: Channel A script, Job 2: Channel B script, Job 3: Channel C, etc. The concern is in regards to the potential overlapping that might occur if multiple email messages are received or if multiple files are found at the same time.

Any Thoughts? Or is this way to complicated and I should look to do something easier.

Thanks.

Here are few suggestions I hope you will find helpful

To avoid having multiple overlapping job instances set the instance control job property to Don't start new instances if there is already a running or queued instance of the same job.

If all input files/emails need to be processed as separate events and you can choose between files or emails, choose files. It's technically much easier to lol through a bunch of files than deal with a bunch of emails (new emails will be ignored if they are received during an active job run. The suggested instance control option will cause that condition.

To have multiple files trigger same job instance, use a file mask in the file trigger settings. Choose job script for the job and in the script get a list of matching files and then loop through that list processing them one by one.

I think your idea for a SQL script template with replaceable placeholders would work just fine. As far as how to do that efficiently much depends on the actual script.

Have I missed anything?

[/list]