TechNet
Export (0) Print
Expand All

Excel Custom Properties

 

Applies To: SQL Server 2016

Source Custom Properties

The Excel source has both custom properties and the properties common to all data flow components.

The following table describes the custom properties of the Excel source. All properties are read/write.

Property nameData TypeDescription
AccessModeIntegerThe mode used to access the database. The possible values are Open Rowset, Open Rowset from Variable, SQL Command, and SQL Command from Variable. The default value is Open Rowset.
CommandTimeoutIntegerThe number of seconds before a command times out. A value of 0 indicates an infinite time-out.

 Note This property is not available in the Excel Source Editor, but can be set by using the Advanced Editor.
OpenRowsetStringThe name of the database object that is used to open a rowset.
OpenRowsetVariableStringThe variable that contains the name of the database object that is used to open a rowset.
ParameterMappingStringThe mapping from parameters in the SQL command to variables.
SqlCommandStringThe SQL command to be executed.
SqlCommandVariableStringThe variable that contains the SQL command to be executed.

The output and the output columns of the Excel source have no custom properties.

For more information, see Excel Source.

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 nameData TypeDescription
AccessModeInteger (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.
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.

Note: This property is not available in the Excel Destination Editor, but can be set by using the Advanced Editor.
FastLoadKeepIdentityBooleanA 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.
FastLoadKeepNullsBooleanA 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.
FastLoadMaxInsertCommitSizeIntegerA value that specifies the batch size that the Excel destination tries to commit during fast load operations. The default value is 2147483647. A value of 0 indicates a single commit operation after all rows are processed.
FastLoadOptionsStringA 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.
OpenRowsetStringWhen AccessMode is OpenRowset, the name of the table or view that the Excel destination accesses.
OpenRowsetVariableStringWhen AccessMode is OpenRowset from Variable, the name of the variable that contains the name of the table or view that the Excel destination accesses.
SqlCommandStringWhen 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.

Common Properties

Community Additions

ADD
Show:
© 2016 Microsoft