Optional Parameters Passed to Sequencer

[iq_etl]

As we move into 9.1, we're looking to create one generic Sequence Job that can be used in several projects.

The issue is that some jobs will require a certain parameter from the scheduler while others may not. Is there a way to make a parameter passed to the sequence optional?

Thanks!

[asorrell]

I'm not really understanding the issue. If you don't over-ride the parameter settings when you run the job sequence, it will just take the default. Actually using the parameter when you execute a job is not a requirement. In that sense, all parameters are "optional". If you don't need it, don't use it.

[ray.wurlod]

Can you be a bit more specific about your requirement? String parameters can have a default of "" but using a default of "" for a Date parameter, for example, will lead to failure to start the job.

[iq_etl]

A bit more specific.

We are building a sequence that we'd like to have be fairly generic to handle multiple job types. Most of our jobs delete all the rows on a table, then load the table with new data. However, we have some jobs that don't delete all of the rows, but perhaps a certain set (say based on a fiscal year).

Passing a year of rows to delete will be fine for that job, but what about the other jobs that don't need this year value as they simply delete all of the data?

Or am I looking at having to make a few job sequences. One that handles our standard cases, one that requires one parameter, another that requires two parameters, etc.

Oh, I'm discussing the parameters in the 'Job' tab of the Job Activity in the sequencer, and not the parameters in the job properties.

[chulett]

I don't see the issue. The sequence job carries all possible parameters needed and you pass to each Job Activity stage whatever parameters each one needs.

[iq_etl]

Perhaps there is no issue, but then I likely don't have a real understanding of the Job Activity.

The way I understand it, the Job Activity requires a job name to be selected and only the parameters for that selected job are available in the sequence.

(The hard-coded job name is then manually replaced by a job_name variable so multiple jobs can use it. See this thread I started a few days ago: viewtopic.php?t=150676 )

This isn't a problem for 95% of our jobs as no parameter is necessary. However, if I have a job that requires a parameter (say a year), I'm thinking that I will need to use _that_ job as the one selected in the Job Activity so that the parameter is available if that job is the one running. The other jobs won't need it.

However, I'm thinking if I have another job (another one in that 5%) that requires, say two specific parameters, then my sequence won't work as it'll only have the parameter for that first unique job (the year) I discussed previously.

How can I get the sequence job to carry all possible parameters needed and _not_ just the parameters available to that one selected job the Job Activity requires?

I hope I've been able to be clear.

Thanks!

[ArndW]

Using the Job Activity does limit you to a hard-coded job call.

You can use the API calls to DSAttachJob(), DSSetParameter() and DSRunJob (plus DSDetachJob()) to dynamically call up the appropriate job.

[FranklinE]

I have this exact situation. I have three parameters passed from the scheduler (via a generic script used by all jobs) and one of them is not needed in all jobs.

You need to keep your parallel jobs at the same level of "generic" as the common job activity in the sequence job. You list it in the parameters tab of the job, but not using it or using it is handled in the job's stages.

I label this a design constraint. Taken in isolation, it can look like redundant coding, but in the overall design it makes sense.

[chulett]

How can I get the sequence job to carry all possible parameters needed and _not_ just the parameters available to that one selected job the Job Activity requires?

The job parameters that a Sequence carries are completely up to you. It is a separate, discrete list that may or may not have any relationship to what your Job Activity or other stages need. Typically they will line up but nothing in the Sequence will care one way or the other if a parameter isn't used somewhere. Perhaps that's where the disconnect is.

I see Franklin is making the same point. Add as many paramaters to the Sequence that will be needed by anything it needs to run. Pass them when and where appropriate.

Note that I'm talking about "out of the box" Sequence functionality without pondering how your "edit the generated code to make it dynamic" approach could monkey wrench it.