Dynamic Parameters

There are a lot of cases when you need to execute a Job by changing some of its parameters.

1. In short

For exemple, you have a Job that imports an Excel file into a database, but the filename is not the same from one month to another.

This Job is launched from the Client application by an user in your company. He obviously cannot edit your Job, but he can change the dynamic parameters.

The simplest exemple would be :

MyImportTable:SELECT * FROM {?1}.XLSX

It means that the {?1} script will be replaced by the first dynamic parameter : your user is now able to choose any filename without manipulating the Job (and, in this case, the associated query)

2. The power of the Dynamic Parameters

There are many things you can do with Dynamic Parameters

The Dynamic Parameters section

There are 3 available features :

  • Static strings : you can write anything as a parameter (ex : MyString)
  • Dynamic script : you have access to a list of keywords that will be automatically replaced (ex : %DD-%MM-%YYYY)
  • Pre-Job Command result : you can configure a Job to run a command before its execution. The returned value of that command can be used as a dynamic parameter (ex : %CS1)

2.1. Static Strings

This is especially useful when you need to add an optional column in the Source data.

  • Exemple :
Set “MyString” in the “Job Configuration” tab
Add a customized column in the “Target” tab and set the value to {?1}
Write your query in the “Job Queries” tab
Show the Result Set. “NEWCOLUMN” was added with “MyString” as a value

2.2. Keywords

This is especially useful when you need to manipulate dynamic dates in your Job.

You can pick any keyword in the list, and use it as a dynamic parameter.
They can also be concatenated in the way you want !
  • Exemple :
Writing “%YYYY-%MM-%DD” in the “Job Configuration” section will be replaced by the actual year, month, and day, as you can see just below the textbox.
It means that you won’t need to change the parameter everytime you run the Job.
A query using the Dynamic Parameter as a filter.
Show the Result Set. “dt_random_date” as been filtered according to the Dynamic Parameter.

2.3. Pre-Job Command result

This is especially useful to filter a query in a “Database to Mail” scenario.

  • Exemple :
Writing “%CS1” in the “Job Configuration” section means that you want that parameter to be the first”Source Pre-Command” result.
Hint : Setting a pre-command is optional and can be defined on the “Source” tab.
The “Source” tab has a reserved section for optional pre and post-Job commands.
– If the Source is an SQL database, the accepted inputs are SQL commands.
– If the Source is a file, any Windows Shell commands are accepted.
A query using the Dynamic Parameter as a filter.
In that case, {?1} will be replaced by the result from :
SELECT MAX(id_sample) FROM sample_table_3;
Show the Result Set. “id_sample” as been filtered according to the Dynamic Parameter.

en_USEnglish
en_USEnglish fr_FRFrench