JavaScript API

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)