JavaScript

Overview

JavaScript Task is an SSIS Control Flow Task that allows automation of any custom logic in the control flow. It is an alternative of Microsoft Script Task and COZYROC Script Task Plus. It is also a more flexible replacement of Microsoft Expression Task.

By choosing JavaScript for implementing custom control flow logic, you will significantly simplify the development and maintenance of your SSIS scripts. For more details about the benefits of JavaScript scripting, please look here.

Editor

The Editor Dialog contains the following tabs:

Parameters tab

The Parameters tab displays the parameters, defined in the script, and their current values. For more detailed information about defining and using script parameters see here.

Properties tab

Referencing code from another JavaScript Task

To avoid code duplication, the code of a JavaScript Task can be stored in another JavaScript Task. The properties under the Linked Task category allow referencing the source JavaScript Task:

• PackageConnection - allows choosing the package, where the source JavaScript Task is (i.e. the current package or an external one).
• JavaScriptTask - allows selecting the source JavaScript Task in the selected package.

If you have specified a source JavaScript Task, you can unlink it later by clicking the button Remove link below.

Customizing the execution and validation logic outside the script code

Under the Script category there are two properties:

• Expression - specifies JavaScript expression that will be executed. If not specified, task.run would be run.
• Validation - specifies JavaScript expression that will be used to validate parameters. If not specified, task.validate would be used.

Usually, the execution expression is defined in the script code by implementingtask.run. But having a separate Expression property provides additional opportunities for code reuse - e.g. two JavaScript Tasks might share a script, but they can execute different logic by configuring different expressions to run.

Script tab

The tab displays the JavaScript script code. It allows editing and adding breakpoints. The embedded editor is the one that comes with the particular version of Visual Studio. For detailed information how to automate SSIS tasks with JavaScript, please refer to the API Reference.

At the bottom of the dialog, there are two buttons that make it convenient to load the JavaScript code from a file and save the code to a file.

Overview

JavaScript Component enables automating of any custom logic in the data flow. It is an alternative of Microsoft Script Component and COZYROC Script Component Plus. You might also consider using it instead of Microsoft Derived Column Transformation if you need to deal with more complex expressions.

By choosing JavaScript for implementing custom data flow logic, you will significantly simplify the development and maintenance of your SSIS scripts. For more details about the benefits of JavaScript scripting, please look here.

Source, Destination or Transformation

The JavaScript Component can be used as a source, a destination or a transformation:

• When used as source, the component doesn't have any inputs
• When used as destination, the component doesn't have any outputs
• When used as transformation, the component can have one or more inputs and one or more outputs

The JavaScript Component is more flexible than the standard Script Component, as it:

• Can define more than one input
• Can define error outputs
• Supports columns with the same names in an input and its synchronous output.

Advanced features for configuring metadata

Configuring metadata via code

The component metadata can be intliialized and changed in component.reinitializeMetadata function. It is invoked in several occasions: when the component needs new metadata, when the active tab in the Editor is changed, and when the "OK" button is clicked. Because of the multiple invocations, the code needs to check if the metadata already contains the desired changes (e.g. check if an input is already added and not to try to add it again).

Input columns mappings

The Editor's "Mappings" tab, which allows specifying mappings between virtual and external column, is hidden by default. It can be shown via code: component.showMappings = true;. The external column cannot be created by the Editor, they must be created from the script. If the external columns do not depend on parameters, they should be created in component.reinitializeMetadata function. Otherwise they should be created in the component.validate function.

Parameterizing metadata changes

JavaScript Component supports parameter types which allow selection of an input, an input columns, an output, an output columns (see here) from the "Parameters" tab. Then in the component.validate function, the component metadata can be modified, according the specified parameter values.

For example, a transformation script can have a parameter for selecting input virtual columns and after some conversion to output the values in an asynchronous output. Based on user selection, in the component.validate function the selected columns can be marked as used and to create matching output columns.

Editor

The Editor Dialog contains the following tabs:

Parameters tab

The Parameters tab displays the parameters, defined in the script, and their current values. For more detailed information about defining and using script parameters see here.

Properties tab

Referencing code from another JavaScript Component

To avoid code duplication, the code of a JavaScript Component can be stored in another JavaScript Component. The properties under the Linked Component category allow referencing the source JavaScript Component:

• PackageConnection - allows choosing the package, where the source JavaScript Component is (i.e. the current package or an external one).
• JavaScriptComponent - allows selecting the source JavaScript Component in the selected package.

If you have specified a source JavaScript Component, you can unlink it later by clicking the button Remove link below.

Displaying information about component metadata

This page displays information about important component properties, its inputs and outputs.

Script tab

The tab displays the JavaScript script code. It allows editing and adding breakpoints. The embedded editor is the one that comes with the particular version of Visual Studio. For detailed information how to create SSIS data flow scripts with JavaScript, please refer to the scripting reference.

At the bottom of the dialog, there are two buttons that make it convenient to load the JavaScript code from a file and save the code to a file.

Mappings tab

This tab defines mappings between external input columns and input columns. It's hidden by default and can be shown by script code.

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.

This programming reference covers various aspects of how to use JavaScript to automate SSIS-related tasks. While some of the content is specific to JavaScript Task or JavaScript Component, most of it is applicable in the context of any JavaScript script.

Global scope objects and methods

Within the JavaScript code there are several objects and methods that can be used anywhere:

Global scope objects

JavaScript Task defines a task object and JavaScript Component defines a component object, which are available in the global scope of the corresponding script.

Besides this main object, there are several objects with convenience methods:

• console = {...}; - it supports all standard methods of the console object + offering additional SSIS-specific methods like progress.
• env = {...}; - used for getting and setting values of Environment Variables

Global scope functions

The following functions can be used anywhere in script code:

• require = function(<additional_file_to_import>) { ; } - imports a JavaScript module from a file located under <COZYROC SSIS+ install folder>\SSIS\JS\ (there are several popular JS files, already available there)
• alert = function(<message_box_content>) { ; } - when in interactive mode, displays a message box with some text. In non-interactive mode, outputs the message to the execution log.
• sleep = function (<delay_in_milliseconds>) { ; } - pauses the script for the specified number of milliseconds.
• getClrType = function (<fully_qualified_CLR_type_name>) { ; } - gets a .NET type (more details below)

Accessing Parameters, Variables and Connections

The following properties are accessible for both JavaScript Task (via its task object) and JavaScript Component (via its component object):

Script Parameters

The script parameters are exposed via the parameters property of the task/component. You can access them like this: component.parameters.MyParameter

For more details why and how to use parameters, see here.

SSIS Variables and Parameters

All SSIS Variables (User and System) and Parameters (Project and Package) are exposed via the variables property of the task/component, which is a wrapper around a VariableDispenser object. Variables are accessible only during the execution stage of the task / component.

If there are no variables/parameters with duplicated names, a short form can be used like this:

Changing a value is allowed only for the User Variables. System Variables, Project and Package Parameters are read-only. Attempting to set their value will throw an exception.

When there are duplicate names, the long form (with ::) should be used:

Each variable is locked on-the-fly for read or for read/write, depending on the access of the .value property. However, there are the following two functions exposed for a variable, which provide fine-grained control over locking and unlocking: lockShared() and lockExclusive(). They can be used like this:

More about variables locking:

• Explicit locking is used to speed up execution when a variable is used often within the script. Please note that locking a variable or a parameter is a relatively slow operation.
• The locking/unlocking mechanism is counter based, so on-the-fly locking/unlocking which happens inside .value will not result in additional underlying lock/unlock invocations, if an explicit call to lockShared() (or lockExclusive()) has been made already.
• If you've used lockShared() on a variable and try to write in it, it'll succeed. However, as the name suggests, there is no guarantee for not having race conditions. If you want an exclusive lock just for this task/component, then use lockExclusive(). Calling the latter function after another lock has been already made (i.e. call to lockShared() or lockExclusive()) will result in throwing a dead-lock prevention exception.
• If a variable is locked exclusively and another task/component is trying to lock it, locking will wait until the exclusive lock is released.
• Explicit unlocking is optional, as all locked variables will be automatically unlocked after the execution of the script.

Various properties/methods for access to the underlying Microsoft.SqlServer.Dts.Runtime.Variable object are available like readOnly, description, etc.

SSIS Connection Managers

Scripts can use SSIS Connection Managers both during the global scope execution and during expressions execution.

The currently supported Connection Managers include:

• Standard: ADO.NET, HTTP, FTP, FILE, FLATFILE
• COZYROC SSIS+: SFTP, SSH, REST, PACKAGE, EXCHANGE, IMAP
• Any other Connection Manager that is returning a string value as a result of calling AcquireConnection (just like FILE, FLATFILE )

Example of using an ADO.NET connection:

The result from a query is an array of objects. Array elements correspond to a table row, i.e. each row is represented by an object. The property names on this object start from 0 for the first column, and end with count of columns minus one.

A field from the query result can be accessed like this:

Example of using a REST connection:

Example of using a FILE connection:

FILE Connection Manager returns file name, so this connection doesn't needs closing. The file can be opened or closed.

.NET Interoperability

Using .NET types

The script can access any .NET types by using its type name, including namespace or assembly qualified name. The function getClrTypeis used like this:

Calling a static method:

Creating an instance:

Creating a generic instance:

Once you have a reference to an object, getting and setting the properties is done like this:

There are a few limitations/gotchas to be aware of, related to calling .NET methods:

• Out and Refarguments are not supported.
• As JavaScript can convert automatically from a given type to another this might lead to an ambiguous call issues. For example, a C# class is having two methods with the same name, one has a double parameter, the other has float. If the method is called using integer value, it would be ambiguous and an exception will be thrown.

Implementing .NET Interfaces

The script can create an .NET interfaces implementations. The function implementClrInterfaces is used like this:

The object DataReaderEnumeratorImpl contains the interface implementations. All calls to interface methods or properties are redirected to this class. It's not required for all interface members to be defined. When an undefined member is used, an 'NotImplementedException' is thrown. A JavaScript object can implement more than one interface, like in the above example.

The object DataReaderEnumeratorImpl is from the Data Flow Enumerator Task script and looks like this:

The methods and properties of the JS object should have same names as the methods and properties of the .NET interface.

COM Interoperability

The script can create and use COM objects instances. Only COM objects implementing IDispatch can be used. Events are supported, the connection point interface should also implement the IDispatch interface.

The COM object type is loaded using getCOMType function, like this:

The function getCOMType accepts two parameters. The first one is mandatory and specify the COM object "Prog ID". The second parameter is the object locale and is optional:

The instance is created as usual:

Next the COM object is used like any other JS object:

Methods of the COM object are invoked like any other JS function. The COM object methods cannot be assigned to variables, though. The following is NOT allowed:

Proper calling is this:

Usage of regular properties:

Default indexer properties with numeric index can be used with square brackets:

Named indexer properties, indexer properties with more than one indexer and indexer properties with non-numeric index are accessed via functions. When getting a property value, the optional prefix "get_" can be used, but direct usage of the property name is also supported.

Using property getter:

When setting property the prefix "set_" is mandatory:

Event handlers can be attached by using "add_" prefix before the event name:

Only one handler for an event can be attached.

Removal of the handler is done by using "remove_" prefix before the event name:

The COM object can be released any time by calling the release function:

All events handlers are removed before release.

A COM object instance has one special function, called changeLocale to modify the locale of the object. This function is useful when the COM type is not obtained via getCOMType.

JavaScript Task Execution

By default, the script execution starts with the task.run method, but you can specify another function (or any valid JavaScript statement) in the task "Expression" property.

All SSIS task need to set an ExecutionResult and JavaScript Task can return one the values below:

Explicitly returning a ScriptResults in your scripts (in task.run or in the Task Expression) is rarely necessary:

• If the script completes normally, then DTSExecResult.Success value is assumed.
• If the script execution is aborted due to an exception a DTSExecResult.Failure is assumed.
• The last two values that correspond to DTSExecResult.Completion or DTSExecResult.Canceled are to be used only in scripts with very special requirements.

In addition, any value returned by task.run (not necessarily ScriptResults) will be saved in the task's "ExecutionValue". This would allow the task "ExecValueVariable" property value to be used by downstream logic.

JavaScript Component Execution

Processing input and output buffers

The component.processData function is responsible for processing buffers of inputs and outputs.

The function has two arguments:

• inputBuffer - the currently processed input buffer (if any)
• outputs - an object, which can contain multiple output buffers. A particular output buffer can be retrieved by the name of the corresponding output (e.g. outputs["Output 0"]) or via the properties sync or async, which return arrays. The sync array contains all output buffers which are synchronous with the current input. The async array contains all asynchronous output buffers.

