TechNet
Export (0) Print
Expand All

Transformation Custom Properties

 

In addition to the properties that are common to most data flow objects in the SQL Server Integration Services object model, many data flow objects have custom properties that are specific to the object. These custom properties are available only at run time, and are not documented in the Integration Services Managed Programming Reference Documentation.

This topic lists and describes the custom properties of the various data flow transformations. For information about the properties common to most data flow objects, see Common Properties.

Some properties of transformations can be set by using property expressions. For more information, see Data Flow Properties that Can Be Set by Using Expressions.

AggregateExport ColumnRow Count
AuditFuzzy GroupingRow Sampling
Cache TransformFuzzy LookupScript Component
Character MapImport ColumnSlowly Changing Dimension
Conditional SplitLookupSort
Copy ColumnMerge JoinTerm Extraction
Data ConversionOLE DB CommandTerm Lookup
Data Mining QueryPercentage SamplingUnpivot
Derived ColumnPivot

Transformations Without Custom Properties

The following transformations have no custom properties at the component, input, or output levels: Merge Transformation, Multicast Transformation, and Union All Transformation. They use only the properties common to all data flow components.

The Aggregate transformation has both custom properties and the properties common to all data flow components.

The following table describes the custom properties of the Aggregate transformation. All properties are read/write.

