Overview
This page describes the dynamic capabilities of COZYROC Data Flow Task Plus. The task can dynamically modify source, destination and transformation 1.6 components at runtime. More detailed information for each component is provided below.
Supported Data Flow Components:
In the latest version of COZYROC's SSIS+, the following components are supported in dynamic data flows (for details see the What's New section in Data Flow Task Plus):
- All Microsoft SSIS components (including those in the SSIS Feature Pack for Azure)
- All COZYROC SSIS+ components
To enable components for dynamic processing, set the Enabled property to True in the Dynamic tab.
Parameters
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.
EDI Source
Select an existing File connection manager, or click <New connection...> to create a connection manager.
Related topics: File Connection Manager
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
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
Database Destination
Select variable containing ADO.NET DataSet or OLEDB Recordset object. The component requires configuration for the different parts of the database statement. There are three types of configuration rows: Insert, Update, Match
Which type of configuration will be used depends on the component Action:
- Insert - only Insert configuration rows are used.
- Update - Update and Match configuration rows are used.
- Delete - only Match configuration rows are used.
- Upsert - all configuration rows are used.
When configuration types not intended to be used for a given Action are specified, they are ignored. When no configuration is specified for a type, the columns are mapped automatically by matching column names. When configuration rows are provided, the automatic columns mapping is disabled. To enable them for the remaining columns the special keyword
##auto##should be used as input column name, external column name is skipped, mapping type must be specified. A column can be defined as ignored by setting null as external column name. Type must be specified. The defined input column will not be mapped for the specified mapping type. Statically mapped columns are preserved and mapped before the configuration and automatic mappings. It is important to map static columns in all mapping types.When the Action is Update or Upsert the Match mappings should be specified only explicitly or with static columns. These mappings corresponds to the Primary Key columns and cannot be made automatically. If automatic mapping is specified and all columns have same names, all columns will be mapped. There is no table where all columns are Primary Key columns.
When the Action is Delete the automatic mapping can be used when the input columns are only the Primary Key columns.
Insert example, two mappings specified, automatic match for the remaining columns:
Input External Type ##auto## NULL * Insert Info Description Insert HomePhone PersonalPhone Insert - NULL means the null value, not a text.
Upsert example, automatic Insert and Update configuration, explicit Match configuration:
Input External Type ID ID Match Upsert example, automatic Insert, explicit Update configuration (to update only the specified columns), explicit Match configuration:
Input External Type Description Description Update Amount Amount Update ID ID Match Related Topics: Integration Services Variables - Add Variable, ADO.NET DataSet, OLEDB Recordset
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
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[(http://msdn.microsoft.com/en-us/library/ms681510.aspx)
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.
Related Topics: Integration Services Variables, Add Variable
Knowledge Base
Samples
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.