Task

File Transfer Task

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

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

All Downloads ----- Join VIP ----- Questions?

Overview

The File Transfer Task is an SSIS Control Flow task for remote file transfer and management.

Note:If you have issues running the File Transfer Task under the SQL Server Job Agent, please check the FAQ here.

Demonstration

Setup

Use the General page of the File Transfer Task Editor dialog to configure the parameters needed to send or receive files and to manage directories and files.

Parameters

Name

Specify the task name.

Description

Specify the task description.

StopOnFailure

Specify whether or not the File Transfer Task terminates if the command fails.

Action

Specify the task action. This parameter has the options listed in the following table.

Option Description
Send files Sends files. Selecting this action displays the dynamic parameters IsLocalVariable, Local, LocalVariable, IsLocalFilterVariable, LocalFilter, LocalFilterVariable, Connection, IsRemoteVariable, Remote, RemoteVariable, OverwriteRemote.
You can enter multiple local specifications, each one separated with a vertical bar (|).
Receive files Receives files. Selecting this action displays the dynamic parameters IsLocalVariable, Local, LocalVariable, OverwriteLocal, Connection, IsRemoteVariable, Remote, RemoteVariable, IncludeSubfolders, IsRemoteFilterVariable, RemoteFilter, RemoteFilterVariable.
Create remote directory Creates a remote directory. Selecting this action displays the dynamic parameters Connection, IsRemoteVariable, Remote, RemoteVariable.
Remove remote directory Removes a remote directory. Selecting this action displays the dynamic parameters Connection, IsRemoteVariable, Remote, RemoteVariable.
Delete remote files Deletes remote files. Selecting this action displays the dynamic parameters Connection, IsRemoteVariable, Remote, RemoteVariable, IncludeSubfolders, IsRemoteFilterVariable, RemoteFilter, RemoteFilterVariable.
Rename remote file Renames a remote file. Selecting this action displays the dynamic parameters Connection, IsOldNameVariable, OldName, OldNameVariable, IsNewNameVariable, NewName, NewNameVariable.
Get remote files list Gets a list of remote files. Selecting this action displays the dynamic parameters ResultVariable, Connection, IsRemoteVariable, Remote, RemoteVariable, IncludeSubfolders, IsRemoteFilterVariable, RemoteFilter, RemoteFilterVariable.
IsLocalVariable

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

Options Description
True The local path is stored in a variable. Selecting this option displays the dynamic parameter LocalVariable.
False The local path is specified in a File connection manager. Selecting this option displays the dynamic parameter Local.
Local

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

Related topics: File Connection Manager Editor

LocalVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsLocalFilterVariable

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

Options Description
True The local filter is stored in a variable. Selecting this option displays the dynamic parameter LocalFilterVariable.
False The local filter is explicitly specified. Selecting this option displays the dynamic parameter LocalFilter.
LocalFilter

Specify local files filter criteria. The criteria is specified using FLEE expression. You can use the following elements from System.IO FileInfo class: CreationTime, Directory, DirectoryName, Extension, FullName, IsReadOnly, LastAccessTime, LastWriteTime, Length, Name.

For example, to get all zip files from the past 5 days use the following expression: LastWriteTime > Now.AddDays(-5) and Name.EndsWith(".zip")

LocalFilterVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

Connection

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

Related Topics: FTPS Connection Manager, REST Connection Manager, SharePoint Connection Manager, SSH Connection Manager

IsRemoteVariable

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

Options Description
True The remote path is stored in a variable. Selecting this option displays the dynamic parameter RemoteVariable.
False The remote path is explicitly specified on the remote server. Selecting this option displays the dynamic parameter Remote.
Remote

Select remote path on the remote server.

RemoteVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable. You can enter an array of items for a remote parameter (string or IFileInfo). The variable type must be Object.

Related Topics: Integration Services Variables, Add Variable

OverwriteRemote

Specify whether or not a remote file can be overwritten.

Remark(s):
If you are sending 0-length files, make sure this option is set to True. Otherwise the remote file will not be created.

OverwriteLocal

Specify whether or not the local file can be overwritten.

IsOldNameVariable

Indicate whether or not the old name is stored in a variable. This property has the options listed in the following table.

Options Description
True The old name is stored in a variable. Selecting this option displays the dynamic parameter OldNameVariable.
False The old name is specified in a File connection manager. Selecting this option displays the dynamic parameter OldName.
OldName

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

Related topics: File Connection Manager

OldNameVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsNewNameVariable

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

Options Description
True The new name is stored in a variable. Selecting this option displays the dynamic parameter NewNameVariable.
False The new name is specified in a File connection manager. Selecting this option displays the dynamic parameter NewName.
NewName

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

Related topics: File Connection Manager

NewNameVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

ResultVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable. Also check the ExtendedRemoteFileInfo parameter below.

Related Topics: Integration Services Variables, Add Variable

ExtendedRemoteFileInfo

Indicate whether or not ResultVariable contains a string of remote file names or array of extended file information. This parameter has the options listed in the following table.

Options Description
True The ResultVariable contains an array of IFileInfo interface objects.
False The ResultVariable contains an string of remote file names separated with a newline (\n) character.

Remark(s):
Not visible in the task user interface.

Check the following script showing how to access ExtendedRemoteFileInfo ResultVariable (you must reference the CozyRoc.SSIS library in your script project):

Imports CozyRoc.SqlServer.SSIS

...

Dim fis As Object()
Dim vars As Variables
Call Dts.VariableDispenser.LockOneForRead("ResultVar", vars)
Try
    fis = CType(vars("ResultVar").Value, Object())
Finally
    Call vars.Unlock()
End Try

Dim firstInfo As IFileInfo = CType(fis(0), IFileInfo)
IsRemoteWildcard

Indicate whether or not the remote path is a wildcard. This property has the options listed in the following table.

Options Description
True The remote path is a wildcard.
False The remote path is an explicit specification.

Remark(s):
Not visible in the task user interface.

NoFilesFail

Specify if the task should fail when the specified remote files don't exist. This parameter has the options listed in the following table.

Options Description
True The task fails if the specified remote files don't exist.
False The task ignores and completes successfully if the specified remote files don't exist.

Remark(s):
Not visible in the task user interface.

IncludeSubfolders

Specify if remote files are recursively included. This parameter has the options listed in the following table.

Options Description
True The task includes all remote files recursively, starting from the specified remote path.
False The task includes only the specified remote path files. This is the default option.
IsRemoteFilterVariable

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

Options Description
True The remote filter is stored in a variable. Selecting this option displays the dynamic parameter RemoteFilterVariable.
False The remote filter is explicitly specified. Selecting this option displays the dynamic parameter RemoteFilter.
RemoteFilter

Specify remote files filter criteria. The criteria is specified using FLEE expression. You can use the following elements from IFileInfo interface: Name, FullName, Size, ModifiedTime, IsDirectory.

For example, to get all zip files from the past 5 days, use the following expression: Modifiedtime > Now.AddDays(-5) and Name.EndsWith(".zip")

RemoteFilterVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

RemotePathSeparator

Specify remote path separator. The default separator is a forward slash (/).

Data streaming

If you use this task to stream data, set the RetainSameConnection parameter to TRUE on the connection manager. Otherwise, the connection will be closed before all the data is transferred.

Related scripts

Samples

  • For a sample showing how to process and iterate over remote file information, look at this package.
  • For a sample showing how to stream data without intermediate storage, look at this package.
  • For a sample showing how to create data in-memory in the data flow and stream without intermediate storage, look at this package.

Dynamic Data Flow

Overview

This page describes the dynamic capabilities of COZYROC Data Flow Task Plus. Data Flow Task Plus can dynamically modify source, destination and transformation (1.6) components at runtime. A more detailed information for each component is provided below.

Supported data flow components:

