Overview
Template Task is SSIS Control Flow task for generation of text documents like XML, EDI, HTML, CSV, etc. The setup is done in two steps:
- Definition of template. The template definition is processed by engine based on Apache Velocity project.
- Connection of template parameters to files, SSIS variables and data flow destinations.
Demonstration
Setup
Use the General page of the Template Task Editor dialog to configure the options needed to generate your text document.
- Name
-
Specify task name.
- Description
-
Specify task description.
- IsTemplateFile (1.4 SR-3)
-
Indicates whether the template layout is stored in a file. This property has the options listed in the following table.
| Value |
Description |
| True |
The template layout is stored in a file. Selecting the value displays the dynamic option TemplateFile. |
| False |
The template layout is directly specified. Selecting the value displays the dynamic option Template. |
- Template
-
Specify text template. For more information about template design, check Template Editor.
- TemplateFile (1.4 SR-3)
-
Select an existing File connection manager, or click <New connection...> to create a connection manager.
Related topics: File Connection Manager Editor
- InputEncoding
-
Specify template input encoding. For reference check .NET Encoding.GetEncodings method.
- ResultEncoding
-
Specify result encoding. For reference check .NET Encoding.GetEncodings method.
- IsResultVariable
-
Indicates whether the result file is stored in a variable. This property has the options listed in the following table.
| Value |
Description |
| True |
The result file is stored in a variable. Selecting the value displays the dynamic option ResultVariable. |
| False |
The result file is specified in a File connection manager. Selecting the value displays the dynamic option Result. |
- Result
-
Select an existing File connection manager, or click <New connection...> to create a connection manager.
Related topics: File Connection Manager Editor
- ResultVariable
-
Contains result file path or output Stream object. Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
Parameters
The list of parameters category is dynamically constructed based on your template design. For each template parameter, the task dialog will construct a pair of parameter source type and source type specific parameter selection.
- [parameter]SourceType
-
Specify parameter source type. This property has the options listed in the following table.
| Value |
Description |
| DirectInput |
Parameter source is direct input. Selecting this value displays the dynamic option [parameter]Direct |
| FileConnection |
Parameter source if file connection. Selecting this value displays the dynamic option [parameter]File. |
| Variable |
Parameter source is variable. Selecting this value displays the dynamic options [parameter]Variable. |
| Destination |
Parameter source is data flow destination. Selecting this value displays the dynamic options [parameter]Destination. |
- [parameter]Direct
-
Specify parameter value.
- [parameter]File
-
Select an existing File connection manager, or click <New connection...> to create a connection manager.
Related topics: File Connection Manager Editor
- [parameter]Variable
-
Select an existing user-defined variable, or click <New variable...> to create a variable.
Related Topics: Integration Services Variables, Add Variable
- [parameter]Destination
-
Type the data flow destination (DataReader Destination), or click the browse button (
) and locate it.
Template Editor
When you press ... button on the Template property, you enter the Template Editor Dialog.
The dialog contains syntax-highlighting editor, list box for definition of sample test data, list of global macros available for drag-and-drop and preview window.
- Sample data:
-
Specify template sample data. Each template parameter sample can be individually specified. The input can be either simple or JSON format input. Use JSON format for simulating complex structures. The sample data doesn't participate in the generation process. It is included only for testing your templates.
- Globals:
-
Select and drag-and-drop a global macro to your template definition. You can implement your own global macros by including it in VM_global_library.vm file. The file is located in the SSIS+ installation folder, under VTL sub-folder.
- Remove Newline
-
Specify to remove carriage return / line feed characters. Certain text documents like EDI do not expect to find new line character in the output. However new line can be used to make template definition more readable. Having this option allows you to continue to use new line for readability, without affecting template generation.
Sample template text to create CSV from DataReader Destination
In Template Editor dialog check 'Remove Newline' option.
#macro( main $reader )
#set( $schemaCols = $reader.Current.GetSchemaTable().Rows )
## Setup header.
#set( $comma = false )
#foreach($col in $schemaCols)
#if( $comma )
,
#else
#set( $comma = true )
#end
$col.ColumnName
#end
#newline()
## Setup data.
#foreach($row in $reader)
#set( $comma = false )
#foreach($col in $schemaCols)
#if( $comma )
,
#else
#set( $comma = true )
#end
$row.get_Item($col.ColumnName)
#end
#newline()
#end
#end
#main( $reader )
Sample template text to create HTML table from ADO.NET Dataset (requires 1.4 SR-1)
#macro( main $ds )
#set( $table = $ds.Tables.get_Item(0) )
<table>
<tbody>
<tr>
#foreach($col in $table.Columns)
<th>$col.ColumnName</th>
#end
</tr>
#foreach($row in $table.Rows)
<tr>
#foreach($col in $table.Columns)
<td>$row.get_Item($col.ColumnName)</td>
#end
</tr>
#end
</tbody>
</table>
#end
#main( $ds )
Sample template text to create HTML table from ADO Recordset (requires 1.4 SR-1)
#macro( main $rs )
<table>
<tbody>
<tr>
#foreach($field in $rs.Fields)
<th>$field.Name</th>
#end
</tr>
#foreach($record in $rs)
<tr>
#foreach($field in $record)
<td>$field.Value</td>
#end
</tr>
#end
</tbody>
</table>
#end
#main( $rs )
Samples
CozyRoc
