JavaScript Parameters

Why parameterize tasks and components?

SSIS tasks and components usually take as input a set of values (or objects) and apply some custom logic to achieve a goal. Separating the inputs from the processing logic provides several benefits:

• Gain extra clarity about the script inputs and their type
• Enable script reuse - within an SSIS package or even across an entire SSIS project.

Without parameters, reusing the script logic and feeding in different inputs can only be achieved by relying on SSIS variables. This often requires a separate step to initialize their values before calling each instance of the task/component that uses this script. Although feasible, this approach makes SSIS packages harder to maintain and also has some limitations (e.g. executing in parallel several instances of the script with different inputs).

JavaScript Task and JavaScript Component offer a superior approach, by enabling script parameterization with an easy-to-use UI. The list of available parameters are defined in the JavaScript code. In design-time they appear within the “Parameters” tab of the task/component editor. There the parameters values are managed in a standard Property Grid, with an assistive UI which enables entering values, selecting items (variables, connections, etc.) from a list and more. You are also able to specify validation rules, so that the task/component can provide an immediate feedback if the inputs are invalid.

---

Defining parameters

Parameters are specified by initializing the parameters property of the task or component like this:

The list of all available parameter properties are:

Parameter description

Description allows providing an extra information displayed to the user for documentation purposes.

Parameter type

The parameter type determines what editor would be used in the "Parameters" Property Grid for specifying the value. The supported types, grouped by category, are:

• Primitive types: string, integer, numeric, boolean, date
• Custom editors: list, text-editor, regex, guid
• Files and folders: file-local, folder-local, file-remote, folder-remote
• SSIS common entities: variable, connection, task, component
• SSIS Component entities: input, output, column

NOTE: Some types (e.g. the file-related ones) allow parameterizing the editor with additional settings. In this case, the type is represented by an object: type: { id: "connection", connectionTypes: ["SFTP", "SSH"] }

Parameter value

The value that's initially specified to value is the default value. The user can change it later, without modifying the script.

With the script logic, the user-specified value is accessed via the value property of the parameter (e.g. parameters.MyParameter.value). It's available both in the validation and execution phases.

Validating values

The validate function is invoked whenever the user changes the parameter's value. It is invoked also when the script is recompiled - on Editor opening, when Editor tab is changed, when OK button is pressed. To indicate that the value is invalid, the function needs to return a string with the error message text. In validation.js file there are several functions provided (e.g. validation.required or validation.greaterThan) for handling common validation scenarios .

Reacting to changes in parameter value

The afterEdit function allows plugging in logic that handles a change of a parameter value. It is invoked only when the user changes the parameter's value. It's useful e.g. for updating component metadata in the JavaScript Component

Grouping and sorting parameters

Category is used for grouping parameters in the Parameters tab. A parameter is always displayed under a particular category. If category is not specified, the parameter would go to the default Misc group. Groups are displayed in an alphabetical order.

Parameters can be sorted within their group by using the property order. The allowed value are integers (negative numbers are also accepted). It's recommended not to have equal values within same group, as the paramters order would be random. If no order is specified explicitly, the name of the parameter would be used for sorting. Parameters without order are positioned after those that have it specified.

Optional parameters

Parameters that are marked as optional (via the optional property) are not required to have a value and by default they are not displayed in the Parameters tab. To make them visible, the user needs to click the Expand Options button.

Disabling parameters

Parameters are enabled by default, but can be disabled via the enabled property. Disabled parameters are displayed as read-only. The enabled property can have an initial false value or it can have an assigned function to calculate the enabled state.

Disabled parameters are used in the following scenarios:

• Permanently disabled parameter - used for displaying read-only calculated values (the value can be recalculated e.g. in validate function).
• Initially disabled parameter - can get enabled later
• Conditionally disabled parameter - the enabled value is a function that decides whether or not to make it readonly, based on other parameters' values.

Hiding parameters

