Using the Text Merge Component
The Text Merge Component is a transformation component used to merge column and variable data into a text template. Drag and drop columns or variables onto the editor to construct the template. When the template is complete, set test values for columns that are in the template and click the preview button to view an example of the merge.
General Page
Configure general settings by specifying columns and variables to merge into a text template.
- List of Columns & Variables
-
Drag and drop columns or variables onto the editor. When the Text Merge component runs, it will replace all occurrences of columns and variables with their value. The merged text is sent to the output column specified by the Output Column Name.
- Test Value
-
Below the list of columns and variables, there is a panel that displays information about the selected column or variable. When selecting a column you can specify the Test Value. When the Preview button is clicked, the editor uses the Test Values to perform a test merge and displays the result in the previewer.
- Preview
-
Selecting Preview will launch a new window using the Test Values to perform a test merge and displays the result in the previewer.
- Preview Mode
-
In the previewer, the preview mode option shows the text in its intended format. This option is strictly a design tool and does not affect runtime. The available preview modes are:
- Plain Text
- JSON
- XML
- Output Column Name
-
The name of the output column that will contain the merged text.
- Expression fx Icon
-
Click the blue fx icon to launch SSIS Expression Editor to enable dynamic updates of the property at run time.
- Generate Documentation Icon
-
Click the Generate Documentation icon to generate a Word document that describes the component's metadata including relevant mapping, and so on.
Headers & Footers Page
Configure header and footer settings along with how to merge them with the body text
- Merge Mode
-
Specify how to merge the specified Header and Footer text with the body text. There are three different Merge Modes:
- Every Row - Prepends the Header text and appends the Footer text to every record. The merged text is directed to the Default Output with the column name of that of the Output Column Name setting found on the Output & Error Handling Page
- Every Batch - Prepends the Header text and appends the Footer text every batch. The batch size is determined by the Batch Size option. The merged text is directed to the Merge Output with the column name of that of the Output Column Name setting found on the Output & Error Handling Page
- Entire Buffer - Prepends the Header text and appends the Footer text to the entire buffer. The merged text is directed to the Merge Output with the column name of that of the Output Column Name setting found on the Output & Error Handling Page
- Batch Size
-
Specifies how many records a merged record will contain when using the Every Batch Merge Mode.
- Header
-
The text to use as the header when merging. Since it is not guaranteed to process at the record level it may not contain column or variable tokens.
- Footer
-
The text to use as the footer when merging. Since it is not guaranteed to process at the record level it may not contain column or variable tokens.
Error Handling Page
The Error Handling page allows you to specify how errors should be handled when they happen.
There are three options available.
- Fail on error
- Redirect rows to error output
- Ignore error
When the Redirect rows to error output option is selected, rows that failed to be sent will be redirected to the 'Error Output' output of the Transformation Component. As indicated in the screenshot below, the blue output connection represents rows that were successfully sent, and the red 'Error Output' connection represents erroneous rows. The 'ErrorMessage' output column found in the 'Error Output' may contain the error message that was reported by the server or the component itself.
