Destination Custom Properties
The data flow objects in the Microsoft SQL Server Integration Services object models have common properties and custom properties at the level of the component, inputs and outputs, and input columns and output columns. The 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 data flow destinations.
ADO NET Destination
Data Mining Model Training Destination
DataReader Destination
Dimension Processing Destination
Excel Destination
Flat File Destination
OLE DB Destination
Partition Processing Destination
Raw File Destination
Recordset Destination
SQL Server Compact Edition Destination
SQL Server Destination
For information about the properties common to most data flow objects, see Common Properties.
Some properties of destinations can be set by using property expressions, as indicated in this topic. For more information, see Data Flow Properties that Can Be Set by Using Expressions.
ADO NET Destination
The ADO.NET destination has both custom properties and the properties common to all data flow components.
The following table describes the custom properties of the ADO.NET destination. All properties are read/write. These properties are not available in the ADO NET Destination Editor, but can be set by using the Advanced Editor.
Property |
Data Type |
Description |
|---|---|---|
BatchSize |
Integer |
The number of rows in a batch that is sent to the server. A value of 0 indicates that the batch size matches the internal buffer size. The default value of this property is 0. |
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. |
TableOrViewName |
String |
The name of the destination table or view. |
UseBulkInsertWhenAvailable |
Boolean |
A value that specifies whether to use the System.Data.SqlClient.SqlBulkCopy interface to improve the performance of bulk insert operations. Only certain ADO.NET providers support this interface. The default value is True. |
For more information, see ADO NET Destination.
Data Mining Model Training Destination Custom Properties
The Data Mining Model Training destination has both custom properties and the properties common to all data flow components.
The following table describes the custom properties of the Data Mining Model Training destination. All properties are read/write.
Property |
Data Type |
Description |
|---|---|---|
ASConnectionId |
String |
The unique identifier of the connection manager. |
ASConnectionString |
String |
The connection string to an instance of Analysis Services or to an Analysis Services project. |
ObjectRef |
String |
An XML tag that identifies the data mining structure that the transformation uses. |
The input and the input columns of the Data Mining Model Training destination have no custom properties.
For more information, see Data Mining Model Training Destination.
DataReader Destination Custom Properties
The DataReader destination has both custom properties and the properties common to all data flow components.
The following table describes the custom properties of the DataReader destination. All properties except for DataReader are read/write.
Property name |
Data Type |
Description |
|---|---|---|
DataReader |
String |
The class name of the DataReader destination. |
FailOnTimeout |
Boolean |
Indicates whether to fail when a ReadTimeout occurs. The default value of this property is False. |
ReadTimeout |
Integer |
The number of milliseconds before a time-out occurs. The default value of this property is 30000 (30 seconds). |
The input and the input columns of the DataReader destination have no custom properties.
For more information, see DataReader Destination.
Dimension Processing Destination Custom Properties
The Dimension Processing destination has both custom properties and the properties common to all data flow components.
The following table describes the custom properties of the Dimension Processing destination. All properties are read/write.
Property |
Data Type |
Description |
|---|---|---|
ASConnectionString |
String |
The connection string to an instance of Analysis Services or to an Analysis Services project. |
KeyDuplicate |
Integer (enumeration) |
When UseDefaultConfiguration is False, a value that indicates how to handle duplicate key errors. The possible values are IgnoreError (0), ReportAndContinue (1), and ReportAndStop (2). The default value of this property is IgnoreError (0). |
KeyErrorAction |
Integer (enumeration) |
When UseDefaultConfiguration is False, a value that indicates how to handle key error. The possible values are ConvertToUnknown (0) and DiscardRecord (1). The default value of this property is ConvertToUnknown (0). |
KeyErrorLimit |
Integer |
When UseDefaultConfiguration is False, the upper limit of key errors that are enabled. |
KeyErrorLimitAction |
Integer (enumeration) |
When UseDefaultConfiguration is False, a value that indicates the action to take when KeyErrorLimit is reached. The possible values are StopLogging (1) and StopProcessing (0). The default value of this property is StopProcessing (0). |
KeyErrorLogFile |
String |
When UseDefaultConfiguration is False, the path and file name of the error log file. |
KeyNotFound |
Integer (enumeration) |
When UseDefaultConfiguration is False, a value that indicates how to handle missing key errors. The possible values are IgnoreError (0), ReportAndContinue (1), and ReportAndStop (2). The default value of this property is IgnoreError (0). |
NullKeyConvertedToUnknown |
Integer (enumeration) |
When UseDefaultConfiguration is False, a value that indicates how to handle null keys converted to the unknown value. The possible values are IgnoreError (0), ReportAndContinue (1), and ReportAndStop (2). The default value of this property is IgnoreError (0). |
NullKeyNotAllowed |
Integer (enumeration) |
When UseDefaultConfiguration is False, a value that indicates how to handle disallowed nulls. The possible values are IgnoreError (0), ReportAndContinue (1), and ReportAndStop (2). The default value of this property is IgnoreError (0). |
ProcessType |
Integer (enumeration) |
The type of dimension processing the transformation uses. The values are ProcessAdd (1) (incremental), ProcessFull (0), and ProcessUpdate (2). |
UseDefaultConfiguration |
Boolean |
A value that specifies whether the transformation uses the default error configuration. If this property is False, the transformation includes information about error processing. |
The input and the input columns of the Dimension Processing destination have no custom properties.
For more information, see Dimension Processing Destination.
Excel Destination Custom Properties
The Excel destination has both custom properties and the properties common to all data flow components.
The following table describes the custom properties of the Excel destination. All properties are read/write.
Property name |
Data Type |
Description |
|---|---|---|
AccessMode |
Integer (enumeration) |
A value that specifies how the destination accesses its destination database. This property can have one of the following values:
|
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. .gif "Note") Note
This property is not available in the Excel Destination Editor, but can be set by using the Advanced Editor.
|
FastLoadKeepIdentity |
Boolean |
A value that specifies whether to copy identity values when data is loaded. This property is available only when using one of the fast load options. The default value of this property is False. |
FastLoadKeepNulls |
Boolean |
A value that specifies whether to copy Null values when data is loaded. This property is available only with one of the fast load options. The default value of this property is False. |
FastLoadMaxInsertCommitSize |
Integer |
A value that specifies the batch size that the Excel destination tries to commit during fast load operations. The default value, 0, indicates a single commit operation after all rows are processed. |
FastLoadOptions |
String |
A collection of fast load options. The fast load options include the locking of tables and the checking of constraints. You can specify one, both, or neither. Note
Some options for this property are not available in the Excel Destination Editor, but can be set by using the Advanced Editor.
|
OpenRowset |
String |
When AccessMode is OpenRowset, the name of the table or view that the Excel destination accesses. |
OpenRowsetVariable |
String |
When AccessMode is OpenRowset from Variable, the name of the variable that contains the name of the table or view that the Excel destination accesses. |
SqlCommand |
String |
When AccessMode is SQL Command, the Transact-SQL statement that the Excel destination uses to specify the destination columns for the data. |
The input and the input columns of the Excel destination have no custom properties.
For more information, see Excel Destination.
Flat File Destination Custom Properties
The Flat File destination has both custom properties and the properties common to all data flow components.
The following table describes the custom properties of the Flat File destination. All properties are read/write.
Property name |
Data Type |
Description |
|---|---|---|
Header |
String |
A block of text that is inserted in the file before any data is written. The value of this property can be specified by using a property expression. |
Overwrite |
Boolean |
A value that specifies whether to overwrite or append to an existing destination file that has the same name. The default value of this property is True. |
The input and the input columns of the Flat File destination have no custom properties.
For more information, see Flat File Destination.
OLE DB Destination Custom Properties
The OLE DB destination has both custom properties and the properties common to all data flow components.
The following table describes the custom properties of the OLE DB destination. All properties are read/write.
Note
The FastLoad options listed here (FastLoadKeepIdentity, FastLoadKeepNulls, and FastLoadOptions) correspond to the similarly named properties exposed by the IRowsetFastLoad interface implemented by the Microsoft OLE DB Provider for SQL Server (SQLOLEDB). For more information, search for IRowsetFastLoad in the MSDN Library.
Property name |
Data Type |
Description |
|---|---|---|
AccessMode |
Integer (enumeration) |
A value that specifies how the destination access its destination database. This property can have one of the following values:
|
AlwaysUseDefaultCodePage |
Boolean |
A value that indicates whether to use the value of the DefaultCodePage property for each column, or to try to derive the codepage from each column's locale. The default value of this property is False. |
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. Note
This property is not available in the OLE DB Destination Editor, but can be set by using the Advanced Editor.
|
DefaultCodePage |
Integer |
The default codepage associated with the OLE DB destination. |
FastLoadKeepIdentity |
Boolean |
A value that specifies whether to copy identity values when data is loaded. This property is available only with one of the fast load options. The default value of this property is False. This property corresponds to the OLE DB IRowsetFastLoad (OLE DB) property SSPROP_FASTLOADKEEPIDENTITY. |
FastLoadKeepNulls |
Boolean |
A value that specifies whether to copy Null values when data is loaded. This property is available only with one of the fast load options. The default value of this property is False. This property corresponds to the OLE DB IRowsetFastLoad (OLE DB) property SSPROP_FASTLOADKEEPNULLS. |
FastLoadMaxInsertCommitSize |
Integer |
A value that specifies the batch size that the OLE DB destination tries to commit during fast load operations. The default value, 2147483647, indicates a single commit operation after all rows are processed. |
FastLoadOptions |
String |
A collection of fast load options. The fast load options include the locking of tables and the checking of constraints. You can specify one, both, or neither. This property corresponds to the OLE DB IRowsetFastLoad property SSPROP_FASTLOADOPTIONS and accepts string options such as CHECK_CONSTRAINTS and TABLOCK. Note
Some options for this property are not available in the Excel Destination Editor, but can be set by using the Advanced Editor.
|
OpenRowset |
String |
When AccessMode is OpenRowset, the name of the table or view that the OLE DB destination accesses. |
OpenRowsetVariable |
String |
When AccessMode is OpenRowset from Variable, the name of the variable that contains the name of the table or view that the OLE DB destination accesses. |
SqlCommand |
String |
When AccessMode is SQL Command, the Transact-SQL statement that the OLE DB destination uses to specify the destination columns for the data. |
The input and the input columns of the OLE DB destination have no custom properties.
For more information, see OLE DB Destination.
Partition Processing Destination Custom Properties
The Partition Processing destination has both custom properties and the properties common to all data flow components.
The following table describes the custom properties of the Partition Processing destination. All properties are read/write.
Property |
Data Type |
Description |
|---|---|---|
ASConnectionString |
String |
The connection string to an Analysis Services project or an instance of Analysis Services. |
KeyDuplicate |
Integer (enumeration) |
When UseDefaultConfiguration is False, a value that indicates how to handle duplicate key errors. The possible values are IgnoreError (0), ReportAndContinue (1), and ReportAndStop (2). The default value of this property is IgnoreError (0). |
KeyErrorAction |
Integer (enumeration) |
When UseDefaultConfiguration is False, a value that indicates how to handle key errors. The possible values are ConvertToUnknown (0) and DiscardRecord (1). The default value of this property is ConvertToUnknown (0). |
KeyErrorLimit |
Integer |
When UseDefaultConfiguration is False, the upper limit of key errors that are allowed. |
KeyErrorLimitAction |
Integer (enumeration) |
When UseDefaultConfiguration is False, a value that indicates the action to take when KeyErrorLimit is reached. The possible values are StopLogging (1) and StopProcessing (0). The default value of this property is StopProcessing (0). |
KeyErrorLogFile |
String |
When UseDefaultConfiguration is False, the path and file name of the error log file. |
KeyNotFound |
Integer (enumeration) |
When UseDefaultConfiguration is False, a value that indicates how to handle missing key errors. The possible values are IgnoreError (0), ReportAndContinue (1), and ReportAndStop (2). The default value of this property is ReportAndContinue (1). |
NullKeyConvertedToUnknown |
Integer (enumeration) |
When UseDefaultConfiguration is False, a value that indicates how to handle null keys converted to the Unknown value. The possible values are IgnoreError (0), ReportAndContinue (1), and ReportAndStop (2). The default value of this property is IgnoreError (0). |
NullKeyNotAllowed |
Integer (enumeration) |
When UseDefaultConfiguration is False, a value that indicates how to handle disallowed nulls. The possible values are IgnoreError (0), ReportAndContinue (1), and ReportAndStop (2). The default value of this property is ReportAndContinue (1). |
ProcessType |
Integer (enumeration) |
The type of partition processing the transformation uses. The possible values are ProcessAdd (1) (incremental), ProcessFull (0), and ProcessUpdate (2). |
UseDefaultConfiguration |
Boolean |
A value that specifies whether the transformation uses the default error configuration. If this property is False, the transformation uses the values of the error-handling custom properties listed in this table, including KeyDuplicate, KeyErrorAction, and so on. |
The input and the input columns of the Partition Processing destination have no custom properties.
For more information, see Partition Processing Destination.
Raw File Destination Custom Properties
The Raw File destination has both custom properties and the properties common to all data flow components.
The following table describes the custom properties of the Raw File destination. All properties are read/write.
Property name |
Data Type |
Description |
|---|---|---|
AccessMode |
Integer (enumeration) |
A value that specifies whether the FileName property contains a file name, or the name of a variable that contains a file name. The options are File name (0) and File name from variable (1). |
FileName |
String |
The name of the file to which the Raw File destination writes. |
WriteOption |
Integer (enumeration) |
A value that specifies whether the Raw File destination deletes an existing file that has the same name. The options are Create Always (0), Create Once (1), Truncate and Append (3), and Append (2). The default value of this property is Create Always (0). |
Note
An append operation requires the metadata of the appended data to match the metadata of the data already present in the file.
The input and the input columns of the Raw File destination have no custom properties.
For more information, see Raw File Destination.
Recordset Destination Custom Properties
The Recordset destination has both custom properties and the properties common to all data flow components.
The following table describes the custom properties of the Recordset destination. All properties are read/write.
Property name |
Data Type |
Description |
|---|---|---|
VariableName |
String |
The name of the variable that holds the ADO recordset. |
The input and the input columns of the Recordset destination have no custom properties.
For more information, see Recordset Destination.
SQL Server Compact Edition Destination Custom Properties
The SQL Server Compact destination has both custom properties and the properties common to all data flow components.
The following table describes the custom properties of the SQL Server Compact destination. All properties are read/write.
Property name |
Data Type |
Description |
|---|---|---|
TableName |
String |
The name of the destination table in a SQL Server Compact database. The value of this property can be specified by using a property expression. |
The input and the input columns of the SQL Server Compact destination have no custom properties.
For more information, see SQL Server Compact Edition Destination.
SQL Server Destination Custom Properties
The SQL Server destination has both custom properties and the properties common to all data flow components.
The following table describes the custom properties of the SQL Server destination. All properties are read/write.
Property name |
Data Type |
Description |
|---|---|---|
AlwaysUseDefaultCodePage |
Boolean |
Forces the use of the DefaultCodePage property value. The default value of this property is False. |
BulkInsertCheckConstraints |
Boolean |
A value that specifies whether the bulk insert checks constraints. The default value of this property is True. |
BulkInsertFireTriggers |
Boolean |
A value that specifies whether the bulk insert fires triggers on tables. The default value of this property is False. |
BulkInsertFirstRow |
Integer |
A value that specifies the first row to insert. The default value of this property is -1, which indicates that no value has been assigned |
BulkInsertKeepIdentity |
Boolean |
A value that specifies whether values can be inserted into identity columns. The default value of this property is False. |
BulkInsertKeepNulls |
Boolean |
A value that specifies whether the bulk insert keeps Null values. The default value of this property is False. |
BulkInsertLastRow |
Integer |
A value that specifies the last row to insert. The default value of this property is -1, which indicates that no value has been assigned. |
BulkInsertMaxErrors |
Integer |
A value that specifies the number of errors that can occur before the bulk insert stops. The default value of this property is –1, which indicates that no value has been assigned. |
BulkInsertOrder |
String |
The names of the sort columns. Each column can be sorted in ascending or descending order. If multiple sort columns are used, the column names are separated by commas. |
BulkInsertTableName |
String |
The SQL Server table or view in the database to which the data is copied. |
BulkInsertTablock |
Boolean |
A value that specifies whether the table is locked during the bulk insert. The default value of this property is True. |
DefaultCodePage |
Integer |
The code page to use when code page information is not available from the data source. |
MaxInsertCommitSize |
Integer |
A value that specifies the maximum number of rows to insert in a batch. When the value is zero, all rows are inserted in a single batch. |
Timeout |
Integer |
A value that specifies the number of seconds the SQL Server destination waits before termination if there is no data available for insertion. A value of 0 means that the SQL Server destination will not time out. The default value of this property is 30. |
The input and the input columns of the SQL Server destination have no custom properties.
For more information, see SQL Server Destination.