Parameters are visible by default, but can be hidden via the visible property. The visible property can have an initial false value or it can have an assigned function to calculate the visible state.

Invisible parameters are used in the following scenarios:

• Initially invisible parameter - can become visible later (e.g. in a validate function call).
• Conditionally invisible parameter - the visible value is a function that decides whether or not to display it, based on other parameters' values.

Defining dependencies between parameters

A parameter can be configured to depend on other parameter(s). Such a dependency means that the current parameter will be disabled until all its prerequisite parameters have a value specified. Changing the value of a prerequisite parameter would lead to resetting the value of all its dependent parameters.

The property depends is used for defining such dependencies. Its value can be a string (for a single prerequisite parameter), an array of strings (for multiple prerequisite parameters) or a function returning string or array of strings (for dynamically changing prerequisite parameters). Parameters which require a connection (e.g. file-remote) are considered automatically dependent on the corresponding property.

Shorthand definition of parameters

All the properties of parameters are optional. The quickest way to define parameters is by specifying only their name and initial value (assuming the type can be inferred from the value):

Shorthand parameters can be only of type string, integer, numeric, boolean or date. This syntax doesn't allow specifying a description, nor a category, so the parameters would end up in the default Misc group.

---

Parameters with custom selectors/editors (common)

Selection from a List of Values

Parameters can specify a list of allowed values via:

• Providing a fixed list of values (e.g. values: ["value 1", "value 2"])
• Providing a function which returns a list of values. (e.g. values: function() { return ["value 1", "value 2"] }). NOTE: The function is invoked within the context of the main object (task or component), i.e. their this is set to this object.

When a parameter type is list, the user will be able to choose a value (or optionally multiple values) from a dropdown list:

• The values of a list must be of the same type. Only primitive types are allowed: string, integer, numeric, boolean or date.
• In multiple-selection lists the returned value is always a string with comma-delimited values (i.e. even if the allowed values are integers, the selected ones would still be converted to strings). The delimiter is the string ",\n" (comma followed by the line feed), it allows usage of comma in values.

Here is an example of a function that retrieves the list of allowed values from a DB table (via an ADO.NET Connection):

NOTE: In the example above, the parameter Database will remain disabled until a connection manager is selected into the parameter ADONETConnection (due to the specified depends property). When the value of the master parameter ( i.e. ADONETConnection ) is changed, the values of all parameters depending on it would be cleared.

Selection of a Variable

When the parameter value is expected to be stored in a SSIS variable, you can use the variable parameter type, which allows easy selection of a variable from a dropdown list. The name of the selected variable would be stored in the parameter value.

Supported variable types are string (any text SSIS data type), integer (any integer SSIS data type), numeric (any floating SSIS data type), boolean, date and object.

If variableTypes is not specified, all available system and user variables will be listed. For that scenario, you can define the parameter using a simpler syntax:

Selection of a Connection Manager

You can use connection type for selecting an available Connection Manager(s) in the package:

Tip: The IDs of the connection types can be seen in the Visual Studio "Add SSIS Connection Manager" dialog. The values in the column "Type" are the allowed values for the property connectionTypes.

Text Editor (XML, SQL, JSON)

For parameters which value is expected to contain text that can't be comfortably displayed in a single line (e.g. an XML fragment or an SQL query), there is a syntax highlighting-capable editor provided via the text-editor type, that supports XML, SQL, JSON, etc.

If such a parameter has avalidate function, it will be executed upon pressing the Text Editor's OK button. Unlike other parameters, where the validation is executed after the value is set, this one allows catching invalid values early on to prevent applying them.

Regular Expression Editor

For parameters of regex type, there is available a RegEx editor that allows testing the regular expression.

Guid Editor

For parameters of guid type, there is inplace editor available which performs Guid validation.

Selection of Task/Component

Based on the provided in SSIS+ Package Connection there are special parameter types (task and component) that support selecting tasks or components from the SSIS package:

The connection property is providing the information which other parameter (of type PackageConnection) should be used, and also implies dependency, thus the depends property given afterwards is redundant. As with other properties, connection can be a function which returns a string. Arrays cannot be used as value for this parameter.

The properties taskType or componentType are optional. If the type is not specified, all tasks/components in a package will be available for selection.

Filtering by component type is do by searching componentType in the component property UserComponentTypeName (many components don't have such property) and in the property IdentificationString.

Filtering by task type is done by searching taskType in the .NET type name of the task and in the task Name property.

Selection of Local File(s) and Folder

Selecting a file from the local file system is specified via the file-local type:

• Property multiple is optional, if omitted single selection will be made.
• Property filter is optional, if omitted no filter will be used.

When none of the optional properties are used, the following shorthand syntax can be used:

Selecting a folder from the local file system is specified via the folder-local type

Selection of Remote File or Folder

To select remote files (file-remote) or remote folder (folder-remote), you would need a remote connection. "SFTP" and "SSH" connections can be used here (the implementation relies on having a connect method that returns an object implementing CozyRoc.SqlServer.SSIS.IFtpClient).

Filtering of Local and Remote Files

Since SSIS+ version 2.0, you can specify a filter for local files (file-filter-local) or remote files (file-filter-remote) via the following syntax:

---

JavaScript Component-specific parameters

Selection of an input

A parameter of type input allows selection of one of the component inputs:

Selection of an output

A parameter of type output allows selection of one of the component outputs. The selection is made from all regular or all error outputs of the component output collection. Whether the outputs include error outputs is determined from the property all, default value is false, which means only regular outputs are listed. Otherwise all outputs are listed - regular and error.

Selection of input or output columns

A parameter of type column allows selection of input or output columns.

The property columnType has the following possible values:

• For input columns: "all" - all columns from the parent output, "selected" - selected columns for read-only or read-write use, "external" - external metadata columns.
• For output columns: "all" - all configured columns into given output, "external" - external metadata columns.

The property containerName is optional. If omitted, the first input or output will be used.

The property multiple is optional. If omitted, only single selection will be allowed.

An example for selecting a virtual column:

After a virtual column is selected it should be marked as being used. This easily can be done in the afterEdit function. In the example above, the selected virtual columns are marked for read-only use, the non-selected are marked as ignored.

An example for selecting an input column:

An example for selecting an output column:

Selection of external columns

When external is specified for columnType, first you need to call externalMetadataColumnCollection.isUsed = true for the desired input or output (usually in afterEdit function) like this:

The creation of external metadata columns can be done in the reinitializeMetadata function, too.

External input column:

External output column:

Using default value with component parameters

Component parameters can have default value - a name of an input, an output or a column.

The default value can be used when multiple selection is allowed. In this case, the default value will be indicated only when a single selection is made. Also when the selection is cleared, the parameter value is reset to the default value.

---

Validation

Two levels of parameter validations are supported (local and global):

Single parameter validation

Parameters can have a validation function assigned to the validate property. Its main purpose is to verify if the value is valid, and if not - to return an error message. When the value is valid, the function should return nothing.

Besides the validation, this function can be also used to change the value, enabled and visible properties of any parameter, but such a usage is not recommended.

The validation function has a single parameter, containing the current value of the parameter. Within the function this keyword refers to the corresponding task or component object.

Examples of validating a value:

In the following script the parameters FileVariable and File are hidden, based on the value of the IsFileVariable.

Global validation (validating values of multiple parameters)

Whenever you need to verify the values of more than one parameter at once, you can use a validate function to do so. The so-called global validation will be executed in multiple occasions:

• during the Validation phase of the task/component
• after opening of the Editor
• after changing the value of any parameter

The global validation is executed after the individual parameter validations. Similarly to the individual validation, it should return a string if an error is present. Within the function this keyword refers to the corresponding task or component object.