Transformation Custom Properties

Aggregate

Export Column

Row Count

Cache Transform

Fuzzy Lookup

Script Component

Conditional Split

Lookup

Sort

Data Conversion

OLE DB Command

Term Lookup

Derived Column

Pivot

AutoExtendFactor

Integer

A value between 1 and 100 that specifies the percentage by which memory can be extended during the aggregation. The default value of this property is 25.

CountDistinctScale

Integer (enumeration)

A value that describes the approximate number of distinct values in a column that the aggregation can count.

This property can have one of the following values:

• Low (1)—indicates up to 500,000 key values

• Medium (2)—indicates up to 5 million key values

• High (3)—indicates more than 25 million key values.

• Unspecified (0)—indicates no CountDistinctScale value is used. Using the Unspecified (0) option may affect performance in large data sets.

KeyScale

Integer (enumeration)

A value that describes approximately how many Group By key values the aggregation can write.

This property can have one of the following values:

• Low (1)—indicates up to 500,000 key values.

• Medium (2)—indicates up to 5 million key values.

• High (3)—indicates more than 25 million key values.

• Unspecified (0)—indicates that no KeyScale value is used.

Keys

Integer

A value that specifies the exact number of Group By keys that the aggregation can write. If a KeyScale value is specified, the value in Keys takes precedence.

AggregationColumnId

Integer

The LineageID of a column that participates in GROUP BY or aggregate functions.

AggregationType

Integer (enumeration)

A value that specifies the aggregation operation to be performed on the column.

This property can have one of the following values:

• Count (1)

• Count all (2)

• Countdistinct (3)

• Sum (4)

• Average (5)

• Maximum (7)

• Minimum (6)

• Group by (0)

CountDistinctScale

Integer (enumeration)

When the aggregation type is Count distinct, a value that describes approximately how many key values the aggregation can write.

This property can have one of the following values:

• Low (1)—indicates up to 500,000 key values,

• Medium (2)—indicates up to 5 million key values,

• High (3)—indicates more than 25 million key values.

• Unspecified (0)—indicates no CountDistinctScale value is used.

LineageItemSelected

Integer (enumeration)

The audit item selected for output.

This property can have one of the following values:

• Execution instance GUID (0)

• Execution start time (4)

• Machine name (5)

• Package ID (1)

• Package name (2)

• Task ID (8)

• Task name (7)

• User name (6)

• Version ID (3)

Connectionmanager

String

Specifies the name of the connection manager.

AvailableInputColumns

String

A list of the available input columns.

CacheColumnName

String

Specifies the name of the column that is mapped to a selected input column.

The name of the column in the CacheColumnName property must match the name of the corresponding column listed on the Columns page of the Cache Connection Manager Editor.

For more information, see Cache Connection Manager Editor

InputColumnLineageId

Integer

A value that specifies the LineageID of the input column that is the source of the output column.

EvaluationOrder

Integer

A value that specifies the position of a condition, associated with an output, in the list of conditions that the Conditional Split transformation evaluates. The conditions are evaluated in order from the lowest to the highest value.

FriendlyExpression

String

An expression that represents the condition that the Conditional Split transformation evaluates. Columns are represented by their names.

The value of this property can be specified by using a property expression.

copyColumnId

Integer

The LineageID of the input column from which the output column is copied.

FastParse

Boolean

A value that indicates whether the column uses the quicker, but locale-insensitive, fast parsing routines that Integration Services provides, or the locale-sensitive standard parsing routines. The default value of this property is False. For more information, see Fast Parse and Standard Parse. .

Note

This property is not available in the Data Conversion Transformation Editor, but can be set by using the Advanced Editor.

ASConnectionId

String

The unique identifier of the connection object.

CatalogName

String

The name of an Analysis Services database.

ModelStructureName

String

The name of the mining structure.

QueryText

String

The prediction query statement that the transformation uses.

Expression

String

An expression that represents the condition that the Conditional Split transformation evaluates. Columns are represented by the LineageID property for the column.

AllowAppend

Boolean

A value that specifies whether the transformation appends data to an existing file. The default value of this property is False.

FileDataColumnID

Integer

A value that identifies the column that contains the data that the transformation inserts into a file. On the Extract Column, this property has a value of 0; on the File Path Column, this property contains the LineageID of the Extract Column.

ExpectBOM

Boolean

A value that specifies whether the Import Column transformation expects a byte-order mark (BOM). A BOM is only expected if the data has the DT_NTEXT data type.

Delimiters

String