Type Component
Source ADO NET, Excel, Flat File, OLE DB, XML, Data Flow, EDI
Destination ADO NET (SQL 2008 and up), DataReader, Excel, Flat File, OLE DB, Recordset, SQL Server, Informix, Oracle, DB2, Data Flow.
Transformation (1.6) Aggregate, Character Map, Conditional Split, Copy Column, Data Conversion, Derived, Export Column, Fuzzy Grouping, Fuzzy Lookup, Import Column, Lookup, Lookup Plus, Merge, Merge Join, OLE DB Command, Pivot, Row Sampling, Sort, Table Difference, Template, Term Extraction, Term Lookup, Union All, Unpivot.

To enable component for dynamic processing you have to set Enabled property to True in the Dynamic tab.

Common Source component properties

Enabled

Set component to be processed dynamically.

FilterType

Select source columns filter type. This parameter has the options listed in the following table.

Value Description
FilterDirect Set to a regular expression that defines the filter. Selecting the value displays the dynamic option FilterDirect.
FilterFile Select a file that contains the filter. Selecting the value displays the dynamic option FilterFile.
FilterVariable Set to a variable that defines the filter. Selecting the value displays the dynamic option FilterVariable.
FilterDirect

Type the filter regular expression.

Related Topics: Regular Expression Editor

FilterFile

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

Related topics: File Connection Manager

FilterVariable

Select an existing user-defined variable, or click <New variable...> to create a variable. The filter can be regular expression, ICollection or ADODB Recordset.

Related Topics: Integration Services Variables, Add Variable

ErrorRowDisposition (1.6 SR-4)

Select how a component proceeds if an error occurs while processing an output column at run time. This parameter has the options listed in the following table.

Value Description
RD_FailComponent Specifies that the component will fail to execute.
RD_IgnoreFailure Specifies that the component will ignore the failure and continue.
RD_RedirectRow Specifies that the row that contains the error is redirected to the error output.
TruncationRowDisposition (1.6 SR-4)

Specifies how a component proceeds when a truncation occurs while processing an output column at runtime. This parameter has the options listed in the following table.

Value Description
RD_FailComponent Specifies that the component will fail to execute.
RD_IgnoreFailure Specifies that the component will ignore the failure and continue.
RD_RedirectRow Specifies that the row that contains the error is redirected to the error output.

Flat File Delimited Source

IsColumnDelimiterVariable (1.5 SR-2)

Indicates whether the column delimiter is stored in a variable. This parameter has the options listed in the following table.

Value Description
True The column delimiter is stored in a variable. Selecting the value displays the dynamic option ColumnDelimiterVariable.
False The column delimiter is directly specified. Selecting the value displays the dynamic option ColumnDelimiter.
ColumnDelimiter

Specify flat file column delimiter.

ColumnDelimiterVariable (1.5 SR-2)

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

Flat File Fixed Source

IsColumnsCountVariable (1.5 SR-2)

Indicates whether the column count is stored in a variable. This parameter has the options listed in the following table.

Value Description
True The columns count is stored in a variable. Selecting the value displays the dynamic option ColumnsCountVariable.
False The columns count is directly specified. Selecting the value displays the dynamic option ColumnsCount.
ColumnsCountVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

ColumnsCount (1.5 SR-2)

Specify flat file columns count.

IsColumnWidthVariable (1.5 SR-2)

Indicates whether the column width is stored in a variable. This parameter has the options listed in the following table.

Value Description
True The column width is stored in a variable. Selecting the value displays the dynamic option ColumnWidthVariable.
False The column width is directly specified. Selecting the value displays the dynamic option ColumnWidth.
ColumnWidth (1.5 SR-2)

Specify flat file ragged right column width.

ColumnWidthVariable (1.5 SR-2)

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

Flat File Ragged Right Source

IsColumnsCountVariable (1.5 SR-2)

Indicates whether the column count is stored in a variable. This parameter has the options listed in the following table.

Value Description
True The columns count is stored in a variable. Selecting the value displays the dynamic option ColumnsCountVariable.
False The columns count is directly specified. Selecting the value displays the dynamic option ColumnsCount.
ColumnsCountVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

ColumnsCount (1.5 SR-2)

Specify flat file columns count.

IsColumnWidthVariable (1.5 SR-2)

Indicates whether the column width is stored in a variable. This parameter has the options listed in the following table.

Value Description
True The column width is stored in a variable. Selecting the value displays the dynamic option ColumnWidthVariable.
False The column width is directly specified. Selecting the value displays the dynamic option ColumnWidth.
ColumnWidth

Specify flat file ragged right column width.

ColumnWidthVariable (1.5 SR-2)

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

EDI Source

Configuration

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

Related topics: File Connection Manager

Common Destination component properties

Enabled

Set component to be processed dynamically.

IsMappingVariable

Indicates whether the mapping is stored in a variable. This parameter has the options listed in the following table.

Value Description
True The mapping is stored in a variable. Selecting the value displays the dynamic option MappingVariable.
False The mapping is specified in a File connection manager. Selecting the value displays the dynamic option Mapping.
Mapping

Select an existing File connection manager, or click <New connection...> to create a connection manager. The columns mapping should be specified as key to value text, where each mapping is separated with newline like:

SourceColumn1=DestinationColumn1
SourceColumn2=DestinationColumn2
...

Related topics: File Connection Manager

MappingVariable

Select an existing user-defined variable, or click <New variable...> to create a variable. The columns mapping can be specified as:

  • Key to value text, where each mapping is separated with newline (\r\n) like:
    SourceColumn1=DestinationColumn1\r\nSourceColumn2=DestinationColumn2\r\n...
  • Object supporting IDictionary interface. For sample dictionary implementation, check Dictionary Destination script.
  • ADODB Recordset object. You can use standard Execute SQL Task to get result in this type of object.

Related Topics: Integration Services Variables, Add Variable

MissingColumnFail (1.5 SR-2)

DEPRECATED: Starting from (1.6 SR-4) use MandatoryMapping parameter instead.

Indicates how to handle missing source columns. This parameter has the options listed in the following table.

Value Description
True The task will fail when source columns are missing in the destination.
False The task will ignore the missing source columns in the destination.
MandatoryMapping (1.6 SR-4)

Specifies mandatory mapping configuration. This parameter has the options listed in the following table.

Value Description
None There is no mandatory mapping.
Source The task will fail when source columns are not mapped.
Destination The task will fail when destination columns are not mapped.
List The task will fail when a column from mandatory list is not mapped. Selecting this option displays the dynamic parameter MandatoryMappingList.
MandatoryMappingList (1.6 SR-4)

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

Flat File Delimited Destination

IsColumnDelimiterVariable (1.5 SR-2)

Indicates whether the column delimiter is stored in a variable. This parameter has the options listed in the following table.

Value Description
True The column delimiter is stored in a variable. Selecting the value displays the dynamic option ColumnDelimiterVariable.
False The column delimiter is directly specified. Selecting the value displays the dynamic option ColumnDelimiter.
ColumnDelimiter

Specify flat file column delimiter.

ColumnDelimiterVariable (1.5 SR-2)

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

Common Transformation component properties

Enabled

Set component to be processed dynamically.

Aggregate Transformation

AggregateConfigurationVariable

Select variable containing ADO.NET DataSet or OLEDB Recordset object. The object should contain aggregate configuration for dynamic columns. You can specify multiple aggregate configurations per column. The configuration consists of the columns listed in the following table.

Column Description
InputColumn Dynamic column name.
Output Name of data flow output where aggregate is created.
AggregationType Aggregation type. This column has the options listed in the following table.

Value Description
0 Group By
1 Count
3 Count Distinct
4 Sum
5 Average
6 Min
7 Max
IsBig Indicates whether the column used in aggregation is high-precision or containing large numeric values. Optional. This column has the options listed in the following table.

Value Description
0 The column is not high-precision.
1 The column is high precision or contains large numeric values. Default.
AggregationComparisonFlags Aggregation comparison flags. Optional. Multiple options can be combined with bitwise OR. This column has the options listed in the following table.
HEX Description
0x00001 Ignore case.
0x00002 Ignore nonspacing characters.
0x10000 Ignore kana type.
0x20000 Ignore character width.
CountDistinctScale For Count Distinct aggregation, specifies scale. Optional.
CountDistinctKeys For Count Distinct aggregation, specifies approximate expected number of keys. Optional.

