Overview
Derived Column Plus is a powerful data flow transformation used for creating new columns or modifying existing ones. It is an alternative of Microsoft Derived Column. Provides the capability to derive new column values based on a rich set of JavaScript expressions, perform calculations, manipulate strings, format dates, perform logical operations, and transform data in various ways. An expression is a mix of columns, operators, functions, literal values, and variables that yield a single output value. Derived Column Plus transformation can define multiple derived columns, and any variable or input columns can appear in multiple expressions. Its flexibility, features and capabilities empower you to meet diverse and challenging data transformation requirements.
Advantages
- Expands the features, flexibility and functionality of the built-in Microsoft SSIS Derived Column.
- Flexible JavaScript expressions editing experience within an embedded JavaScript editor.
- Convenient real-time expression evaluator.
- Provides a rich set of predefined JavaScript functions.
- Additional JavaScript custom functions can be added in an easy way.
Derived Column Plus component dialog
The component dialog contains the following tabs:
Derived Columns tab
This tab is the container where the derived columns are defined. It contains the following numbered sections:
- Derived column definitions
- JavaScript expression editor
- Input columns / variables / predefined JavaScript functions tree
- Sample parameter values
- Real-time expression evaluator showing the result, as you type.
The JavaScript expression of a column can be build in the embedded JavaScript editor (See Section 2). The expressions can contain any combination of variables, functions, operators, and columns from the transformation input. They can be dragged from the tree located on the right side of the editor (See Section 3). It provides a rich set of predefined functions with their descriptions available. Function types:
- Mathematical Functions
- String Functions
- Date/Time Functions
- Type Casts
- Other Functions
- User Defined Functions (available if custom JavaScript functions are defined in the Script tab)
Parameters from the expression are detected while building it (See Section 4). You can define sample parameters values if needed. There is a powerful real time expression evaluator below that evaluates the expression during its construction (See Section 5). The evaluation result is based on the sample parameters values. If its data type does not match the derived column data type, an error message will be shown.
Script tab
The tab displays the JavaScript script code and derived columns definitions. You may define custom JavaScript functions to use them in building derived column expression. They will be available in Functions tree on the right side of the JavaScript editor.
It is recommended to edit the columns definitions in Derived Columns tab.
Error Output tab
Here you can specify how errors are handled by the component. Errors can fail the component, or they can be ignored, or they can be redirected to an error output.
JavaScript Expression Examples
Here you can find some useful JavaScript expression examples that are supported by Derived Column Plus.
| Example | Description |
|---|---|
| capitalize( "i love cats and dogs." ) | Capitalizes the first letter of a string. |
| clean( " I love cats and dogs ." ) | Replaces any consecutive whitespace characters (two or more spaces, tabs, or newlines) with a single space. |
| "I love cats ".concat( "and dogs." ) | Combines the text of two strings and returns a new string. |
| dasherize( "I love cats and dogs." ) | Replaces uppercase letters with a dash, replaces consecutive dashes, underscores and whitespace characters with a single dash, and converts the string to lowercase. |
| decodeBase64ASCII( "SSBsb3ZlIGNhdHMgYW5kIGRvZ3Mu") | Decodes a string value that has been encoded in Base64 ASCII format. |
| encodeBase64ASCII( "I love cats and dogs.") | Encodes a string value in Base64 ASCII format. |
| escapeRegExp( "I love *cats* and *dogs*." ) | Escapes any special characters within the input string that have special meaning in regular expressions. |
| fireError("An error occured") | Writes an error message to the SSIS log and returns the message. |
| fireInformation("For your information") | Writes an information message to the SSIS log and returns the message. |
| fireWarning("Warning") | Writes a warning message to the SSIS log and returns the message. |
| getFileContent("D:\\test_log.txt") | Returns file content according to a given file path. |
| getFileLinesCount("D:\\test_log.txt") | Returns file lines count according to a given file path. |
| getIndent( " I love cats and dogs.") | Determines the minimum indentation level present in the string. |
| getValueFromJsonString( [User::JsonString], "address.city") | Returns value from json string based on a given json path. Sample value for [User::JsonString]: {"name": "Max", "age": 38, "address": {"city": "New York", "country": "USA"}} |
| getValueFromXmlString( "<root><employee><name>Max</name><age>38</age></employee></root>", "/root/employee/name") | Returns value from xml string based on a given xpath. |
| getSearchCount("I love cats and dogs. I have a dog.", "dog") | Returns count of occurrences of a word in a string. |
| "I love cats and dogs".includes( "dogs", 10 ) | Performs a case-sensitive search to determine whether one string may be found within another string, returning true or false as appropriate. |
| incrementalValue(0,1) | Returns an incremental value starting at a given number and incrementing by a given step for each row. |
| insert( "I love dogs.", 7, "cats and" ) | Modifies the input string by inserting the new value at a specified position. |
| "I love {cats and dogs}".match( "\{([^}]+)\}" ) | Used to match a regular expression against a string. |
| padString("I love cats and dogs.", 30, " ", "right") | Pads a string with a specified value to achieve a desired length. The padding can be applied on the left, right, or both sides of the string based on the type parameter ("left", "right" or "both"). |
| "I love cats and dogs.".replace("cats and dogs", "nature" ) | Finds a match between a regular expression and a string, and to replace the matched substring with a new substring. |
| replaceValueInJsonString( [User::JsonString], "address.city", "Boston") | Replaces all values in json string based on a given json path and returns the modified json string. Sample value for [User::JsonString]: {"name": "Max", "age": 38, "address": {"city": "New York", "country": "USA"}} |
| replaceValueInXmlString( "<root><employee><name>Max</name><age>38</age></employee></root>", "/root/employee/name", "John") | Replaces all values in xml string based on a given xpath and returns the modified xml string. |
| reverseString("Boston") | Reverses the characters in a given string. |
| "I love cats and dogs".slice( 4, 10 ) | Extracts a section of a string and returns a new string. |
| stringLeft("John, Max", ",") | Extracts the portion of a string that appears before a specified separator. |
| stringMatch("dogs", "dogs") | Checks if a given string matches any of the provided matchers. The matchers can be regular expressions or strings, and the matching is case-insensitive. The function returns true if there is a match and undefined otherwise. |
| stringRight("John, Max", ",") | Extracts the portion of a string that appears after a specified separator. |
| "I love cats and dogs".substr( 0, 15 ) | Returns the characters in a string beginning at the specified location through the specified number of characters. |
| swapCase("I love cats and dogs.") | Converts uppercase to lowercase and vice versa of characters in a string. |
| titleize("I love cats and dogs.") | Converts a string into title case by capitalizing the first character of each word. |
| truncate("I love cats and dogs.", 7, "nature") | Truncates the string to a specified length. If the string is longer than the specified length, it adds a truncate string at the end. |
| urlDecode("http%3a%2f%2fwww.cozyroc.com%2f") | Converts a string that has been encoded for transmission in a URL into a decoded string. |
| urlEncode("http://www.cozyroc.com/") | Encodes a URL string. |
Demonstration
Quick Start
In this Quick Start article, we will show you step by step in How-To create a simple package that uses Derived Column Plus transformation to create a new column and to modify an existing one. We will use the OLE DB Source component in the Data Flow to read the data from a table and write it to a different table using the OLE DB Destination component.
Setup Data Flow – OLE DB Source Component
- Click the Data Flow tab.
- Add the OLE DB Source component from the SSIS Toolbox, by dragging the component to the design surface.
- Double-Click on the OLE DB Source to open the Editor, configure the connection manager and select the source table from the list.
- Click OK button.
(See Figure-01)
Setup Data Flow – Derived Column Plus component
- Add the Derived Column Plus component from the SSIS Toolbox, by dragging the component to the design surface.
- Drag the connector arrow from OLE DB Source to the Derived Column Plus component.
- Double-Click on the Derived Column Plus to open the Editor.
- Create new derived column that replaces an existing input column:
4.1. Select "Add" button. In "Derived Column" drop down list select the existing input column. The derived column will have the same properties including Data Type, Length, Precision, Scale, Code Page and they cannot be edited.
4.2. Construct the derived column expression in JavaScript editor. The expression can contain any combination of variables, functions, operators, and columns from the transformation input. You can drag-drop them from the tree on the right side of the editor. Search functionality is provided for convenience.
4.3. Enter sample parameter values if needed. Real time expression evaluation is performed and can be seen in the Evaluation Result pane.
(See Figure-02)
- Create another new derived column :
5.1. Select "Add" button. In "Derived Column" drop down list select "<add as new>" value. Give Data Type, Length, Precision, Scale, Code Page properties values.
5.2. Construct the derived column expression in JavaScript editor.
(See Figure-03)
Setup Data Flow – OLE DB Destination Component
- Add the OLE DB Destination Component from the SSIS Toolbox, by dragging the component to the design surface.
- Drag the connector arrow from Derived Column Plus to the OLE DB Destination Component.
- Double-Click on the OLE DB Destination to open the Editor, configure the connection manager and select the destination table.
- Click on the Mappings page to map derived and input columns to the destination columns. (See Figure-04)
- Click the OK button to return to the Data Flow.
- Click File from Menu and Save All.
- To run the package, Click Start Debugging on the menu bar, or press F5 key.
Note: After the package finishes running, press Shift+F5 to return to design mode.
Knowledge Base
What's New
- New: Included
encodeBase64BytesanddecodeBase64Bytesfunctions to work with DT_BYTES, DT_IMAGE columns. - Fixed: Many improvements and cleanup.
- 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.