Tags: 
1.4, Task

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

Set component to be processed dynamically.

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.

Type the filter regular expression.

Related Topics: Regular Expression Editor

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

Related topics: File Connection Manager

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

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.

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

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.

Specify flat file column delimiter.

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

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.

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

Related Topics: Integration Services Variables, Add Variable

Specify flat file columns count.

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.

Specify flat file ragged right column width.

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

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.

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

Related Topics: Integration Services Variables, Add Variable

Specify flat file columns count.

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.

Specify flat file ragged right column width.

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

Related Topics: Integration Services Variables, Add Variable


EDI Source

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

Related topics: File Connection Manager


Common Destination component properties

Set component to be processed dynamically.

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.

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

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

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.

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.

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

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.

Specify flat file column delimiter.

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

Set component to be processed dynamically.


Aggregate Transformation

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

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

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

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

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

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

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

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

Select variable containing fuzzy lookup reference table. Optional.

Related Topics: Integration Services Variables, Add Variable

Select variable containing fuzzy lookup match index table. Optional.

Related Topics: Integration Services Variables, Add Variable

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

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

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

Related Topics: Integration Services Variables, Add Variable


Lookup Plus Transformation

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

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

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

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

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

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

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

Related Topics: Integration Services Variables, Add Variable

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

Related Topics: Integration Services Variables, Add Variable

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

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

Related Topics: Integration Services Variables, Add Variable

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

Related Topics: Integration Services Variables, Add Variable

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

Related Topics: Integration Services Variables, Add Variable


Unpivot Transformation

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

Related Topics: Integration Services Variables, Add Variable

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

Related Topics: Integration Services Variables, Add Variable

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


FAQ

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.

  • 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.

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.