Related Topics: Integration Services Variables - Add Variable, ADO.NET DataSet, OLEDB Recordset

Character Map Transformation

CharacterConfigurationVariable

Select variable containing ADO.NET DataSet or OLEDB Recordset object. The object should contain character map configuration for dynamic columns. You can specify multiple character map configurations per column. The configuration consists of the columns listed in the following table.

Column Description
InputColumn Dynamic column name.
MapFlags Character operation flags. Multiple options can be combined with bitwise OR. This column has the options listed in the following table.
HEX Description
0x0000100 Lowercase.
0x0000200 Uppercase.
0x0000800 Byte reversal.
0x0100000 Hiragana.
0x0200000 Katakana.
0x0400000 Half width.
0x0800000 Full width.
0x1000000 Linguistic casing. Requires Lowercase or Uppercase option.
0x2000000 Simplified Chinese.
0x4000000 Traditional Chinese.
ResultColumn Dynamic output column name. Optional. If not specified, the transformation will overwrite related dynamic input column.

Related Topics: Integration Services Variables - Add Variable, ADO.NET DataSet, OLEDB Recordset

Conditional Split Transformation

OutputsExpressionVariable

Select variable containing string, Array, ADO.NET DataSet or OLEDB Recordset object. Each specified element contains SSIS expression for the respective conditional split output. The number of elements should match the number of outputs. If you use string, separate each expression with newline.

Related Topics: Integration Services Variables - Add Variable, ADO.NET DataSet, OLEDB Recordset

Copy Column Transformation

CopyConfigurationVariable

Select variable containing ADO.NET DataSet or OLEDB Recordset object. The object should contain copy column configuration for dynamic columns. You can specify multiple copies per column. The configuration consists of the columns listed in the following table.

Column Description
InputColumn Dynamic column name.
ResultColumn Dynamic output column name.

Related Topics: Integration Services Variables - Add Variable, ADO.NET DataSet, OLEDB Recordset

Data Conversion Transformation

ConvertConfigurationVariable

Select variable containing ADO.NET DataSet or OLEDB Recordset object. The object should contain data conversion configuration for dynamic columns. You can specify multiple data conversion configurations per column. The configuration consists of the columns listed in the following table.

Column Description
InputColumn Dynamic column name.
ResultColumn Dynamic output column name.
DataType Output column data type. You have to specify SSIS data type.
Length Output column length.
Precision Output column precision.
Scale Output column scale.
CodePage Output column code page.
FastParse Indicates whether fast parse option is used. This column has the options listed in the following table.

Value Description
0 The column doesn't use fast parse.
1 The column uses fast parse.

Related Topics: Integration Services Variables - Add Variable, ADO.NET DataSet, OLEDB Recordset

Derived Transformation

DerivedConfigurationVariable

Select variable containing ADO.NET DataSet or OLEDB Recordset object. The object should contain derived column configuration. The configuration consists of the columns listed in the following table.

Column Description
ResultColumn Dynamic output column name. If name matches dynamic column name, the input column will be overwritten.
Expression Derived column expression.
DataType Output column data type. You have to specify SSIS data type.
Length Output column length.
Precision Output column precision.
Scale Output column scale.
CodePage Output column code page.

Related Topics: Integration Services Variables - Add Variable, ADO.NET DataSet, OLEDB Recordset, SSIS Expression

Export Column Transformation

ColumnConfigurationVariable

Select variable containing ADO.NET DataSet or OLEDB Recordset object. The object should contain configuration for dynamic columns. The configuration consists of the columns listed in the following table.

Column Description
InputColumn Dynamic column name, which contains data to export.
ResultColumn Contains file path to export location.
WriteBOM Indicates whether a byte-order mark (BOM) is written to the file. Optional. This column has the options listed in the following table.

Value Description
0 BOM is not written. Default.
1 BOM is written.
ForceTruncate Indicates whether an existing file is truncated. Optional. This column has the options listed in the following table.

Value Description
0 File is not truncated.
1 File is truncated. Default.
AllowAppend Indicates whether an existing file is appended. Optional. This column has the options listed in the following table.

Value Description
0 File is not appended. Default.
1 File is appended.

Related Topics: Integration Services Variables - Add Variable, ADO.NET DataSet, OLEDB Recordset

Fuzzy Grouping Transformation

ColumnConfigurationVariable

Select variable containing ADO.NET DataSet or OLEDB Recordset object. The object should contain configuration for dynamic columns. The configuration consists of the columns listed in the following table.

Column Description
InputColumn Dynamic column name.
ResultColumn Pass-thru column name. Optional.
MatchType Fuzzy grouping match type. Optional. This column has the options listed in the following table.

Value Description
1 Exact match.
2 Fuzzy match. Default.
Numerals Indicates the significance of leading and trailing numerals in comparing the column data. Optional. This column has the options listed in the following table.

Value Description
0 Neither. Default.
1 Leading.
2 Trailing.
3 Leading and Trailing.
MinSimilarity Indicates the similarity threshold at the join level. Optional. The default is 0.
FuzzyComparisonFlags String comparison flags. Optional. The default is 1. Multiple options can be combined with bitwise OR. This column has the options listed in the following table.
HEX Description
0x00001 Ignore case.
0x00002 Ignore nonspacing characters.
0x00004 Ignore symbols.
0x01000 Sort punctuation as symbols.
0x10000 Ignore kana type.
0x20000 Ignore character width.
GroupColumn Fuzzy group column name. Optional. The default is [dynamic column]_clean.
SimilarityColumn Similarity column name. Optional. The default is _Similarity_[dynamic column].

Related Topics: Integration Services Variables - Add Variable, ADO.NET DataSet, OLEDB Recordset

Fuzzy Lookup Transformation

ReferenceTableVariable

Select variable containing fuzzy lookup reference table. Optional.

Related Topics: Integration Services Variables, Add Variable

MatchIndexNameVariable

Select variable containing fuzzy lookup match index table. Optional.

Related Topics: Integration Services Variables, Add Variable

ColumnConfigurationVariable

Select variable containing ADO.NET DataSet or OLEDB Recordset object. The object should contain configuration for dynamic columns. The configuration consists of the columns listed in the following table.

Column Description
InputColumn Dynamic column name.
JoinToReferenceColumn Reference column name.
ResultColumn Result column name. Optional. If not specified, reference column name is used.
JoinType Fuzzy match type. Optional. This column has the options listed in the following table.

Value Description
1 Exact match.
2 Fuzzy match. Default.
MinSimilarity Indicates the similarity threshold at the join level. Optional. The default is 0.
FuzzyComparisonFlags String comparison flags. Optional. The default is 0. Multiple options can be combined with bitwise OR. This column has the options listed in the following table.
HEX Description
0x00001 Ignore case.
0x00002 Ignore nonspacing characters.
0x00004 Ignore symbols.
0x01000 Sort punctuation as symbols.
0x10000 Ignore kana type.
0x20000 Ignore character width.
FuzzyComparisonFlagsEx Mapping flags. Optional. The default is 0. Multiple options can be combined with bitwise OR. This column has the options listed in the following table.
HEX Description
0x0000 NoMapping.
0x0010 MapFoldCZone.
0x0020 MapPrecomposed.
0x0080 MapFoldDigits.
0x2000 MapExpandLigatures.

Related Topics: Integration Services Variables - Add Variable, ADO.NET DataSet, OLEDB Recordset

Import Column Transformation

ColumnConfigurationVariable

Select variable containing ADO.NET DataSet or OLEDB Recordset object. The object should contain configuration for dynamic columns. The configuration consists of the columns listed in the following table.

Column Description
InputColumn Dynamic column name, which contains file path to information to import.
ResultColumn Contains column name where imported data is stored.
ExpectBOM Indicates whether a byte-order mark (BOM) to be expected in file. Optional. This column has the options listed in the following table.