The token delimiters that the transformation uses. The default delimiters include the following characters: space ( ), comma(,), period (.), semicolon (;), colon (:), hyphen (-), double straight quotation mark ("), single straight quotation mark ('), ampersand (&), slash mark (/), backslash (\), at sign (@), exclamation point (!), question mark (?), opening parenthesis ((), closing parenthesis ()), less than (<), greater than (>), opening bracket ([), closing bracket (]), opening brace ({), closing brace (}), pipe (|), number sign (#), asterisk (*), caret (^), and percent (%).

MaxMemoryUsage

Integer

The maximum amount of memory for use by the transformation. The default value of this property is 0, which enables dynamic memory usage.

The value of this property can be specified by using a property expression.

Note

This property is not available in the Fuzzy Grouping Transformation Editor, but can be set by using the Advanced Editor.

ExactFuzzy

Integer (enumeration)

A value that specifies whether the transformation performs a fuzzy match or an exact match. The valid values are Exact and Fuzzy. The default value for this property is Fuzzy.

LeadingTrailingNumeralsSignificant

Integer (enumeration)

A value that specifies the significance of numerals.

This property can have one of the following values:

• LeadingNumeralsSignificant (1)—use if leading numerals are significant.

• TrailingNumeralsSignificant (2)—use if trailing numerals are significant.

• LeadingAndTrailingNumeralsSignificant (3)—use if both leading and trailing numerals are significant.

• NumeralsNotSpecial (0)—use if numerals are not significant.

ToBeCleaned

Boolean

A value that specifies whether the column is used to identify duplicates; that is, whether this is a column on which you are grouping. The default value of this property is False.

ColumnType

Integer (enumeration)

A value that identifies the type of output column.

This property can have one of the following values:

• KeyIn (1)

• KeyOut (2)

• Similarity (3)

• ColumnSimilarity (4)

• PassThru (5)

• Canonical (6)

• Undefined (0)

CopyReferenceTable

Boolean

Specifies whether a copy of the reference table should be made for fuzzy lookup index construction and subsequent lookups. The default value of this property is True.

DropExistingMatchIndex

Boolean

A value that specifies whether the match index specified in MatchIndexName is deleted when MatchIndexOptions is not set to ReuseExistingIndex. The default value for this property is True.

MatchIndexName

String

The name of the match index. The match index is the table in which the transformation creates and saves the index that it uses. If the match index is reused, MatchIndexName specifies the index to reuse. MatchIndexName must be a valid SQL Server identifier name. For example, if the name contains spaces, the name must be enclosed in brackets.

MaxMemoryUsage

Integer

The maximum cache size for the lookup table. The default value of this property is 0, which means the cache size has no limit.

The value of this property can be specified by using a property expression.

Note

This property is not available in the Fuzzy Lookup Transformation Editor, but can be set by using the Advanced Editor.

MinSimilarity

Integer

The similarity threshold that the transformation uses at the component level, specified as a value between 0 and 1. Only rows greater than the threshold qualify as matches.

ReferenceTableName

String

The name of the lookup table. The name must be a valid SQL Server identifier name. For example, if the name contains spaces, the name must be enclosed in brackets.

FuzzyComparisonFlags

Integer

A value that specifies how the transformation compares the string data in a column. For more information, see Comparing String Data.

JoinToReferenceColumn

String

A value that specifies the name of the column in the reference table to which the column is joined.

MinSimilarity

Double

The similarity threshold that the transformation uses at the column level, specified as a value between 0 and 1. Only rows greater than the threshold qualify as matches.

ColumnType

Integer (enumeration)

A value that identifies the type of output column for columns that the transformation adds to the output.

This property can have one of the following values:

• Similarity (1)

• Confidence (2)

• ColumnSimilarity (3)

• Undefined (0)

SourceInputColumnLineageId

Integer

A value that identifies the input column that provides values to this output column.

CacheType

Integer (enumeration)

The cache type for the lookup table. The values are Full (0), Partial (1), and None (2). The default value of this property is Full.

MaxMemoryUsage

Integer

The maximum cache size for the lookup table. The default value of this property is 25, which means that the cache size has no limit.

NoMatchBehavior

Integer (enumeration)

A value that specifies whether rows without matching entries in the reference dataset are treated as errors.

When the property is set to Treat rows with no matching entries as errors (0), the rows without matching entries are treated as errors. You can specify what should happen when this type of error occurs by using the Error Output page of the Lookup Transformation Editor dialog box. For more information, see Lookup Transformation Editor (Error Output Page).

When the property is set to Send rows with no matching entries to the no match output (1), the rows are not treaded as errors.

The default value is Treat rows with no matching entries as errors (0).

ReferenceMetaDataXML

String

Metadata for the columns in the lookup table that the transformation copies to its output.

SqlCommandParam

String

The parameterized SQL statement that populates the lookup table.

CopyFromReferenceColumn

String

The name of the column in the reference table from which a column is copied.

CopyFromReferenceColumn

String

The name of the column in the reference table from which a column is copied.

JoinType

Integer (enumeration)

Specifies whether the join is an inner (2), left outer (1), or full join (0).

NumKeyColumns

Integer

The number of columns that are used in the join.

InputColumnID

Integer

The LineageID of the input column from which data is copied to this output column.

CommandTimeout

Integer

The maximum number of seconds that the SQL command can run before timing out. A value of 0 indicates an infinite time. The default value of this property is 0.

SQLCommand

String

The Transact-SQL statement that the transformation runs for each row in the data flow.

The value of this property can be specified by using a property expression.

DBParamInfoFlag

Integer (bitmask)

A set of flags that describe parameter characteristics. For more information, see the DBPARAMFLAGSENUM in the OLE DB documentation in the MSDN Library.

SamplingSeed

Integer

The seed the random number generator uses. The default value of this property is 0, indicating that the transformation uses a tick count.

Selected

Boolean

Designates the output to which the sampled rows are directed. On the selected output, Selected is set to True, and on the unselected output, Selected is set to False.

PivotUsage

Integer (enumeration)

A value that specifies the role of a column when the data set is pivoted.

PivotKeyValue

String

One of the possible values from the column that is marked as the pivot key by the value of its PivotUsage property.

The value of this property can be specified by using a property expression.

VariableName

String

The name of the variable that holds the row count.

SamplingSeed

Integer

The seed that the random number generator uses. The default value of this property is 0, indicating that the transformation uses a tick count.

Selected

Boolean

Designates the output to which the sampled rows are directed. On the selected output, Selected is set to True, and on the unselected output, Selected is set to False.

InputColumnLineageId

Integer

A value that specifies the LineageID of the input column that is the source of the output column.

ReadOnlyVariables

String

A comma-separated list of variables available to the Script Component for read-only access.

CurrentRowWhere

String

The WHERE clause in the SELECT statement that selects the current row among rows with the same business key.

FailOnFixedAttributeChange

Boolean

A value that specifies whether the transformation fails when row columns with fixed attributes contain changes or the lookup in the dimension table fails. If you expect incoming rows to contain new records, set this value to True to make the transformation continue after the lookup fails, because the transformation uses the failure to identify new records. The default value of this property is False.

IncomingRowChangeType

Integer

A value that specifies whether all incoming rows are new rows, or whether the transformation should detect the type of change.

SQLCommand

String

The SQL statement used to create a schema rowset.

ColumnType

Integer (enumeration)

The update type of the column. The values are: Changing Attribute (2), Fixed Attribute (4), Historical Attribute (3), Key (1), and Other (0).

EliminateDuplicates

Boolean

Specifies whether the transformation removes duplicate rows from the transformation output. The default value of this property is False.

NewComparisonFlags

Integer (bitmask)

A value that specifies how the transformation compares the string data in a column. For more information, see Comparing String Data.

SortColumnID

Integer

The LineageID of a sort column.

FrequencyThreshold

Integer

A numeric value that indicates the number of times a term must occur before it is extracted. The default value of this property is 2.

MaxLengthOfTerm

Integer

A numeric value that indicates the maximum length of a term. This property applies only to phrases. The default value of this property is 12.

OutTermColumn

String

The name of the column that contains the exclusion terms.

ScoreType

Integer

A value that specifies the type of score to associate with the term. Valid values are 0 indicating frequency, and 1 indicating a TFIDF score. The TFIDF score is the product of Term Frequency and Inverse Document Frequency, defined as: TFIDF of a Term T = (frequency of T) * log( (#rows in Input) / (#rows having T) ). The default value of this property is 0.

IsCaseSensitive

Boolean

A value that specifies whether a case sensitive comparison applies to the match of the input column text and the lookup term. The default value of this property is False.

RefTermTable

String

The name of the table that contains the column with lookup terms.

InputColumnType

Integer

A value that specifies the use of the column. Valid values are 0 for a passthrough column, 1 for a lookup column, and 2 for a column that is both a passthrough and a lookup column.

CustomLineageID

Integer

The LineageID of the corresponding input column if the InputColumnType of that column is 0 or 2.

DestinationColumn

Integer

The LineageID of the output column to which the input column maps. A value of -1 indicates that the input column is not mapped to an output column.

PivotKey

Boolean

Indicates whether the values in the PivotKeyValue property of input columns are written to this output column.

In the Unpivot scenario described in Unpivot Transformation, the Pivot Value column name is Product and designates the new Product column into which the Ham, Coke, Milk, Beer, and Chips columns are unpivoted.

• Added numeric values for the following properties:

  • The CountDistinctScale, KeyScale, AggregationType, and CountDistinctScale properties of the Aggregate transformation.

  • The CacheType and NoMatchBehavior properties of the Lookup transformation.

  • The MatchIndexOptions property of the Fuzzy Lookup transformation.

  • The JoinType property of the Merge Join transformation.

• Added the valid values for the following properties:

  • The LineageItemSelected property of the Audit transformation.

  • The MapFlags property of the Character Map transformation.

  • The ExactFuzzy, FuzzyComparisonFlags, LeadingTrailingNumeralsSignificant, and ColumnType properties of the Fuzzy Grouping transformation.

  • The ColumnType property of the Fuzzy Lookup transformation.