Share via


Source 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, input and output, and input column and output column. The custom properties can be accessed 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 sources.

  • ADO NET Source

  • Excel Source

  • Flat File Source

  • OLE DB Source

  • Raw File Source

  • XML Source

For information about the properties common to most data flow objects, see Common Properties.

Some properties of sources 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 Source Custom Properties

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

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

Property name

Data Type

Description

CommandTimeout

String

A value that specifies the number of seconds before the SQL command times out. A value of 0 indicates that the command never times out.

SqlCommand

String

The SQL statement that the ADO NET source uses to extract data.

When the package loads, you can dynamically update this property with the SQL statement that the ADO NET source will use. For more information, see Integration Services Expression Reference and Using Property Expressions in Packages.

AllowImplicitStringConversion

Boolean

A value that indicates whether the following occurs:

  • No generation of a validation error if there is a mismatch between external metadata types and output column types that are strings (DT_WSTR or DT_NTEXT).

  • Implicit conversion of external metadata types to the string data type that the output column uses.

The default value is TRUE.

For more information, see ADO NET Source.

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

For more information, see ADO NET Source.

Excel 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 name

Data Type

Description

AccessMode

Integer

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

CommandTimeout

Integer

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

OpenRowset

String

The name of the database object that is used to open a rowset.

OpenRowsetVariable

String

The variable that contains the name of the database object that is used to open a rowset.

ParameterMapping

String

The mapping from parameters in the SQL command to variables.

SqlCommand

String

The SQL command to be executed.

SqlCommandVariable

String

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

Flat File Source Custom Properties

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

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

Property name

Data Type

Description

FileNameColumnName

String

The name of an output column that contains the file name. If no name is specified, no output column containing the file name will be generated.

NoteNote
This property is not available in the Flat File Source Editor, but can be set by using the Advanced Editor.

RetainNulls

Boolean

A value that specifies whether to retain Null values from the source file as Null values when the data is processed by the Data Transformation Pipeline engine. The default value of this property is False.

The output of the Flat File source has no custom properties.

The following table describes the custom properties of the output columns of the Flat File source. All properties are read/write.

Property name

Data Type

Description

FastParse

Boolean

A value that indicates whether the column uses the quicker, but locale-insensitive, fast parsing routines that DTS provides or the locale-sensitive standard parsing routines. For more information, see Fast Parse and Standard Parse. The default value of this property is False.

NoteNote
This property is not available in the Flat File Source Editor, but can be set by using the Advanced Editor.

For more information, see Flat File Source.

OLE DB Source Custom Properties

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

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

Property name

Data Type

Description

AccessMode

Integer

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

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 number of seconds before a command times out. A value of 0 indicates an infinite time-out.

NoteNote
This property is not available in the OLE DB Source Editor, but can be set by using the Advanced Editor.

DefaultCodePage

Integer

The code page to use when code page information is unavailable from the data source.

OpenRowset

String

The name of the database object that is used to open a rowset.

OpenRowsetVariable

String

The variable that contains the name of the database object that is used to open a rowset.

ParameterMapping

String

The mapping from parameters in the SQL command to variables.

SqlCommand

String

The SQL command to be executed.

SqlCommandVariable

String

The variable that contains the SQL command to be executed.

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

For more information, see OLE DB Source.

Raw File Source Custom Properties

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

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

Property name

Data Type

Description

AccessMode

Integer (enumeration)

The mode used to access the raw data. The possible values are File name (0) and File name from variable (1). The default value is File name (0).

FileName

String

The path and file name of the source file.

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

For more information, see Raw File Source.

XML Source Custom Properties

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

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

Property name

Data Type

Description

AccessMode

Integer

The mode used to access the XML data.

UseInlineSchema

Boolean

A value that indicates whether to use an inline schema definition within the XML source. The default value of this property is False.

XMLData

String

The file or variables from which to retrieve the XML data.

The value of this property can be specified by using a property expression.

XMLSchemaDefinition

String

The path and file name of the schema definition file (.xsd).

The value of this property can be specified by using a property expression.

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

Property name

Data Type

Description

RowsetID

String

A value that identifies the rowset associated with the output.

The output columns of the XML source have no custom properties.

For more information, see XML Source.

Change History

Updated content

  • Added numeric values for the AccessMode property of the Raw File source.