Value Description
0 BOM is not expected. Default.
1 BOM to be expected.

Related Topics: Integration Services Variables - Add Variable, ADO.NET DataSet, OLEDB Recordset

Lookup Transformation

SqlCommandVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

Lookup Plus Transformation

ColumnConfigurationVariable

Select variable containing ADO.NET DataSet or OLEDB Recordset object. The object should contain configuration for dynamic columns. The configuration consists of the columns listed in the following table.

Column Description
InputColumn Dynamic column name.
LookupConnection Package connection to use in lookup.
LookupQuery Lookup SQL query.
ResultColumn Lookup result output column name.
OnNull Value to use when input is NULL.
NoMatch Value to use when there is no match.

Related Topics: Integration Services Variables - Add Variable, ADO.NET DataSet, OLEDB Recordset

OLE DB Command Transformation

ParameterColumnsVariable

Select an existing user-defined variable, or click <New variable...> to create a variable. The variable should contain comma (,) separated list of command parameters.

Related Topics: Integration Services Variables, Add Variable

Pivot Transformation

PivotKeysVariable

Select an existing user-defined variable, or click <New variable...> to create a variable. The variable should contain comma (,) separated list of pivot keys.

Related Topics: Integration Services Variables, Add Variable

Sort Transformation

SortColumnsVariable

Select an existing user-defined variable, or click <New variable...> to create a variable. The variable should contain comma (,) separated list of sort columns.

Related Topics: Integration Services Variables, Add Variable

Table Difference Transformation

ColumnConfigurationVariable

Select variable containing ADO.NET DataSet or OLEDB Recordset object. Optional. The object should contain configuration for dynamic columns. The configuration consists of the columns listed in the following table.

Column Description
InputColumn Dynamic column name.
KeyOrder Column key order. Optional. If not specified, it will use the column key order from upstream component.
UpdateID Column update output identifier. Optional. If not specified, it will use the first update output.
Option Column type. Optional. This column has the options listed in the following table.

Value Description
0 Key column.
1 Compare column. Default.
2 Use old column.
3 Use new column.

Related Topics: Integration Services Variables - Add Variable, ADO.NET DataSet, OLEDB Recordset

Template Transformation

TemplateConfigurationVariable

Select variable containing ADO.NET DataSet or OLEDB Recordset object. The object should contain template column configuration. The configuration consists of the columns listed in the following table.

Column Description
ResultColumn Dynamic output column name. If name matches dynamic column name, the input column will be overwritten.
Template Template column specification.

Related Topics: Integration Services Variables - Add Variable, ADO.NET DataSet, OLEDB Recordset, Apache Velocity

Term Extraction Transformation

ExtractionColumnVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

ExclusionTableVariable

Optional. Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

ExclusionColumnVariable

Optional. Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

Term Lookup Transformation

LookupColumnVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

ReferenceTableVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

ReferenceColumnVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

Unpivot Transformation

PivotColumnVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

DestinationColumnVariable

Select an existing user-defined variable, or click <New variable...> to create a variable.

Related Topics: Integration Services Variables, Add Variable

PivotKeysVariable

Select an existing user-defined variable, or click <New variable...> to create a variable. The variable should contain comma (,) separated list of pivot keys.

Related Topics: Integration Services Variables, Add Variable

How to create completely dynamic data flow? (1.4 SR-3)

Starting from 1.4 SR-3 the data flow can be completely dynamic - the need to have one common column across different iteration sets is no longer required. To have completely dynamic components, you have to remove all statically defined columns (both in the components and related connection managers). However there are components, which require at least one column to pass successfully validation. We have implemented a workaround, which is based on thunk (not used) column. If you encounter a component requiring at least one column, define a column named THUNK_COLUMN. This column will be maintained and ignored at runtime by the Data Flow Task Plus.

Dynamic Column Custom Handler (1.4 SR-3)

  • To setup custom routine in the data flow script for additional setup of dynamically added data flow column, attribute your script main class with DataFlowColumnAdded attribute. Set as parameter the name of the script method. Check below the required handler method layout.
    <DataFlowColumnAdded("OnColumnAdded")> _
    Public Class ScriptMain
        Inherits UserComponent
    ...
        Public Sub OnColumnAdded(ByVal component As IDTSComponentMetaData90, ByVal isInput As Boolean, ByVal colName As String)
    ...

Samples

  • You can download sample package demonstrating all dynamic data flow transformations from here.
  • Getting Error Row Description and Column Information Dynamically by Eric Whitley.

Parallel Loop Task

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

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

All Downloads ----- Join VIP ----- Questions?

Overview

The Parallel Loop Task is an SSIS Control Flow task, which can execute multiple iterations of the standard Foreach Loop Container concurrently. Based on actual tests, when a CPU-intensive process is run on a 4-core machine using this Parallel Loop Task, it executes 3 times faster as compared to running the same process sequentially.


Setup

Use the General page of the Parallel Loop Task Editor dialog to configure the parameters needed to execute a loop in parallel.

Parameters

Name

Specify the task name.

Description

Specify the task description.

StopOnFailure

Specify whether or not the Parallel Loop Task terminates if parallel execution fails.

PackageConnection

Specify the package connection.

ForEachLoop

Type the Foreach Loop you want executed concurrently, or click the browse button () and locate it. You must disable the target loop because the loop execution will be done by the Parallel Loop Task.

MaxExecutables

Specify the number of threads to use for concurrent execution. A value of -1 allows the maximum number of concurrently running iterations to equal the number of processors plus two. The specified value is constrained to be equal to or less than the Packages' MaxConcurrentExecutables property.

Considerations about Foreach Loop iteration variable

It is mandatory to use an iteration variable scoped inside the loop container. Failure to do this will result in errors and/or concurrency issues.

How to set up a dynamic loop connection

Follow the steps below to set up a dynamic iteration-variable dependent connection:

  1. Create a variable with the package scope and use this variable in the connection manager to construct your expression.
  2. Create a new variable with the same name and type, but with the loop scope.


The Parallel Loop Task will use the variable with the loop scope to evaluate the connection manager expression and build the dynamic connection.

Samples

  • For a sample demonstrating concurrent compression with the COZYROC Zip Task, check this package. The package contains loops, demonstrating both variable and connection iterations.
  • Introducing ETL Assistant – A Dynamic, Templated Approach to SSIS by Eric Whitley

Excel Task

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

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

All Downloads ----- Join VIP ----- Questions?

Overview

The Excel Task is an SSIS Control Flow task for management of Excel workbooks.


Setup

Use the General page of the Excel Task Editor dialog to configure the parameters needed to process an Excel workbook.

Parameters

Name

Specify the task name.

Description

Specify the task description.

StopOnFailure

Specify whether or not the Excel Task should terminate if the chosen action fails.

Action

Specify the task action. This parameter has the options listed in the following table.

