You can use this logging capability to troubleshoot the saving of data to external data sources that the OLE DB destination performs. You can log the calls that the OLE DB destination makes to external data providers. To learn more about fast load options, see BULK INSERT (Transact-SQL). The Transact-SQL keywords are traditionally typed using uppercase letters, but the keywords are not case sensitive. Note: Performance can be improved if you use the ORDER option to sort the input data according to the clustered index on the table. If sort order is omitted, the insert operation assumes the data is unsorted. Any number of columns may be listed and it is optional to include the sort order. The presence of the option indicates that triggers fire. Specifies whether triggers fire on the insert table. The option has the form KILOBYTES_PER_BATCH =. Specifies the size in kilobytes to insert. In addition to the fast load options exposed in the OLE DB Destination Editor dialog box,you can configure the OLE DB destination to use the following bulk load options by typing the options in FastLoadOptions property in the Advanced Editor dialog box. The value 1000 indicates that the destination is configured to use batches of 1000 rows.Īny constraint failure at the destination causes the entire batch of rows defined by FastLoadMaxInsertCommitSize to fail. If the OLE DB destination uses all the fast load options that are stored in FastLoadOptions and listed in the OLE DB Destination Editor dialog box, the value of the property is set to TABLOCK, CHECK_CONSTRAINTS, ROWS_PER_BATCH=1000. Other fast load options are stored in a comma-separated list in the FastLoadOptions property. For example, FastLoadKeepIdentity specifies whether to keep identify values, FastLoadKeepNulls specifies whether to keep null values, and FastLoadMaxInsertCommitSize specifies the number of rows to commit as a batch. Some fast load options are stored in specific properties of the OLE DB destination. Specify the number of rows in the batch and the commit size. Retain a null value during the bulk load operation.Ĭheck constraints on the target table or view during the bulk import operation.Īcquire a table-level lock for the duration of the bulk load operation. Keep identity values from the imported data file or use unique values assigned by SQL Server. If the OLE DB destination uses a fast-load data access mode, you can specify the following fast load options in the user interface, OLE DB Destination Editor, for the destination: The OLE DB destination has one regular input and one error output.įor more information about data types, see Integration Services Data Types. For example, you cannot map an input column with a string data type to a destination column with a numeric data type. In addition, the data types of mapped columns must be compatible. For example, if a destination column does not allow null values, an input column must be mapped to that column. You do not have to map input columns to all destination columns, but depending on the properties of the destination columns, errors can occur if no input columns are mapped to the destination columns. For more information, see OLE DB Connection Manager.Īn Integration Services project also provides the data source object from which you can create an OLE DB connection manager, to make data sources and data source views available to the OLE DB destination.Īn OLE DB destination includes mappings between input columns and columns in the destination data source. This destination uses an OLE DB connection manager to connect to a data source and the connection manager specifies the OLE DB provider to use. For example, some destinations do not support the data types that the CREATE TABLE statement uses. The CREATE TABLE statement that the OLE DB Destination Editor dialog box generates may require modification depending on the destination type.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |