OLE DB Destination Usage

The first part: Introduction

OLE DB Destination component is the data flow load to Destination, a total of 5 kinds data Access Mode, the general Destination component internal implementation of the principle is to use the insert Statement to insert data into the target table.

If OLE DB Destination component selected Data Access Mode uses the Fast load option, the SQL command used by the component changes and the insert becomes a bulk INSERT.

If data access mode does not use the fast load option, Destination component uses the INSERT clause to insert the information into the database, and if data access mode uses the fast load option, the Des Tination component uses the BULK INSERT clause to insert data into the target table, fast load option significantly increases the performance of input insertions.

1, if destination table does not exist, you can click the New button, component automatically generate a TSQL script for CREATE TABLE, generate destination table.

CREATE TABLE [OLE DB Destination] (    [code] varchar (TEN),    [name] varchar (10))

The use of New on MSDN is described below

Create a new table by using the Create Table dialog box.

When you click New, integration Services generates a default CREATE TABLE statement based on the connected data s Ource. This default CREATE table statement would not include the FILESTREAM attribute even if the source table includes a column W ith the FILESTREAM attribute declared. To run a integration Services component with the FILESTREAM attribute, first implement FILESTREAM storage on the Destinat Ion database. Then, add the FILESTREAM attribute to the CREATE TABLE statement in theCreate Table dialog box.

2,fast Load option

When you use the fast Load option, the Destination component uses BULK INSERT to BULK INSERT data.

Keep Identity: When an identity column exists in the target table, the value of the identity column in the target table is automatically generated if the Keep identity is not selected, and if Keep identity is selected, Then the ID value in the target table remains the same as the data source.

Keep Nulls: If the target table sets a DEFAULT constraint for a column, the default constraint for the target table is normally triggered when the data flow passes NULL to the column. If keep Nulls is not selected, the target table populates the data column with default value, and if Keep Nulls is selected, the target table retains that column as null.

Table Lock: If selected, adds a table-level lock for the entire destination table before inserting the data.

Check Constraints: Checks whether data inserted into destination table conforms to the constraint of destination table, and if it can be determined that the data conforms to the constraint of target table, Then unchecked check Constraints will significantly improve performance.

When inserting large amounts of data, proper control over the size of tempdb and transaction log can improve the performance of data insertion.

Rows per batch: Configure the Bulk INSERT data rows for each batch.

Maximum Insert Commit Size: Commits a transaction when a specified number of data rows are inserted.

Any constraint failure at the destination causes the entire batch of rows defined by fastloadmaxinsertcommitsize To fail.

3,sql Command Mode

When you select SQL Command from the Data Access Mode list, the component requires an SQL SELECT query clause instead of an INSERT statement.

The component obtains the metadata of the data column of the target table from the SELECT clause, sets column mapping, and the component inserts the data into the target according to column mapping.

4,sql Command mode does not support parameter

The OLE DB destination does not a support parameters. If you need to execute a parameterized INSERT statement, consider the OLE DB Command transformation.

Part II:MSDN 5 Types of Data Access mode for OLE DB Destination are described below

Data access mode,specify The method for loading data into the destination.

Table name or view name variable

SQL command

td> td>

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 is optimized for bulk inserts
Specify the table or view name in a variable.
Table name or view name Variable-fast load	Specify the table or view name in a VA Riable, and use the fast load option to load the data. For more information about the fast load data access modes, which is optimized for bulk inserts
Load data into the OLE DB destination by using a SQL query.

Data Access Mode Dynamic OptionsEach of the settings for Data access ModeDisplays a dynamic set of options specific to that setting. The following sections describe each of the dynamic options available for each Data access ModeSetting.

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.

Keep identity

Specify whether to copy the identity values when the 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 if 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 the is true.

Check constraints

Specify whether the destination checks constraints when it loads data. The default value of the is true.

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.

Maximum Insert Commit Size

Specify the batch size, the OLE DB destination tries to commit during fast load operations. The value of 0 indicates, the all data are committed in a, a single batch after all rows has been processed.

A value of 0 might cause the running package to stop responding if the OLE DB destination and another data flow C Omponent is updating the same source table. To prevent the package from stopping, set the Maximum Insert commits the sizeoption to 2147483647.

If you provide a value for this property, the destination commits rows in batches that is the smaller of (a) the Maxi Mum Insert commit size, or (b) the remaining rows in the buffer, which is currently being processed.

Any constraint failure at the destination causes the entire batch of rows defined by Maximum Insert commit size t o 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.

Keep identity

Specify whether to copy the identity values when the 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 if 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.

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

Maximum Insert Commit Size

Specify the batch size, the OLE DB destination tries to commit during fast load operations. The default value of 2147483647 indicates, all data are committed in a single batch after all rows has been p Rocessed.

A value of 0 might cause the running package to stop responding if the OLE DB destination and another data flow C Omponent is updating the same source table. To prevent the package from stopping, set the Maximum Insert commits the sizeoption to 2147483647.

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, contains the Quer Y text by clicking Browse.

The OLE DB destination does not a support parameters. If you need to execute a parameterized INSERT statement, consider the 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.

OLE DB Destination Usage