PropertyData typeDescription
AutoExtendFactorIntegerA 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.
CountDistinctKeysIntegerA value that specifies the exact number of distinct counts that the aggregation can write. If a CountDistinctScale value is specified, the value in CountDistinctKeys takes precedence.
CountDistinctScaleInteger (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.
KeysIntegerA value that specifies the exact number of Group By keys that the aggregation writes. If a KeyScalevalue is specified, the value in Keys takes precedence.
KeyScaleInteger (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.

The following table describes the custom properties of the output of the Aggregate transformation. All properties are read/write.

PropertyData typeDescription
KeysIntegerA 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.
KeyScaleInteger (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 no KeyScale value is used.

The following table describes the custom properties of the output columns of the Aggregate transformation. All properties are read/write.

PropertyData typeDescription
AggregationColumnIdIntegerThe LineageID of a column that participates in GROUP BY or aggregate functions.
AggregationComparisonFlagsIntegerA value that specifies how the Aggregate transformation compares string data in a column. For more information, see Comparing String Data.
AggregationTypeInteger (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)
CountDistinctKeysIntegerWhen the aggregation type is Count distinct, a value that specifies the exact number of keys that the aggregation can write. If a CountDistinctScale value is specified, the value in CountDistinctKeys takes precedence.
CountDistinctScaleInteger (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.
IsBigBooleanA value that indicates whether the column contains a value larger than 4 billion or a value with more precision than a double-precision floating-point value. The value can be 0 or 1. 0 indicates that IsBig is False and the column does not contain a large value or precise value. The default value of this property is 1.

The input and the input columns of the Aggregate transformation have no custom properties.

For more information, see Aggregate Transformation.

The Audit transformation has only the properties common to all data flow components at the component level.

The following table describes the custom properties of the output columns of the Audit transformation. All properties are read/write.

Property nameData typeDescription
LineageItemSelectedInteger (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)

The input, the input columns, and the output of the Audit transformation have no custom properties.

For more information, see Audit Transformation.

The Cache Transform transformation has both custom properties and the properties common to all data flow components.

The following table describes the properties of the Cache Transform transformation. All properties are read/write.

PropertyData typeDescription
ConnectionmanagerStringSpecifies the name of the connection manager.
ValidateExternalMetadataBooleanIndicates whether the Cache Transform is validated by using external data sources at design time. If the property is set to False, validation against external data sources occurs at run time.

The default value it True.
AvailableInputColumnsStringA list of the available input columns.
InputColumnsStringA list of the selected input columns.
CacheColumnNameStringSpecifies 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

The Character Map transformation has only the properties common to all data flow components at the component level.

The following table describes the custom properties of the output columns of the Character Map transformation. All properties are read/write.

PropertyData typeDescription
InputColumnLineageIdIntegerA value that specifies the LineageID of the input column that is the source of the output column.
MapFlagsInteger (enumeration)A value that specifies the string operations that the Character Map transformation performs on the column. This property can have one of the following values:

 Byte reversal (2)

 Full width (6)

 Half width (5)

 Hiragana (3)

 Katakana (4)

 Linguistic casing (7)

 Lowercase (0)

 Simplified Chinese (8)

 Traditional Chinese(9)

 Uppercase (1)

The input, the input columns, and the output of the Character Map transformation have no custom properties.

For more information, see Character Map Transformation.

The Conditional Split transformation has only the properties common to all data flow components at the component level.

The following table describes the custom properties of the output of the Conditional Split transformation. All properties are read/write.

PropertyData typeDescription
EvaluationOrderIntegerA 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.
ExpressionStringAn expression that represents the condition that the Conditional Split transformation evaluates. Columns are represented by lineage identifiers.
FriendlyExpressionStringAn 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.
IsDefaultOutBooleanA value that indicates whether the output is the default output.

The input, the input columns, and the output columns of the Conditional Split transformation have no custom properties.

For more information, see Conditional Split Transformation.

The Copy Column transformation has only the properties common to all data flow components at the component level.

The following table describes the custom properties of the output columns of the Copy Column transformation. All properties are read/write.

Property nameData typeDescription
copyColumnIdIntegerThe LineageID of the input column from which the output column is copied.

The input, the input columns, and the output of the Copy Column transformation have no custom properties.

For more information, see Copy Column Transformation.

The Data Conversion transformation has only the properties common to all data flow components at the component level.

The following table describes the custom properties of the output columns of the Data Conversion transformation. All properties are read/write.

PropertyData typeDescription
FastParseBooleanA 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.
SourceInputColumnLineageIdIntegerThe LineageID of the input column that is the source of the output column.

The input, the input columns, and the output of the Data Conversion transformation have no custom properties.

For more information, see Data Conversion Transformation.

The Data Mining Query transformation has both custom properties and the properties common to all data flow components.

The following table describes the custom properties of the Data Mining Query transformation. All properties are read/write.

PropertyData typeDescription
ASConnectionIdStringThe unique identifier of the connection object.
ASConnectionStringStringThe connection string to an Analysis Services project or an Analysis Services database.
CatalogNameStringThe name of an Analysis Services database.
ModelNameStringThe name of the data mining model.
ModelStructureNameStringThe name of the mining structure.
ObjectRefStringAn XML tag that identifies the data mining structure that the transformation uses.
QueryTextStringThe prediction query statement that the transformation uses.

The input, the input columns, the output, and the output columns of the Data Mining Query transformation have no custom properties.

For more information, see Data Mining Query Transformation.

The Derived Column transformation has only the properties common to all data flow components at the component level.

The following table describes the custom properties of the input columns and output columns of the Derived Column transformation. When you choose to add the derived column as a new column, these custom properties apply to the new output column; when you choose to replace the contents of an existing input column with the derived results, these custom properties apply to the existing input column. All properties are read/write.

PropertyData typeDescription
ExpressionStringAn expression that represents the condition that the Conditional Split transformation evaluates. Columns are represented by the LineageID property for the column.
FriendlyExpressionStringAn 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.

The input and output of the Derived Column transformation have no custom properties.

For more information, see Derived Column Transformation.

The Export Column transformation has only the properties common to all data flow components at the component level.

The following table describes the custom properties of the input columns of the Export Column transformation. All properties are read/write.

PropertyData typeDescription
AllowAppendBooleanA value that specifies whether the transformation appends data to an existing file. The default value of this property is False.
ForceTruncateBooleanA value that specifies whether the transformation truncates an existing files before writing data. The default value of this property is False.
FileDataColumnIDIntegerA 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.
WriteBOMBooleanA value that specifies whether a byte-order mark (BOM) is written to the file.

The input, the output, and the output columns of the Export Column transformation have no custom properties.

For more information, see Export Column Transformation.

The Import Column transformation has only the properties common to all data flow components at the component level.

The following table describes the custom properties of the input columns of the Import Column transformation. All properties are read/write.

PropertyData typeDescription
ExpectBOMBooleanA 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.
FileDataColumnIDIntegerA value that identifies the column that contains the data that the transformation inserts into the data flow. On the column of data to be inserted, this property has a value of 0; on the column that contains the source file paths, this property contains the LineageID of the column of data to be inserted.

The input, the output, and the output columns of the Import Column transformation have no custom properties.

For more information, see Import Column Transformation.

The Fuzzy Grouping transformation has both custom properties and the properties common to all data flow components.

The following table describes the custom properties of the Fuzzy Grouping transformation. All properties are read/write.

PropertyData typeDescription
DelimitersStringThe 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 (%).
ExhaustiveBooleanA value that specifies whether each input record is compared to every other input record. The value of True is intended mostly for debugging purposes. The default value of this property is False.

Note: This property is not available in the Fuzzy Grouping Transformation Editor, but can be set by using the Advanced Editor.
MaxMemoryUsageIntegerThe 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.
MinSimilarityDoubleThe similarity threshold that the transformation uses to identify duplicates, expressed as a value between 0 and 1. The default value of this property is 0.8.

The following table describes the custom properties of the input columns of the Fuzzy Grouping transformation. All properties are read/write.

PropertyData typeDescription
ExactFuzzyInteger (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.
FuzzyComparisonFlagsInteger (enumeration)A value that specifies how the transformation compares the string data in a column. This property can have one of the following values:

 FullySensitive

 IgnoreCase

 IgnoreKanaType

 IgnoreNonSpace

 IgnoreSymbols

 IgnoreWidth

 

For more information, see Comparing String Data.
LeadingTrailingNumeralsSignificantInteger (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.
MinSimilarityDoubleThe similarity threshold used for the join on the column, specified as a value between 0 and 1. Only rows greater than the threshold qualify as matches.
ToBeCleanedBooleanA 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.

The following table describes the custom properties of the output columns of the Fuzzy Grouping transformation. All properties are read/write.

Property nameData typeDescription
ColumnTypeInteger (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)
InputIDIntegerThe LineageID of the corresponding input column.

The input and output of the Fuzzy Grouping transformation have no custom properties.

For more information, see Fuzzy Grouping Transformation.

The Fuzzy Lookup transformation has both custom properties and the properties common to all data flow components.

The following table describes the custom properties of the Fuzzy Lookup transformation. All properties except for ReferenceMetadataXML are read/write.

PropertyData typeDescription
CopyReferenceTableBooleanSpecifies 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.
DelimitersStringThe delimiters that the transformation uses to tokenize column values. 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 (%).
DropExistingMatchIndexBooleanA 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.
ExhaustiveBooleanA value that specifies whether each input record is compared to every other input record. The value of True is intended mostly for debugging purposes. The default value of this property is False.

Note: This property is not available in the Fuzzy Lookup Transformation Editor, but can be set by using the Advanced Editor.
MatchIndexNameStringThe 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.
MatchIndexOptionsInteger (enumeration)A value that specifies how the transformation manages the match index. This property can have one of the following values:

 ReuseExistingIndex (0)

 GenerateNewIndex (1)

 GenerateAndPersistNewIndex (2)

 GenerateAndMaintainNewIndex (3)
MaxMemoryUsageIntegerThe 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.
MaxOutputMatchesPerInputIntegerThe maximum number of matches the transformation can return for each input row. The default value of this property is 1.

Note: Values greater than 100 can only be specified by using the Advanced Editor.
MinSimilarityIntegerThe 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.
ReferenceMetadataXMLStringIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
ReferenceTableNameStringThe 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.
WarmCachesBooleanWhen true, the lookup partially loads the index and reference table into memory before execution begins. This can enhance performance.

The following table describes the custom properties of the input columns of the Fuzzy Lookup transformation. All properties are read/write.

PropertyData typeDescription
FuzzyComparisonFlagsIntegerA value that specifies how the transformation compares the string data in a column. For more information, see Comparing String Data.
FuzzyComparisonFlagsExInteger (enumeration)A value that specifies which extended comparison flags the transformation uses. The values can include MapExpandLigatures, MapFoldCZone, MapFoldDigits, MapPrecomposed, and NoMapping. NoMapping cannot be used with other flags.
JoinToReferenceColumnStringA value that specifies the name of the column in the reference table to which the column is joined.
JoinTypeIntegerA value that specifies whether the transformation performs a fuzzy or an exact match. The default value for this property is Fuzzy. The integer value for the exact join type is 1 and the value for the fuzzy join type is 2.
MinSimilarityDoubleThe 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.

The following table describes the custom properties of the output columns of the Fuzzy Lookup transformation. All properties are read/write.

System_CAPS_ICON_note.jpg Note


For output columns that contain passthrough values from the corresponding input columns, CopyFromReferenceColumn is empty and SourceInputColumnLineageID contains the LineageID of the corresponding input column. For output columns that contain lookup results, CopyFromReferenceColumn contains the name of the lookup column, and SourceInputColumnLineageID is empty.

PropertyData typeDescription
ColumnTypeInteger (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)
CopyFromReferenceColumnStringA value that specifies the name of the column in the reference table that provides the value in an output column.
SourceInputColumnLineageIdIntegerA value that identifies the input column that provides values to this output column.

The input and output of the Fuzzy Lookup transformation have no custom properties.

For more information, see Fuzzy Lookup Transformation.

The Lookup transformation has both custom properties and the properties common to all data flow components.

The following table describes the custom properties of the Lookup transformation. All properties except for ReferenceMetadataXML are read/write.

PropertyData typeDescription
CacheTypeInteger (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.
DefaultCodePageIntegerThe default code page to use when code page information is not available from the data source.
MaxMemoryUsageIntegerThe maximum cache size for the lookup table. The default value of this property is 25, which means that the cache size has no limit.
MaxMemoryUsage64IntegerThe maximum cache size for the lookup table on a 64-bit computer.
NoMatchBehaviorInteger (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).
ParameterMapStringA semicolon-delimited list of lineage IDs that map to the parameters used in the SqlCommand statement.
ReferenceMetaDataXMLStringMetadata for the columns in the lookup table that the transformation copies to its output.
SqlCommandStringThe SELECT statement that populates the lookup table.
SqlCommandParamStringThe parameterized SQL statement that populates the lookup table.

The following table describes the custom properties of the input columns of the Lookup transformation. All properties are read/write.

PropertyData typeDescription
CopyFromReferenceColumnStringThe name of the column in the reference table from which a column is copied.
JoinToReferenceColumnsStringThe name of the column in the reference table to which a source column joins.

The following table describes the custom properties of the output columns of the Lookup transformation. All properties are read/write.

Property nameData typeDescription
CopyFromReferenceColumnStringThe name of the column in the reference table from which a column is copied.

The input and output of the Lookup transformation have no custom properties.

For more information, see Lookup Transformation.

The Merge Join transformation has both custom properties and the properties common to all data flow components.

The following table describes the custom properties of the Merge Join transformation.

PropertyData typeDescription
JoinTypeInteger (enumeration)Specifies whether the join is an inner (2), left outer (1), or full join (0).
MaxBuffersPerInputIntegerYou no longer have to configure the value of the MaxBuffersPerInput property because Microsoft has made changes that reduce the risk that the Merge Join transformation will consume excessive memory. This problem sometimes occurred when the multiple inputs of the Merge Join produced data at uneven rates.
NumKeyColumnsIntegerThe number of columns that are used in the join.
TreatNullsAsEqualBooleanA value that specifies whether the transformation handles null values as equal values. The default value of this property is True. If the property value is False, the transformation handles null values like SQL Server does.

The following table describes the custom properties of the output columns of the Merge Join transformation. All properties are read/write.

Property nameData typeDescription
InputColumnIDIntegerThe LineageID of the input column from which data is copied to this output column.

The input, the input columns, and the output of the Merge Join transformation have no custom properties.

For more information, see Merge Join Transformation.

The OLE DB Command transformation has both custom properties and the properties common to all data flow components.

The following table describes the custom properties of the OLE DB Command transformation.

Property nameData typeDescription
CommandTimeoutIntegerThe 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.
DefaultCodePageIntegerThe code page to use when code page information is unavailable from the data source.
SQLCommandStringThe 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.

The following table describes the custom properties of the external columns of the OLE DB Command transformation. All properties are read/write.

Property nameData typeDescription
DBParamInfoFlagInteger (bitmask)A set of flags that describe parameter characteristics. For more information, see the DBPARAMFLAGSENUM in the OLE DB documentation in the MSDN Library.

The input, the input columns, the output, and the output columns of the OLE DB Command transformation have no custom properties.

For more information, see OLE DB Command Transformation.

The Percentage Sampling transformation has both custom properties and the properties common to all data flow components.

The following table describes the custom properties of the Percentage Sampling transformation.

PropertyData typeDescription
SamplingSeedIntegerThe seed the random number generator uses. The default value of this property is 0, indicating that the transformation uses a tick count.
SamplingValueIntegerThe size of the sample as a percentage of the source.

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

The following table describes the custom properties of the outputs of the Percentage Sampling transformation. All properties are read/write.

Property nameData typeDescription
SelectedBooleanDesignates 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.

The input, the input columns, and the output columns of the Percentage Sampling transformation have no custom properties.

For more information, see Percentage Sampling Transformation.

The following table describes the custom component properties of the Pivot transformation.

PropertyData typeDescription
PassThroughUnmatchedPivotKeytsBooleanSet to True to configure the Pivot transformation to ignore rows containing unrecognized values in the Pivot Key column and to output all of the pivot key values to a log message, when the package is run.

The following table describes the custom properties of the input columns of the Pivot transformation. All properties are read/write.

PropertyData typeDescription
PivotUsageInteger (enumeration)One of the following values that specify the role of a column when the data set is pivoted:

 0: The column is not pivoted, and the column values are passed through to the transformation output.

 1: The column is part of the set key that identifies one or more rows as part of one set. All input rows with the same set key are combined into one output row.

 2: The column is a pivot column. At least one column is created from each column value.

 3: The values from this column are put in columns that are created as a result of the pivot.

The following table describes the custom properties of the output columns of the Pivot transformation. All properties are read/write.

PropertyData typeDescription
PivotKeyValueStringOne 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.
SourceColumnIntegerThe LineageID of an input column that contains a pivoted value, or -1. The value of -1 indicates that the column is not used in a pivot operation.

For more information, see Pivot Transformation.

The Row Count transformation has both custom properties and the properties common to all data flow components.

The following table describes the custom properties of the Row Count transformation. All properties are read/write.

Property nameData typeDescription
VariableNameStringThe name of the variable that holds the row count.

The input, the input columns, the output, and the output columns of the Row Count transformation have no custom properties.

For more information, see Row Count Transformation.

The Row Sampling transformation has both custom properties and the properties common to all data flow components.

The following table describes the custom properties of the Row Sampling transformation. All properties are read/write.

PropertyData typeDescription
SamplingSeedIntegerThe seed that the random number generator uses. The default value of this property is 0, indicating that the transformation uses a tick count.
SamplingValueIntegerThe row count of the sample.

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

The following table describes the custom properties of the outputs of the Row Sampling transformation. All properties are read/write.

Property nameData typeDescription
SelectedBooleanDesignates 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.

The following table describes the custom properties of the output columns of the Row Sampling transformation. All properties are read/write.

PropertyData typeDescription
InputColumnLineageIdIntegerA value that specifies the LineageID of the input column that is the source of the output column.

The input and input columns of the Row Sampling transformation have no custom properties.

For more information, see Row Sampling Transformation.

The Script Component has both custom properties and the properties common to all data flow components. The same custom properties are available whether the Script Component functions as a source, transformation, or destination.

The following table describes the custom properties of the Script Component. All properties are read/write.

Property nameData typeDescription
ReadOnlyVariablesStringA comma-separated list of variables available to the Script Component for read-only access.
ReadWriteVariablesStringA comma-separated list of variables available to the Script Component for read/write access.

The input, the input columns, the output, and the output columns of the Script Component have no custom properties, unless the script developer creates custom properties for them.

For more information, see Script Component.

The Slowly Changing Dimension transformation has both custom properties and the properties common to all data flow components.

The following table describes the custom properties of the Slowly Changing Dimension transformation. All properties are read/write.

PropertyData typeDescription
CurrentRowWhereStringThe WHERE clause in the SELECT statement that selects the current row among rows with the same business key.
EnableInferredMemberBooleanA value that specifies whether inferred member updates are detected. The default value of this property is True.
FailOnFixedAttributeChangeBooleanA 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.
FailOnLookupFailureBooleanA value that specifies whether the transformation fails when a lookup of an existing record fails. The default value of this property is False.
IncomingRowChangeTypeIntegerA value that specifies whether all incoming rows are new rows, or whether the transformation should detect the type of change.
InferredMemberIndicatorStringThe column name for the inferred member.
SQLCommandStringThe SQL statement used to create a schema rowset.
UpdateChangingAttributeHistoryBooleanA value that indicates whether historical attribute updates are directed to the transformation output for changing attribute updates.

The following table describes the custom properties of the input columns of the Slowly Changing Dimension transformation. All properties are read/write.

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

The input, the outputs, and the output columns of the Slowly Changing Dimension transformation have no custom properties.

For more information, see Slowly Changing Dimension Transformation.

The Sort transformation has both custom properties and the properties common to all data flow components.

The following table describes the custom properties of the Sort transformation. All properties are read/write.

PropertyData typeDescription
EliminateDuplicatesBooleanSpecifies whether the transformation removes duplicate rows from the transformation output. The default value of this property is False.
MaximumThreadsIntegerContains the maximum number of threads the transformation can use for sorting. A value of 0 indicates an infinite number of threads. The default value of this property is 0.

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

The following table describes the custom properties of the input columns of the Sort transformation. All properties are read/write.

PropertyData typeDescription
NewComparisonFlagsInteger (bitmask)A value that specifies how the transformation compares the string data in a column. For more information, see Comparing String Data.
NewSortKeyPositionIntegerA value that specifies the sort order of the column. A value of 0 indicates that the data is not sorted on this column.

The following table describes the custom properties of the output columns of the Sort transformation. All properties are read/write.

PropertyData typeDescription
SortColumnIDIntegerThe LineageID of a sort column.

The input and output of the Sort transformation have no custom properties.

For more information, see Sort Transformation.

The Term Extraction transformation has both custom properties and the properties common to all data flow components.

The following table describes the custom properties of the Term Extraction transformation. All properties are read/write.

PropertyData ypeDescription
FrequencyThresholdIntegerA numeric value that indicates the number of times a term must occur before it is extracted. The default value of this property is 2.
IsCaseSensitiveBooleanA value that specifies whether case sensitivity is used when extracting nouns and noun phrases. The default value of this property is False.
MaxLengthOfTermIntegerA numeric value that indicates the maximum length of a term. This property applies only to phrases. The default value of this property is 12.
NeedRefenceDataBooleanA value that specifies whether the transformation uses a list of exclusion terms stored in a reference table. The default value of this property is False.
OutTermColumnStringThe name of the column that contains the exclusion terms.
OutTermTableStringThe name of the table that contains the column with exclusion terms.
ScoreTypeIntegerA 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.
WordOrPhraseIntegerA value that specifies term type. The valid values are 0 indicating words only, 1 indicating noun phrases only, and 2 indicating both words and noun phrases. The default value of this property is 0.

The input, the input columns, the output, and the output columns of the Term Extraction transformation have no custom properties.

For more information, see Term Extraction Transformation.

The Term Lookup transformation has both custom properties and the properties common to all data flow components.

The following table describes the custom properties of the Term Lookup transformation. All properties are read/write.

PropertyData typeDescription
IsCaseSensitiveBooleanA 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.
RefTermColumnStringThe name of the column that contains the lookup terms.
RefTermTableStringThe name of the table that contains the column with lookup terms.

The following table describes the custom properties of the input columns of the Term Lookup transformation. All properties are read/write.

PropertyData typeDescription
InputColumnTypeIntegerA 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.

The following table describes the custom properties of the output columns of the Term Lookup transformation. All properties are read/write.

Property nameData typeDescription
CustomLineageIDIntegerThe LineageID of the corresponding input column if the InputColumnType of that column is 0 or 2.

The input and output of the Term Lookup transformation have no custom properties.

For more information, see Term Lookup Transformation.

The Unpivot transformation has only the properties common to all data flow components at the component level.

System_CAPS_ICON_note.jpg Note


This section relies on the Unpivot scenario described in Unpivot Transformation to illustrate the use of the options described here.

The following table describes the custom properties of the input columns of the Unpivot transformation. All properties are read/write.

PropertyData typeDescription
DestinationColumnIntegerThe 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.
PivotKeyValueStringA value that is copied to a transformation output column.

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

In the Unpivot scenario described in Unpivot Transformation, the Pivot Values are the text values Ham, Coke, Milk, Beer, and Chips. These will appear as text values in the new Product column designated by the Pivot Key Value Column Name option.

The following table describes the custom properties of the output columns of the Unpivot transformation. All properties are read/write.

Property nameData typeDescription
PivotKeyBooleanIndicates 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.

The input and output of the Unpivot transformation have no custom properties.

For more information, see Unpivot Transformation.

Integration Services Transformations
Common Properties
Path Properties
Data Flow Properties that Can Be Set by Using Expressions

Show:
© 2016 Microsoft