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.

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	New content: Added MaxInsertCommitSize property of SQL Server destination.
5 December 2005	New content: Added AccessMode property of Raw File destination. Added AlwaysUseDefaultCodepage and DefaultCodepage properties of SQL Server destination. Changed content: Corrected list of values for WriteOption property of Raw File destination