The component.processData function can be called multiple times per execution:

• When at least one asynchronous output is present, the first call of component.processData will be only with the asynchronous outputs and the inputBuffer parameter will have a null value.
• Thencomponent.processDatais called once for each input. If this input has synchronous outputs, they will be present in the output object. The asynchronous outputs are always present in the outputs object.

Columns values can be get and set by using the buffer functions get and set:

• The get function has one argument which is the original index of the column or the column name: inputBuffer.get(0) or inputBuffer.get("RowId").
• The set function has two arguments, the first is the original index of the column or its name, the second is the new value: outputBuffer.set(1, "1st Avenue") or outputBuffer.set("Street name", "1st Avenue").

A simple example of processing a synchronous output:

A simple example of processing an asynchronous output:

Pre and post execute

The two methods would be called before and after the calls to component.processData:

• component.preExecute = function (); - executed during the component "pre execute" phase. Here you can acquire connections and lock variables.
• component.postExecute = function (); - executed during the component "post-execute" phase. Here you can release connections and unlock variables, if necessary.

Reinitializing metadata

An advanced technique is to create or modify programatically the component metadata via the component.reinitializeMetadata method. Implementing this method enables creating, deleting or altering inputs, outputs and columns.

An example with initialization of a transformation with an error output:

Metadata changes can be simplified by using the component.metadata.js library. It contain helper functions for metadata manipulations. To use the library execute following line:

Function to change inputs is syncInputs.

Synchronize SSIS component inputs with the specification in the provided inputData array. Upon success, all inputs will have the properties, specified in inputData. Extra inputs will be deleted.

The argument component is the JavaScript Component object.

The argument inputData is an array of objects with the following properties:

• name - input name (mandatory)
• description - input description (optional, default: "")
• isUsedExternalCollection - is the external metadata column collection used (optional, default: false)
• hasSideEffects - does the component performs custom actions that are not visible to the data flow engine (optional, default: false)
• errorRowDisposition - how to handle errors (optional, default: "DTSRowDisposition.RD_NotUsed")
• truncationRowDisposition - how to handle truncated values (optional, default: "DTSRowDisposition.RD_NotUsed").

Function to change outputs is syncOutputs.

Synchronize SSIS component outputs with the specification in the provided outputData array. Upon success, all outputs will have the properties, specified in outputData. Extra outputs will be deleted.

The argument component is the JavaScript Component object.

The argument outputData is an array of objects with the following properties:

• name - output name (mandatory)
• description - output description (optional, default: "")
• isErrorOut - is an error output (optional, default: false)
• hasSideEffects - does the component performs custom actions that are not visible to the data flow engine (optional, default: false)
• synchronousInput - name of an input that is synchronous to the output (optional, default: "")
• exclusionGroup - ID of an exclusion group (optional, default: 0)
• isSorted - is data in the output sorted (optional, default: false).

Function to change columns is syncColumns.

Synchronize regular or external columns of an input or an output with the provided columnData. Upon success, all columns will have the properties, specified in columnData.

Checks if the current columns in the input/output matches the specification in columnData. If not, the columns are recreated with the data in the supplied columnData.

Automatically keeps input external columns mappings when a new input is connected and an input column in the new input has the same name as an input column from the old input. Creates mappings between columns with the same name.

The argument io is either an input or an output for which to synchronize columns.

The argument columnData is an array of objects with the following properties:

• name - column name (mandatory)
• dataType - column SSIS data type (mandatory)
• length - the length of the column (optional, default: 0)
• precision - the number of digits contained in a column (optional, default: 0)
• scale - the scale of the column (optional, default: 0)
• codePage - the code page of the column (optional, default: 0)
• description - column description (optional, default: "")

The argument isExternal is a boolean, indicating which columns are synchronized - regular or external (optional, default: true for input columns, false for output).

Returns true if no difference were detected.

Function to use input columns with given names is useInputColumns.

In the specified SSIS input set READONLY usage for the columns in the "columnNames" array and remove any columns, which names are not in the array.

Handling adding or deleting of input/output paths

If you need to handle changes in paths (i.e. connections from JavaScript component to other components), the following methods are available for inserting custom logic:

• component.onInputPathAttached = function (inputID)
• component.onInputPathDetached = function (inputID)
• component.onOutputPathAttached = function (outputID)