Template Task


SQL Server 2005, 2008, 2008R2, 2012, 2014

SQL Server 2005, 2008, 2008R2, 2012, 2014

Looking for older releases? Check here.

Overview

The Template Task is an 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 an engine based on the 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 parameters needed to generate your text document.

Name

Specify the task name.

Description

Specify the task description.

IsTemplateFile (1.4 SR-3)

Indicate whether or not the template layout is stored in a file. This parameter has the options listed in the following table.

Option Description
True The template layout is stored in a file. Selecting this option displays the dynamic parameter TemplateFile.
False The template layout is directly specified. Selecting this option displays the dynamic parameter Template.
Template

Specify the text template. For more information about template design, see Template Editor.

TemplateFile (1.4 SR-3)

Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

InputEncoding

Specify the template input encoding. For reference check the .NET Encoding.GetEncodings method.

ResultEncoding

Specify the result encoding. For reference check .NET Encoding.GetEncodings method.

IsResultVariable

Indicate whether or not the result file is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The result file is stored in a variable. Selecting this option displays the dynamic parameter ResultVariable.
False The result file is specified in a File connection manager. Selecting this option displays the dynamic parameter Result.
Result

Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

ResultVariable

Contains the result file path or output Stream object. Select an existing user-defined variable, or click <New variable...> to create a new 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 parameter has the options listed in the following table.

Option Description
DirectInput The parameter source is direct input. Selecting this option displays the dynamic parameter [parameter]Direct
FileConnection The parameter source is file connection. Selecting this option displays the dynamic parameter [parameter]File.
Variable The parameter source is a variable. Selecting this option displays the dynamic parameter [parameter]Variable.
Destination The parameter source is a data flow destination. Selecting this option displays the dynamic parameter [parameter]Destination.
[parameter]Direct

Specify the parameter value.

[parameter]File

Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

[parameter]Variable

Select an existing user-defined variable, or click <New variable...> to create a new 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 the ... button on the Template parameter, you enter the Template Editor Dialog.

The dialog contains a syntax-highlighting editor, a list box for definition of sample test data, a list of global macros available for drag-and-drop and a 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 the VM_global_library.vm file. The file is located in the SSIS+ installation folder, under the VTL sub-folder.

Remove Newline

Use this parameter to remove carriage return / line feed characters. Certain text documents like EDI do not expect to find new line characters in the output. However, new lines can be used to make template definition more readable. Having this parameter allows you to continue to use new lines for readability, without affecting template generation.

How to access the #foreach loop counter

Use the $velocityCount variable reference to access the loop counter. The counter starts at 1.

How to format the Date

If the $dt parameter contains the DateTime structure and you want to format the date as "year-month-day", use the DateTime.ToString method like this:

$dt.ToString("yyyy-MM-dd")

Sample template text to create CSV from DataReader Destination

In the Template Editor dialog, check the 'Remove Newline' parameter. The Current object below implements the standard ADO.NET IDataReader interface.

#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 pass 2 objects to the Template Task

#macro( main $list1 $list2 )
...
...
#end
#main( $list1 $list2 )

Sample template text to create an 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 an 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