Option Description
Load from stream Loads an Excel workbook from a stream object at runtime. Selecting this action displays the dynamic parameter SourceStream.
Save to stream Saves an Excel workbook to a stream object at runtime. Selecting this action displays the dynamic parameter TargetStream.
Get worksheets list Gets a list of worksheets from an Excel workbook. The result is a regular array. Selecting this action displays the dynamic parameter ResultVariable.
New worksheet Creates a new worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable.
Rename worksheet Renames a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsOldNameVariable, OldName, OldNameVariable, IsNewNameVariable, NewName, NewNameVariable.
Delete worksheet Deletes a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable.
Clear worksheet Clears a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable.
Delete Columns Deletes columns in a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable, IsStartColumnVariable, StartColumn, StartColumnVariable, IsCountVariable, Count, CountVariable.
Delete Rows Deletes rows in a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable, IsStartRowVariable, StartRow, StartRowVariable, IsCountVariable, Count, CountVariable.
Find cell Finds a cell with a specific value in a worksheet. Selecting this action displays the dynamic parameters FoundColumnVariable, FoundRowVariable, IsWorksheetVariable, Worksheet, WorksheetVariable, IsValueVariable, Value, ValueVariable, IsStartColumnVariable, StartColumn, StartColumnVariable, IsStartRowVariable, StartRow, StartRowVariable.
Replace cell Finds a cell with a specific value (old value) in a worksheet and replaces it with another value (new value). Selecting this action displays the dynamic parameters SaveAtEnd, ReplaceCountVariable, IsWorksheetVariable, Worksheet, WorksheetVariable, IsOldValueVariable, OldValue, OldValueVariable, IsNewValueVariable, NewValue, NewValueVariable.
Set password (1.5 SR-1) Set an open or modify password for an Excel workbook. Selecting this action displays the dynamic parameters SaveAtEnd, IsOpenPasswordVariable, OpenPassword, OpenPasswordVariable, IsModifyPasswordVariable, ModifyPassword, ModifyPasswordVariable.
Set worksheet visible (1.6 SR-4) Set worksheet visibility. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable, IsValueVariable, Value, ValueVariable.
SourceStream

Specify the stream object from which to load an Excel workbook at runtime. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

TargetStream

Specify a stream object to which the Excel workbook should be saved at runtime. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

ResultVariable

Specify the variable where the list of worksheets from the specified Excel workbook should be stored as a regular array. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

SaveAtEnd

Indicate whether or not the workbook should be saved at the end of the execution of this task. This parameter has the options listed in the following table.

Option Description
True The workbook should be saved when execution of the task is complete.
False The workbook should not be saved to a file. The changes will only be kept in memory.
IsWorksheetVariable

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

Option Description
True The worksheet name is stored in a variable. Selecting this option displays the dynamic parameter WorksheetVariable.
False The worksheet name is specified explicitly. Selecting this option displays the dynamic parameter Worksheet.
Worksheet

Explicitly specify the worksheet name.

WorksheetVariable

Specify the variable containing the worksheet name. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsOldNameVariable

Indicate whether or not the old worksheet name is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The old worksheet name is stored in a variable. Selecting this option displays the dynamic parameter OldNameVariable.
False The old worksheet name is specified explicitly. Selecting this option displays the dynamic parameter OldName.
OldName

Specify the old worksheet name.

OldNameVariable

Specify the variable in which the old worksheet name is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsNewNameVariable

Indicate whether or not the new worksheet name is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The new worksheet name is stored in a variable. Selecting this option displays the dynamic parameter NewNameVariable.
False The new worksheet name is specified explicitly. Selecting this option displays the dynamic parameter NewName.
NewName

Specify the new worksheet name.

NewNameVariable

Specify the variable in which the new worksheet name is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsStartColumnVariable

Indicate whether or not the start column for the specified action is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The start column is stored in a variable. Selecting this option displays the dynamic parameter StartColumnVariable.
False The start column is specified explicitly. Selecting this option displays the dynamic parameter StartColumn.
StartColumn

Explicitly specify the start column. The value is 1-based.

StartColumnVariable

Specify the variable in which the start column is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsStartRowVariable

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

Option Description
True The start row is stored in a variable. Selecting this option displays the dynamic parameter StartRowVariable.
False The start row is specified explicitly. Selecting this option displays the dynamic parameter StartRow.
StartRow

Explicitly specify the start row. The value is 1-based.

StartRowVariable

Specify the variable in which the start row is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsCountVariable

Indicate whether or not the row or column count is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The row or column count is stored in a variable. Selecting this option displays the dynamic parameter CountVariable.
False The row or column count is specified explicitly. Selecting this option displays the dynamic parameter Count.
Count

Explicitly specify the number of rows or columns to be deleted. Set the count to -1 to remove all used columns or rows after the specified start position.

CountVariable

Specify the variable containing the number of rows or columns to be deleted. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

FoundColumnVariable

Indicate the variable where the column of the cell that was found will be stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

FoundRowVariable

Indicate the variable where the row of the cell that was found will be stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsValueVariable

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

Option Description
True The value is stored in a variable. Selecting this option displays the dynamic parameter ValueVariable.
False The value is specified explicitly. Selecting this option displays the dynamic parameter Value.
Value

Explicitly specify the value.

ValueVariable

Specify the variable in which the value is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

ReplaceCountVariable

Indicate the variable where the resulting number of replacements is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsOldValueVariable

Indicate whether or not the old value (i.e. the value being replaced) is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The old value is stored in a variable. Selecting this option displays the dynamic parameter OldValueVariable.
False The old value is specified explicitly. Selecting this option displays the dynamic parameter OldValue.
OldValue

Explicitly specify the old value to be found and replaced.

OldValueVariable

Specify the variable where the old value (the one being found and replaced) is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsNewValueVariable

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

Option Description
True The new value is stored in a variable. Selecting this option displays the dynamic parameter NewValueVariable.
False The new value is specified explicitly. Selecting this option displays the dynamic parameter NewValue.
NewValue

Explicitly specify the new value which will replace the old value.

NewValueVariable

Specify the variable containing the new value which will replace the old value. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsOpenPasswordVariable (1.5 SR-1)

Indicate whether or not the open password for the Excel workbook is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The open password is stored in a variable. Selecting this option displays the dynamic parameter OpenPasswordVariable.
False The open password is specified explicitly. Selecting this option displays the dynamic parameter OpenPassword.
OpenPassword (1.5 SR-1)

Explicitly specify the open password for the Excel workbook.

OpenPasswordVariable (1.5 SR-1)

Specify the variable containing the open password for the Excel workbook. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsModifyPasswordVariable (1.5 SR-1)

Indicate whether or not the modify password for the Excel workbook is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The modify password is stored in a variable. Selecting this option displays the dynamic parameter ModifyPasswordVariable.
False The modify password is specified explicitly. Selecting this option displays the dynamic parameter ModifyPassword.
ModifyPassword (1.5 SR-1)

Explicitly specify the modify password for the Excel workbook.

ModifyPasswordVariable (1.5 SR-1)

Specify the variable containing the modify password for the Excel workbook. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

Samples

Send Mail Task

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

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

All Downloads ----- Join VIP ----- Questions?

Overview

The Send Mail Task Plus is an SSIS Control Flow task for sending e-mail. The task can also S/MIME sign and encrypt e-mails.


Demonstration

Demonstration VideoDemonstration Video

Setup

Use the General page of the Send Mail Task Editor dialog to configure the parameters desired for sending e-mails.

Parameters

Name

Specify the task name.

Description

Specify the task description.

IsNewMail

Indicate whether or not the task creates new mail. This parameter has the options listed in the following table.

Option Description
True The task creates new mail. Selecting this option displays the dynamic parameters From, IsSubjectVariable, Subject, SubjectVariable, ToSource, ToDirect, ToFile, ToVariable, CcSource, CcDirect, CcFile, CcVariable, BccSource, BccDirect, BccFile, BccVariable, HeadersSource, HeadersDirect, HeadersFile, HeadersVariable, BodyTextSource, BodyTextDirect, BodyTextFile, BodyTextVariable, BodyHtmlSource, BodyHtmlDirect, BodyHtmlFile, BodyHtmlVariable, Priority, IsAttachmentVariable, Attachment, AttachmentVariable.
False The task loads existing mail. Selecting this option displays the dynamic parameters IsSourceVariable, Source, SourceVariable.
From

Specify the mail sender’s e-mail address.

IsSubjectVariable

Indicate whether or not the e-mail’s subject line is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The e-mail’s subject line is stored in a variable. Selecting this option displays the dynamic parameter SubjectVariable.
False The e-mail’s subject line is specified directly. Selecting this option displays the dynamic parameter Subject.
Subject

Specify the e-mail’s subject line.

SubjectVariable

Specify the variable in which the e-mail’s subject line is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

ToSource

Specify the source of the e-mail’s recipients. This parameter has the options listed in the following table.

Option Description
Direct input The e-mail recipients are specified directly. Selecting this option displays the dynamic parameter ToDirect.
File connection The e-mail’s recipients are loaded from a file. Each e-mail recipient can be on a separate line. Selecting this option displays the dynamic parameter ToFile.
Variable The e-mail’s recipients are loaded from a variable. Selecting this option displays the dynamic parameter ToVariable.
ToDirect

Specify the e-mail’s recipients. Each e-mail recipient is separated with a semicolon (;).

ToFile

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

Related topics: File Connection Manager Editor

ToVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

CcSource

Specify the source of the copied (CC’d) e-mail recipients. This parameter has the options listed in the following table.

Option Description
Direct input Copied (CC’d) e-mail recipients are specified directly. Selecting this option displays the dynamic parameter CcDirect.
File connection Copied (CC’d) e-mail recipients are loaded from a file. Each copied e-mail recipient can be on a separate line. Selecting this option displays the dynamic parameter CcFile.
Variable Copied (CC’d) e-mail recipients are loaded from a variable. Selecting this option displays the dynamic parameter CcVariable.
CcDirect

Specify the e-mail recipients to be copied (CC’d). Separate each recipient with a semicolon (;).

CcFile

Specify the file from which to load the recipients to be copied (CC’d) on the e-mail. Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

CcVariable

Specify the variable from which to load the recipients to be copied (CC’d) on the e-mail. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

BccSource

Specify the source for the e-mail recipients to be blind copied (BCC’d); these recipients will not be revealed. This parameter has the options listed in the following table.

Option Description
Direct input Blind-copied (BCC’d) e-mail recipients are directly specified. Selecting this option displays the dynamic parameter BccDirect.
File connection Blind-copied (BCC’d) e-mail recipients are loaded from a file. Specify each e-mail recipient on a separate line. Selecting this option displays the dynamic parameter BccFile.
Variable Blind-copied (BCC’d) e-mail recipients are loaded from a variable. Selecting this option displays the dynamic parameter BccVariable.
BccDirect

Specify the e-mail recipients to be blind-copied (BCC’d). Separate each recipient with a semicolon (;).

BccFile

Specify the file from which to load the recipients to be blind-copied (BCC’d) on the e-mail. Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

BccVariable

Specify the variable from which to load the recipients to be blind-copied (BCC’d) on the e-mail. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

HeadersSource (1.6 SR-1)

Specify the source for the e-mail headers. This parameter has the options listed in the following table.

Option Description
Direct input e-mail headers are directly specified. Selecting this option displays the dynamic parameter HeadersDirect.
File connection e-mail headers are loaded from a file. Selecting this option displays the dynamic parameter HeadersFile.
Variable e-mail headers are loaded from a variable. Selecting this option displays the dynamic parameter HeadersVariable.
HeadersDirect (1.6 SR-1)

Specify the e-mail headers. The parameter should be specified as header name to value text, where each header is separated with newline like:

header1=value1
header2=value2
...

HeadersFile (1.6 SR-1)

Specify the file from which to load the e-mail headers. Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

HeadersVariable (1.6 SR-1)

Specify the variable from which to load the e-mail headers. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

BodyTextSource

Specify the source for the text in the body of the e-mail. This parameter has the options listed in the following table.

Option Description
Direct input The text for the body of the e-mail is specified directly. Selecting this option displays the dynamic parameter BodyTextDirect.
File connection The text for the body of the e-mail is loaded from a file. Selecting this option displays the dynamic parameter BodyTextFile.
Variable The text for the body of the e-mail is loaded from a variable. Selecting this option displays the dynamic parameter BodyTextVariable.
BodyTextDirect

Specify the text for the body of the e-mail.

BodyTextFile

Specify the file from which to load the text for the body of the e-mail. Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

BodyTextVariable

Specify the variable from which to load the text for the body of the e-mail. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

BodyHtmlSource

Specify the source of the HTML for the body of the e-mail. This parameter has the options listed in the following table.

Option Description
Direct input The HTML for the body of the e-mail is specified directly. Selecting this option displays the dynamic parameter BodyHtmlDirect.
File connection The HTML for the body of the e-mail is loaded from a file. Selecting this option displays the dynamic parameter BodyHtmlFile.
Variable The HTML for the body of the e-mail is loaded from a variable. Selecting this option displays the dynamic parameter BodyHtmlVariable.
BodyHtmlDirect

Specify the HTML for the body of the e-mail.

BodyHtmlFile

Specify the file from which to load the HTML for the body of the e-mail. Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

BodyHtmlVariable

Specify the variable from which to load the HTML for the body of the e-mail. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

Priority

Specify the priority of the e-mail. These are the available options: Low, Normal, High.

IsAttachmentVariable

Indicate whether or not the list of e-mail attachments is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The list of e-mail attachments is stored in a variable. Selecting this option displays the dynamic parameter AttachmentVariable.
False The list of e-mail attachments is specified directly. Selecting this option displays the dynamic parameter Attachment.
Attachment

Specify the list of e-mail attachments. Separate each attachment with a semicolon (;).

AttachmentVariable

Specify the variable from which to load the list of e-mail attachments. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsSigned

Indicate whether or not the e-mail must be signed. This parameter has the options listed in the following table.

Option Description
True The e-mail must be signed. Selecting this option displays the dynamic parameters IsPrivateKeyVariable, PrivateKey, PrivateKeyVariable, Password.
False The e-mail should not be signed.
IsPrivateKeyVariable

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

Option Description
True The private key file path is stored in a variable. Selecting this option displays the dynamic parameter PrivateKeyVariable.
False The private key file path is specified in a File connection manager. Selecting this option displays the dynamic parameter PrivateKey.
PrivateKey

Specify the file containing the private key. Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

PrivateKeyVariable

Specify the variable containing the private key. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

Password

Specify the private key password.

IsEncrypted

Indicate whether or not the e-mail should be encrypted. This parameter has the options listed in the following table.

Option Description
True The e-mail should be encrypted. Selecting this option displays the dynamic parameters IsCertificateVariable, Certificate, CertificateVariable.
False The e-mail should not be encrypted.
IsCertificateVariable

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

Option Description
True The certificate file path is stored in a variable. Selecting this option displays the dynamic parameter CertificateVariable.
False The certificate file path is specified in a File connection manager. Selecting this option displays the dynamic parameter Certificate.
Certificate

Specify the File connection manager from which to obtain the file path of the certificate. Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

CertificateVariable

Specify the variable from which to obtain the file path of the certificate. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

Location

Indicate the delivery location of the e-mail. This parameter has the options listed in the following table.

Option Description
Server The e-mail is to be delivered to a mail server. Selecting this option displays the dynamic parameter Connection.
File The e-mail is to be saved as a file. Selecting this option displays the dynamic parameters IsTargetVariable, Target, TargetVariable, OverwriteExisting.
Connection

Specify the mail server to which the e-mail is to be delivered. Select an existing connection manager, or click <New connection...> to create a new connection manager.

Related topics: SMTP Connection Manager, Exchange Connection Manager (1.6)

IsTargetVariable

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

Option Description
True The target file path is stored in a variable. Selecting this option displays the dynamic parameter TargetVariable.
False The target file path is specified in a File connection manager. Selecting this option displays the dynamic parameter Target.
Target

Specify the File connection manager in which the e-mail is to be stored. Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

TargetVariable

Specify the variable in which the e-mail is to be stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

OverwriteExisting

Specify whether or not an existing e-mail file can be overwritten with the newly created e-mail file. This parameter has the options listed in the following table.

Option Description
True If an existing e-mail file exists, it will be overwritten by the newly created e-mail file.
False If an existing e-mail file exists, it will not be overwritten by the newly created e-mail file and a warning message will be generated in the log file.

Receive Mail Task

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

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

All Downloads ----- Join VIP ----- Questions?

Overview

The Receive Mail Task is an SSIS Control Flow task for receiving e-mails. The task can also decrypt and verify signatures of S/MIME e-mails.


Demonstration

Demonstration VideoDemonstration Video

Setup

