OLE DB

The OLE DB connector is an object that is used to export data to a database using OLE DB drivers.

After adding the connector to the ETL task working area, set basic properties and export options. The connector input and output can also be edited, and error handling parameters can also be set.

Basic Properties

The basic properties are used to set object name, identifier and comment.

Export Options

The Export Options page enables the user to specify connection parameters for data export.

Set the data source connection settings manually in the Connection String box or using special dialog box of data transmission channel properties. To open the dialog box, click the button. When the connection string is automatically generated, the %METABASE_OLEDBPARAMS% global variable which is used to store additional connection settings, is added to the box.

In the Table combobox select or enter name of the table to be used as a data consumer. If the table does not exist, it is created after executing ETL task.

Selecting the Clear Consumer Before Loading Data checkbox enables the user to delete all information from the consumer before data loading.

Error Handling

The Error Handling page determines behavior of the object on error occurrence.

NOTE. The page is common for all data consumer connectors, except for XML, Text, JSON and User Consumer. Consider setting up error handling parameters using the example of the Repository data consumer editing wizard.

Set parameters:

Action Executed on Error Occurrence. Select the action in the drop-down list, which is executed if errors occur on data loading:

Stop Task Calculation. If export error occurs, ETL task execution is stopped.
Skip Only Incorrect Records. If an export error occurs, error records will be excluded. Use this action when the presence of all records is not critical.
Skip All Records In One Transaction. On export error occurrence all records of the transaction with the error will be excluded. Use this action when it is required to divide all the records into transactions, in which it is necessary to get all data without exception.

A file that is used to store skipped records can be specified on selecting the Skip Only Incorrect Records or Skip All Records In One Transaction actions.

Save Skipped Data to File. Specify the path to the file to save the skipped records, or select it using the Browse button. If there is no file with such a name, it is created automatically.
Skipped records are stored in the specified file if the Skip Only Incorrect Records or Skip All Records in One Transaction actions are selected for task errors handling. Data is deleted in the existing file before loading new data to it.
Parameters of saving records to file:

Encoding: Unicode.

NOTE. If on export there were no skipped records, the text "??" is written to the file. This means an empty Unicode file with the Win encoding.

Rows separator: {Carriage Return}{Line Feed}.
Fields separator: ; (semicolon).
Text qualifier: "" (double quotes).

NOTE. The following file name is formed by default: <consumer identifier>_skipped records.csv.

Each skipped record is registered in the ETL log as a single record containing the number of skipped record, error text and error field information. If all records within transaction are skipped because of one error record, the log displays this reason in the Description field.

Stop Execution if Specified Number of Records are Skipped. If required, specify the maximum number of error records, over which export is stopped.
Number of Records Processed in One Transaction. Specify the number of records processed in one transaction. Transaction mechanism is recommended to be used to optimize time spent on data export. All records in one transaction are processed as a single whole: if an error occurs when loading at least one record from the transaction, then the entire transaction will be unsuccessful. One thousand records are processed by default.

NOTE. If it is not required to distribute records by transaction, specify the number of records equal to zero. In this case, all records from the source are loaded one by one. This setting is applied only if the Stop Task Calculation or Skip Only Incorrect Records actions are selected in the Action Executed on Error Occurrence drop-down list.

Call the Event if Error Occurred.If it is required to call an event if an error occurs in the ETL task, select the checkbox and specify the unit containing the implementation of the required event handlers. The unit should be pre-specified in ETL task settings.

After executing the operations, the export object behavior is specified in case of error situations on data loading.

Edit Input

To set a list of fields and link to input, use the Edit Input page.

The following parameters are available on the page:

Identifier

Link to object

Fields

NOTE. The screenshot represents edit wizard for the Repository data consumer.

Edit Output

The Edit Output page enables the user to set links to the consumer object, to which data on executing ETL tasks is loaded.

NOTE. The page is common to all data connectors and transformers, except for the Split and User Algorithm transformers. Consider setting up a list of fields and output links using the example of the Repository data source editing wizard.

The following settings are available on the page:

Identifier

Link to object

Fields