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.
Quick Start
In this quick-start we will setup two parameterizable JavaScript Components (a source and a transformation) to automate a simple scenario - generating dates and adding row counter. The source will generate rows with a date column, while the transformation will add another column with a row count.
Selecting component type
Let’s start by creating a source JavaScript Component. After dropping the component on the design surface, let’s select the "Source" radio button. This would create a component with one output and no inputs.
Add parameters in the script
Let’s open the component editor and introduce two component parameters “StartDate” and “Count” by assigning them to component.parameters. We will specify for them description, type and validation. For validation we will use the library validation.js where are defined the most frequently used validations.
View the parameters in the "Parameters" tab
After you switch to the "Parameters" tab you would see the two parameters in the property grid there. Until you specify values for them, there would be some validation error messages that prompt you to do so.
Specify parameters values
Now specify values for the Count and StartDate parameters.
Output column creation
Let’s create the output and the output column. As the columns are static, they will be created in component.reinitializeMetadata function. We need one output and one column of type DT_DATE. We make definition of the output and the output column. Helper functions will remove current when they are different and create new. To use helper functions the library component.metadata.js needs to be included.
Implement the main script logic
The main logic needs to be implemented in the function component.processData(inputBuffer, outputs). With this step, the setup of the source component is complete.
The Transformation Component
The next JavaScript Component in our scenario is a transformation. After dropping the component on the design surface, let’s keep the default "Transformation" radio button and then connect it with the source component.
Add parameter in the script
Let’s introduce the parameter "CounterStart" - initial value of the rows counter. We will specify “description”, “type” and “validation” for it.
Specify parameter value
Let’s start our row counter from 100.
Selecting input column
We need to define input and select the input column. This is done via code in component.reinitializeMetadata function.
Output column creation
Let’s create the RowCount column. Again we will use helper functions from the component.metadata.js library.
Implement the main script logic
The last step of the component setup is implementing component.processData(inputBuffer, outputs).
Summary
In summary, we have demonstrated how to setup two simple JavaScript Components: a source (having only an output) and a transformation (having an input and a synchronous output). Unlike the standard Script Component, please note, that both of them use a single method for processing the data component.processData(inputBuffer,outputs).
Knowledge Base
- Where can I find the documentation for the JavaScript Component?
- COZYROC JavaScript component: Unable to type in KeyAttribute or ValueAttribute
- Where can I find scripts for the JavaScript Task and the JavaScript Component?
- Microsoft Dataverse - A Template for Close Opportunities as WON or LOST
- How to configure the Javascript Task or JavaScript Component
What's New
- New: Commands to quickly Copy/Paste user parameters.
- Fixed: Visual Studio crash after initial script selection.
- New: Introduced component.
Related documentation
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.