Use the General page of the Receive Mail Task Editor dialog to configure the parameters needed to receive e-mails.

Parameters

Name

Specify the task name.

Description

Specify the task description.

StopOnFailure

Specify whether or not the Receive Mail Task terminates if the chosen action fails.

Action

Specify the task action. This parameter has the options listed in the following table.

Option Description
Get mail Downloads mail to a local file. Selecting this action displays the dynamic parameters IsLocalVariable, Local, LocalVariable, OverwriteExisting, MailConnection, MailVariable.
Open mail Opens the mail file. Selecting this action displays the dynamic parameters IsLocalVariable, Local, LocalVariable, Decrypt, VerifySignature, IsSenderVariable, Sender, SenderVariable, IsDateVariable, Date, DateVariable, IsSubjectVariable, Subject, SubjectVariable, IsBodyTextVariable, BodyText, BodyTextVariable, IsBodyHtmlVariable, BodyHtml, BodyHtmlVariable, IsAttachmentVariable, Attachment, AttachmentVariable.
Delete mail Deletes the mail. Selecting this action displays the dynamic parameters MailConnection, MailVariable.
Get mail list Gets a list of e-mails. Selecting this action displays the dynamic parameters ResultVariable, MailConnection, IsFilterVariable, Filter, FilterVariable.
IsLocalVariable

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

Option Description
True The local path is stored in a variable. Selecting this option displays the dynamic parameter LocalVariable.
False The local path is specified in a File connection manager. Selecting this option displays the dynamic parameter Local.
Local

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

Related topics: File Connection Manager Editor

LocalVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

OverwriteExisting

Specify whether or not an existing local file can be overwritten with the received mail. This parameter has the options listed in the following table.

Option Description
True If an existing local file exists, it will be overwritten by the received mail.
False If an existing local file exists, it will not be overwritten by the received mail and a warning message will be generated in the log file.
MailConnection

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

Related Topics: IMAP Connection Manager, Exchange Connection Manager (1.6), POP3 Connection Manager

MailVariable

Mail information object. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

Decrypt

Indicate whether or not the mail must be decrypted. This parameter has the options listed in the following table.

Option Description
True The mail must be decrypted. Selecting this option displays the dynamic parameters IsPrivateKeyVariable, PrivateKey, PrivateKeyVariable, Password.
False The mail is not encrypted so it need not be decrypted.
IsPrivateKeyVariable

Indicate whether the file path of the private key is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The file path of the private key is stored in a variable. Selecting this option displays the dynamic parameter PrivateKeyVariable.
False The file path of the private key is specified in a File connection manager. Selecting this option displays the dynamic parameter PrivateKey.
PrivateKey

Specify the File connection manager where the file path of the private key is specified. Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

PrivateKeyVariable

Specify the variable where the file path of the private key is specified. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

Password

Specify the private key password.

VerifySignature

Indicate whether or not the mail signature must be verified. This parameter has the options listed in the following table.

Option Description
True Verify the mail signature. Selecting this option displays the dynamic parameters , IsCertificateVariable, Certificate, CertificateVariable.
False Do not verify the mail signature.
IsCertificateVariable

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

Option Description
True The file path of the certificate is stored in a variable. Selecting this option displays the dynamic parameter CertificateVariable.
False The file path of the certificate is specified in a File connection manager. Selecting this option displays the dynamic parameter Certificate.
Certificate

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

Related topics: File Connection Manager Editor

CertificateVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsSenderVariable (1.5 SR-3)

Indicate whether or not the sender of the mail should be stored in a variable. This parameter has the options listed in the following table.

Option Description
True The sender of the mail should be stored in a variable. Selecting this option displays the dynamic parameter SenderVariable.
False The sender of the mail should be stored in a file and is specified in a File connection manager. Selecting this option displays the dynamic parameter Sender.
Sender (1.5 SR-3)

Specify the file where the sender of the mail should be stored. Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

SenderVariable (1.5 SR-3)

Specify the variable where the sender of the mail should be stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsDateVariable (1.5 SR-3)

Indicate whether or not the origination date of the mail should be stored in a variable. This parameter has the options listed in the following table.

Option Description
True The origination date of the mail should be stored in a variable. Selecting this option displays the dynamic parameter DateVariable.
False The origination date of the mail should be stored in a file and is specified in a File connection manager. Selecting this option displays the dynamic parameter Date.
Date (1.5 SR-3)

Specify the file in which the origination date of the mail should be stored. Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

DateVariable (1.5 SR-3)

Specify the variable in which the origination date of the mail should be stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsSubjectVariable

Indicate whether or not the subject line of the mail should be stored in a variable. This parameter has the options listed in the following table.

Option Description
True The subject line of the mail should be stored in a variable. Selecting this option displays the dynamic parameter SubjectVariable.
False The subject line of the mail should be stored in a file and is specified in a File connection manager. Selecting this option displays the dynamic parameter Subject.
Subject

Specify the file in which the subject line of the mail should be stored. Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

SubjectVariable

Specify the variable in which the subject line of the the mail should be stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsBodyTextVariable

Indicate whether or not the text of the body of the mail should be stored in a variable. This parameter has the options listed in the following table.

Option Description
True The text of the body of the mail should be stored in a variable. Selecting this option displays the dynamic parameter BodyTextVariable.
False The text of the body of the mail should be stored in a file and is specified in a File connection manager. Selecting this option displays the dynamic parameter BodyText.
BodyText

Specify the file in which the text of the body of the mail should be stored. Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

BodyTextVariable

Specify the variable in which the text of the body of the mail should be stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsBodyHtmlVariable

Indicate whether or not the HTML of the body of the mail should be stored in a variable. This parameter has the options listed in the following table.

Option Description
True The HTML of the body of the mail should be stored in a variable. Selecting this option displays the dynamic parameter BodyHtmlVariable.
False The HTML of the body of the mail should be stored in a file and is specified in a File connection manager. Selecting this option displays the dynamic parameter BodyHtml.
BodyHtml

Specify the file where the HTML of the body of the mail should be stored. Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

BodyHtmlVariable

Specify the variable where the HTML of the body of the mail should be stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsAttachmentVariable

Indicate whether or not the mail attachments folder is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The mail attachments folder should be stored in a variable. Selecting this option displays the dynamic parameter AttachmentVariable.
False The mail attachments folder should be specified in a File connection manager. Selecting this option displays the dynamic parameter Attachment.
Attachment

Specify the File connection manager in which the mail attachments folder should be specified. Select an existing File connection manager, or click <New connection...> to create a new connection manager.

Related topics: File Connection Manager Editor

AttachmentVariable

Specify the variable in which the mail attachments folder should be specified. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

ResultVariable

Specify the variable where the returned list of mail should be stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsFilterVariable

Indicate whether the mail filter criteria is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The mail filter criteria is stored in a variable. Selecting this option displays the dynamic parameter FilterVariable.
False The mail filter criteria is directly specified. Selecting this option displays the dynamic parameter Filter.
Filter

Specify the mail filter criteria. The filter expression for each connection type is as follows:

Connection Description
POP3

FLEE expression. You can use the following elements: ID, Date, Size, From, To, CC, ReplyTo, Subject, BodyText, BodyHtml, HasAttachments.

For example, to get all e-mails after 10 Sep 2009 containing "data" in the subject line, use the following expression: Date > #09/10/2009# and Subject.Contains("data")

Note: Because the filter process is done on the client side, be careful what elements you use in your expressions. Using BodyText, BodyHtml and HasAttachments elements will require complete mail retrieval.

IMAP IMAP SEARCH COMMAND expression.
EXCHANGE (1.6) Exchange AQS expression.
FilterVariable

Specify the variable in which the mail filter criteria is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

Related scripts

Samples

  • For a sample showing how to process and iterate over mail information, check this package.
  • Email Attachment Harvesting using COZYROC SSIS+ by Greg Troyan.

SCP Task

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

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

All Downloads ----- Join VIP ----- Questions?

Overview

