Designing independent reusable objects with variables and parameters

Objectives

After completing this lesson, you will be able to:

  • Design independent reusable objects

Variables, and Parameters

Definitions

Variables

A variable is a common component that acts as a placeholder to represent values with the potential to change each time a job is executed. To make variables easy to identify in an expression, variable names start with a dollar sign ($). They can be of any data type supported by Data Services.

A variable can either be Global or Local.

  • Global Variables
    • Set at job level
    • Available for assignment or reference in child objects
    • Can be assigned in job and/or execution properties
  • Local Variables
    • Object related variable
    • Not directly available in referenced objects
    • Can only be assigned through scripts

Parameters

A parameter is another type of placeholder that calls a variable.

This call allows the value from the variable in a job or a work flow to be passed to the parameter in a dependent work flow or data flow. Parameters are most commonly used to filter data in WHERE clauses.

  • Parameters
    • Can be input or output, one way or two way
    • Assigned on the Calls tab of Variables and Parameters
    • Calls a variable

Naming Variables and Parameters

Naming Convention

Start all names with a dollar sign ($), and use the prefixes in the table as a naming convention to ensure consistency across projects:

TypeNaming Convention
Global variable$G_
Local variable$L_
Parameter$P_

Global Variables versus Local Variables and Parameters

Local variables are restricted to the job or work flow in which they are created. You can only assign a local variable through a script. You can use parameters to pass local variables to the work flows and data flows in the object. A local variable is included as part of the definition of the work flow or data flow, and so it is portable between jobs.

Global variables are also restricted to the job in which they are created. Their value can be assigned through a script or at execution. They do not require parameters to be passed to work flows and data flows in that job. You can reference the global variable directly in expressions for any object of the job.

Caution
Be careful when using Global Variables. Your work flow or data flow using global variables become dependent on the job definition and might not be easily reused!

Global variables can simplify your work. Set values for global variables in script objects or using external job execution, or schedule properties. For example, during production, you can change values for default global variables at runtime from a job’s schedule without having to open a job in the Designer.

Variables and Parameters by Object Type

The table summarizes the variables and parameters you can create for each type of object:

ObjectTypeUsed By
JobGlobal variableAny object in the job.
JobLocal variableA script or conditional in the job.
Work flowLocal variableThis work flow or passed down to other work flows or data flows using a parameter.
Work flowParameterParent objects to pass variables. Work flows may also return variables or parameters to parent objects.
Data flowParameterA WHERE clause, column mapping, or function in the data flow. Data flows cannot return output values.

Setting Global Variables Default Value

In addition to setting a variable inside a job using a script, you can set and maintain global variable default values using job properties.

Values set in the job properties are processed the same way as those set in a script. If you set a value for the same variable both in a script and in the job properties, the value from the script overrides the value from the property.

All values defined as job properties are shown in the Properties window. By setting values outside of a script, you can rely on the Properties window for viewing values that have been set for global variables and easily edit values when testing or scheduling a job.

Setting Global Variables Using Execution Properties

You can also view and edit these default values in the Execution Properties dialog box of the Designer. This allows you to override job property values at runtime. Data Services saves values in the repository as job properties.

Let's Try It

Let me first guide you through the use of a global variable:

Now, let's see how you can make your data flow more easily reusable:

If you want to test it by yourself, go ahead:

Log in to track your progress & complete quizzes