Destination Custom Properties
The data flow objects in the Microsoft SQL Server 2005 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.
- Data Mining Model Training Destination
- DataReader Destination
- Dimension Processing 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 Using Property Expressions to Specify Property Values for Data Flow Objects.
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, ReportAndContinue, and ReportAndStop. The default value of this property is IgnoreError. |
KeyErrorAction |
Integer (enumeration) |
When UseDefaultConfiguration is False, a value that indicates how to handle key error. The possible values are ConvertToUnknown and DiscardRecord. The default value of this property is ConvertToUnknown. |
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 and StopProcessing. The default value of this property is StopProcessing. |
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, ReportAndContinue, and ReportAndStop. The default value of this property is IgnoreError. |
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, ReportAndContinue, and ReportAndStop. The default value of this property is IgnoreError. |
NullKeyNotAllowed |
Integer (enumeration) |
When UseDefaultConfiguration is False, a value that indicates how to handle disallowed nulls. The possible values are IgnoreError, ReportAndContinue, and ReportAndStop. The default value of this property is IgnoreError. |
ProcessType |
Integer (enumeration) |
The type of dimension processing the transformation uses. The values are ProcessAdd (incremental), ProcessFull, and ProcessUpdate. |
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. The options are OpenRowset, OpenRowset from Variable, OpenRowset Using Fastload, OpenRowset Using Fastload from Variable, and SQL Command. For OpenRowset and OpenRowset Using Fastload, you provide the name of a table or view. For OpenRowset from Variable and OpenRowset Using Fastload from Variable, you provide the name of a variable that contains the name of a table or view. For SQL Command, you provide a SQL statement.
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 "ms135971.note(en-US,SQL.90).gif")
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. The options are OpenRowset, OpenRowset from Variable, OpenRowset Using Fastload, OpenRowset Using Fastload from Variable, and SQL Command. For OpenRowset and OpenRowset Using Fastload, you provide the name of a table or view. For OpenRowset from Variable and OpenRowset Using Fastload from Variable, you provide the name of a variable that contains the name of a table or view. For SQL Command, you provide a SQL statement.
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, 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. 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, ReportAndContinue, and ReportAndStop. The default value of this property is IgnoreError. |
KeyErrorAction |
Integer (enumeration) |
When UseDefaultConfiguration is False, a value that indicates how to handle key errors. The possible values are ConvertToUnknown and DiscardRecord. The default value of this property is ConvertToUnknown. |
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 and StopProcessing. The default value of this property is StopProcessing. |
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, ReportAndContinue, and ReportAndStop. The default value of this property is ReportAndContinue. |
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, ReportAndContinue, and ReportAndStop. The default value of this property is IgnoreError. |
NullKeyNotAllowed |
Integer (enumeration) |
When UseDefaultConfiguration is False, a value that indicates how to handle disallowed nulls. The possible values are IgnoreError, ReportAndContinue, and ReportAndStop. The default value of this property is ReportAndContinue. |
ProcessType |
Integer (enumeration) |
The type of partition processing the transformation uses. The possible values are ProcessAdd (incremental), ProcessFull, and ProcessUpdate. |
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 and File name from variable. |
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, Create Once, Truncate and Append, and Append. The default value of this property is Create Always. |
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 2005 Compact Edition 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 2005 Compact Edition destination. All properties are read/write.
| Property name | Data Type | Description |
|---|---|---|
TableName |
String |
The name of the destination table in a SQL Server 2005 Compact Edition database. The value of this property can be specified by using a property expression. |
The input and the input columns of the SQL Server 2005 Compact Edition 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.
See Also
Reference
Common Properties
Source Custom Properties
Transformation Custom Properties
Path Properties
Concepts
Using Property Expressions to Specify Property Values for Data Flow Objects
Other Resources
Integration Services Destinations
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
14 April 2006 |
|
5 December 2005 |
|