The SCP Task is an SSIS Control Flow task for secure copying of files with an SSH server. For more advanced functionality check SFTP Task.


Setup

Use the General page of the SCP Task Editor dialog to configure the options needed to copy files with a remote SSH server.

Parameters

Name

Specify the task name.

Description

Specify the task description.

StopOnFailure

Specify whether the SCP Task should terminate if copying fails.

Action

Specify the task action. This parameter has the options listed in the following table.

Option Description
Send files Send files. Selecting this action displays the dynamic options IsLocalVariable, Local, LocalVariable, ScpConnection, IsRemoteVariable, Remote, RemoteVariable.
Receive files Receive files. Selecting this action displays the dynamic options IsLocalVariable, Local, LocalVariable, ScpConnection, IsRemoteVariable, Remote, RemoteVariable.
IsLocalVariable

Indicates whether the local path is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The local path is stored in a variable. Selecting this option displays the dynamic option LocalVariable.
False The local path is specified in a File connection manager. Selecting this option displays the dynamic option Local.
Local

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

Related topics: File Connection Manager Editor

LocalVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

ScpConnection

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

Related Topics: SSH Connection Manager

IsRemoteVariable

Indicates whether the remote path is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The remote path is stored in a variable. Selecting this option displays the dynamic option RemoteVariable.
False The remote path is explicitly specified on the remote server. Selecting this option displays the dynamic option Remote.
Remote

Select the remote path on the remote server.

RemoteVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

Template Task

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

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

All Downloads ----- Join VIP ----- Questions?

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

Stream Task

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

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

All Downloads ----- Join VIP ----- Questions?

Overview

The Stream Task is an SSIS Control Flow task for management and manipulation of the standard Stream object.


Setup

Use the General page of the Stream Task Editor dialog to configure the parameters needed to work with a stream object.

Parameters

Name

Specify the task name.

Description

Specify the task description.

StreamVariable

Specify the variable containing the manipulated stream object. Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

Action

Specify the task action. This parameter has the options listed in the following table.

Option Description
Create a stream Creates a stream object. Selecting this action displays the dynamic parameters StreamType, IsFilenameVariable, Filename, FilenameVariable, OverwriteExisting.
Open file stream Opens a stream object to a file. Selecting this action displays the dynamic parameters IsFilenameVariable, Filename, FilenameVariable.
Read from stream Reads data from a stream. The result is an array of bytes. Selecting this option displays the dynamic parameters Read, Terminator, Length, ResultVariable, CloseAtEnd.
Write to stream Writes data to a stream. Selecting this option displays the dynamic parameters Write, IsTextVariable, Text, TextVariable, InputStreamVariable, CloseAtEnd.
Setup virtual file (1.6 SR-2) Setup virtual in-memory file. Selecting this option displays the dynamic parameters ResultVariable, Type, UseMemoryCache.
StreamType

Specify the type of stream object created. This parameter has the options listed in the following table.

Option Description
Normal Creates a file stream.
Memory Creates a memory stream.
Transfer Creates a transfer stream. This stream is useful for transferring data between processes. It allows for the simultaneous writing of data in one process and reading this same data in another process.

IsFilenameVariable

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

Option Description
True The file name is stored in a variable. Selecting this option displays the dynamic parameter FilenameVariable.
False The file name is specified in a File connection manager. Selecting this option displays the dynamic parameter Filename.
Filename

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

Related topics: File Connection Manager Editor

FilenameVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

OverwriteExisting

Use this parameter to indicate that the existing file should be overwritten.

Read

Specify the type of read from a stream object. This parameter has the options listed in the following table.

Option Description
Terminator Reads from the stream until a specified terminator combination is reached.
Length Reads a fixed length from the stream.

Terminator

Specify terminator, which controls when reading should stop.

Length

Specify a fixed length to read from the stream.

ResultVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

CloseAtEnd

Use this parameter to indicate that the stream should be closed at the end of the action.

Write

Specify what is to be written to the stream object. This parameter has the options listed in the following table.

Option Description
Text Writes text to a stream.
Input stream Writes another stream to the target stream.

IsTextVariable

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

Option Description
True The text is stored in a variable. Selecting this option displays the dynamic parameter TextVariable.
False The text is specified. Selecting this option displays the dynamic parameter Text.
Text

Specify the text to be written to the stream object.

TextVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

InputStreamVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

Type (1.6 SR-2)

Specify virtual file type. This parameter has the options listed in the following table.

Option Description
Read The virtual file will be used for reading (default).
Write The virtual file will be used for writing.
UseMemoryCache (1.6 SR-2)

Indicate whether memory cache is used for virtual file. This parameter has the options listed in the following table.

Option Description
True Memory cache is used.
False Memory cache is not used (default).

Samples

  • For a sample demonstrating the virtual file action, look at this package.

Jabber Task

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

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

All Downloads ----- Join VIP ----- Questions?

Overview

The Jabber Task is an SSIS Control Flow task for interaction with Jabber/XMPP clients.


Setup

Use the General page of the Jabber Task Editor dialog to configure the parameters needed to communicate with a client.

Options

Name

Specify the task name.

Description

Specify the task description.

Action

Specify the task action. This parameter has the options listed in the following table.

Option Description
Send message Sends a message. Selecting this action displays the dynamic parameters Type, IsRecipientVariable, Recipient, RecipientVariable, Thread, IsSubjectVariable, Subject, SubjectVariable, IsBodyVariable, Body, BodyVariable, IsHtmlVariable, Html, HtmlVariable.
Receive message Receives a message. Selecting this action displays the dynamic parameters ResultVariable, IsSenderVariable, Sender, SenderVariable, Timeout.
JabberConnection

Selects an existing Jabber connection manager, or click <New connection...> to create a new connection manager.

Related Topics: Jabber Connection Manager

Type

Specify the type of message to send. This parameter has the options listed in the following table.

Option Description
Normal Normal message.
Chat Chat (one-on-one) message.
Group chat Group chat message.
Headline Headline message.
Error Error message.

IsRecipientVariable

Indicate whether or not the recipient(s) are stored in a variable. This parameter has the options listed in the following table.

Option Description
True The recipient is stored in a variable. Selecting this option displays the dynamic parameter RecipientVariable.
False The recipient is specified. Selecting this option displays the dynamic parameter Recipient.
Recipient

Type in the recipient or select from the Jabber server roster list. Separate multiple recipients with a semicolon (;).

RecipientVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

Thread

Specify a message thread.

IsSubjectVariable

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

Option Description
True The message subject is stored in a variable. Selecting this option displays the dynamic parameter SubjectVariable.
False The message subject is specified. Selecting this option displays the dynamic parameter Subject.
Subject

Specify the message subject.

SubjectVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsBodyVariable

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

Option Description
True The message body is stored in a variable. Selecting this option displays the dynamic parameter BodyVariable.
False The message body is specified. Selecting this option displays the dynamic parameter Body.
Body

Specify the message body.

BodyVariable

Selects an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsHtmlVariable

Indicate whether or not the message HTML text is stored in a variable. This parameter has the options listed in the following table.

Option Description
True The message HTML text is stored in a variable. Selecting this option displays the dynamic parameter HtmlVariable.
False The message HTML text is specified. Selecting this option displays the dynamic parameter Html.
Html

Specify message HTML text.

HtmlVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

ResultVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

IsSenderVariable

Indicate whether or not the message sender(s) are stored in a variable. This parameter has the options listed in the following table.

Option Description
True The message sender is stored in a variable. Selecting this option displays the dynamic parameter SenderVariable.
False The message sender is specified. Selecting this option displays the dynamic parameter Sender.
Sender

Optional. This parameter is used to define the senders from whom messages will be accepted. If a sender is not in the list, messages will not be accepted from them. Type in the sender or select from the Jabber server roster list. Separate multiple senders with a semicolon (;).

SenderVariable

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable

Timeout

Specify maximum time in seconds to wait for a message to arrive. The default value of this parameter is 3600 (1 hour).

Syndicate content