Share via

OLE DB Destination Editor (Connection Manager Page)

  • Article

Use the Connection Manager page of the OLE DB Destination Editor dialog box to select the OLE DB connection for the destination. This page also lets you select a table or view from the database.

Note

To load data to a data source that uses Microsoft Office Excel 2007, use an OLE DB destination. You cannot use an Excel destination to load data to an Excel 2007 data source. For more information, see Configure OLE DB Connection Manager. To load data to a data source that uses Microsoft Office Excel 2003 or earlier, use an Excel destination. For more information, see Excel Destination Editor (Connection Manager Page).

Note

The CommandTimeout property of the OLE DB destination is not available in the OLE DB Destination Editor, but can be set by using the Advanced Editor. In addition, certain fast load options are available only in the Advanced Editor. For more information on these properties, see the OLE DB Destination section of Destination Custom Properties.

To learn more about the OLE DB destination, see OLE DB Destination.

Static Options

  • OLE DB connection manager
    Select an existing connection manager from the list, or create a new connection by clicking New.
  • New
    Create a new connection manager by using the Configure OLE DB Connection Manager dialog box.

  • Data access mode
    Specify the method for loading data into the destination. Loading double-byte character set (DBCS) data requires use of one of the fast load options. For more information about the fast load data access modes, which are optimized for bulk inserts, see OLE DB Destination.

    Option Description

    Table or view

    Load data into a table or view in the OLE DB destination.

    Table or view - fast load

    Load data into a table or view in the OLE DB destination and use the fast load option. For more information about the fast load data access modes, which are optimized for bulk inserts, see OLE DB Destination.

    Table name or view name variable

    Specify the table or view name in a variable.

    Related information: Using Variables in Packages

    Table name or view name variable - fast load

    Specify the table or view name in a variable, and use the fast load option to load the data. For more information about the fast load data access modes, which are optimized for bulk inserts, see OLE DB Destination.

    SQL command

    Load data into the OLE DB destination by using a SQL query.
  • Preview
    Preview results by using the Preview Query Results dialog box. Preview can display up to 200 rows.

Data Access Mode Dynamic Options

Data access mode = Table or view

  • Name of the table or the view
    Select the name of the table or view from a list of those available in the data source.

Data access mode = Table or view – fast load

  • Name of the table or view
    Select a table or view from the database by using this list, or create a new table by clicking New.
  • New
    Create a new table by using the Create Table dialog box.
  • Keep identity
    Specify whether to copy identity values when data is loaded. This property is available only with the fast load option. The default value of this property is false.
  • Keep nulls
    Specify whether to copy null values when data is loaded. This property is available only with the fast load option. The default value of this property is false.
  • Table lock
    Specify whether the table is locked during the load. The default value of this property is false.
  • Check constraints
    Specify whether the destination checks constraints when it loads data. The default value of this property is false.

  • Rows per batch
    Specify the number of rows in a batch. The default value of this property is –1, which indicates that no value has been assigned.

    Note

    Clear the text box in the OLE DB Destination Editor to indicate that you do not want to assign a custom value for this property.

  • Maximum insert commit size
    Specify the batch size that the OLE DB destination tries to commit during fast load operations. The default value of 0 indicates that all data is committed in a single batch after all rows have been processed. If you provide a value for this property, the destination commits rows in batches that are the smaller of (a) the Maximum insert commit size, or (b) the remaining rows in the buffer that is currently being processed.

    Note

    Any constraint failure at the destination causes the entire batch of rows defined by Maximum insert commit size to fail.

Data access mode = Table name or view name variable

  • Variable name
    Select the variable that contains the name of the table or view.

Data Access Mode = Table name or view name variable – fast load)

  • Variable name
    Select the variable that contains the name of the table or view.
  • New
    Create a new table by using the Create Table dialog box.
  • Keep identity
    Specify whether to copy identity values when data is loaded. This property is available only with the fast load option. The default value of this property is false.
  • Keep nulls
    Specify whether to copy null values when data is loaded. This property is available only with the fast load option. The default value of this property is false.
  • Table lock
    Specify whether the table is locked during the load. The default value of this property is false.
  • Check constraints
    Specify whether the task checks constraints. The default value of this property is false.

  • Rows per batch
    Specify the number of rows in a batch. The default value of this property is –1, which indicates that no value has been assigned.

    Note

    Clear the text box in the OLE DB Destination Editor to indicate that you do not want to assign a custom value for this property.

  • Maximum insert commit size
    Specify the batch size that the OLE DB destination tries to commit during fast load operations. The default value of 0 indicates that all data is committed in a single batch after all rows have been processed.

Data access mode = SQL command

  • SQL command text
    Enter the text of a SQL query, build the query by clicking Build Query, or locate the file that contains the query text by clicking Browse.

    Note

    The OLE DB destination does not support parameters. If you have to execute a parameterized INSERT statement, use the OLE DB Command transformation. For more information, see OLE DB Command Transformation.

  • Build query
    Use the Query Builder dialog box to construct the SQL query visually.
  • Browse
    Use the Open dialog box to locate the file that contains the text of the SQL query.
  • Parse query
    Verify the syntax of the query text.

See Also

Reference

OLE DB Destination Editor (Mappings Page)
OLE DB Destination Editor (Error Output Page)

Other Resources

Integration Services Error and Message Reference
How to: Load Data Using the OLE DB Destination

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
New content:
  • Added a note about the use of Excel 2007 data sources.

17 July 2006
Changed content:
  • Provided link to detailed information about fast load options.
  • Described how to delete a custom value for the Rows per batch setting.

14 April 2006
Changed content:
  • Deleted description of Parameters option.

5 December 2005
Changed content:
  • Added a note about use of the Advanced Editor.