Share via


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:

  • OpenRowset (0)—You provide the name of a table or view.

  • OpenRowset from Variable (1)—You provide the name of a variable that contains the name of a table or view.

  • OpenRowset Using Fastload (3)—You provide the name of a table or view.

  • OpenRowset Using Fastload from Variable (4)—You provide the name of a variable that contains the name of a table or view.

  • SQL Command (2)—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.

NoteNote
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.

NoteNote
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:

  • OpenRowset (0)—You provide the name of a table or view.

  • OpenRowset from Variable (1)—You provide the name of a variable that contains the name of a table or view.

  • OpenRowset Using Fastload (3)—You provide the name of a table or view.

  • OpenRowset Using Fastload from Variable (4)—You provide the name of a variable that contains the name of a table or view.

  • SQL Command (2)—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.

NoteNote
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.

NoteNote
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.