Introduction

This page describes how to use CData Sync to extract data. The load-from-file extraction method that is used as an example, which is available in all UiPath Process Mining Connectors.

The default extraction tool for connectors is CData Sync. With CData Sync you can define connections to extract data from a source to a destination. See the illustration below for an overview.

For general instructions on how to use CData, see the official CData Sync documentation Getting Started with CData Sync.

CData Sync is only used for extracting data and not for applying data transformations. Therefore, the transformation functionality in CData Sync is not used.

The default extractor that should be available in any connector is the load-from-file extractor.

Load-from-file

The load-from-file extraction method is available in all connectors. This extraction method loads data from .csv files and stores it in a SQL Server database with the use of CData Sync.

Create source connection

Follow these steps to create the source connection.

Step	Action
1	Click on Connections and select type CSV on the Sources tab of the Add Connection dialog.
2	Set the URI to the path where the `.csv` files are stored. The `CSV` file connection can be set either using a local file path or an online document storage using the correct credentials.
3	Select the Connection String option button.
4	Enter the connection string in the Connection String field: `URI=[c:\dba_file_location]; FMT=TabDelimited; PushEmptyValuesAsNull=True; TypeDetectionScheme=None; ExcludeFileExtensions=True; IncludeFiles=CSV,TXT,TAB,TSV;` Note: Make sure the `URI` is the path where the `.csv` files are stored.

See the illustration below.

Create destination connection

Follow these steps to create the destination connection.

Step	Action
1	Go to the Destinations tab in the Add Connection dialog and define a new connection of type SQL Server.
2	Configure the settings to connect to your SQL Server database. If you want to use DataBridgeAgent for loading data into a MS SQL Server database, make sure to use the database name `ProcessMining_<APP_GUID>`.

See the illustration below.

Create job

Follow these steps to create the job.

Step	Action
1	Add a new job.
2	Set the source connection to the `CSV` connection.
3	Set the destination connection to the SQL Server connection.
4	Go to the Advanced tab, and set your database schema as the `Destination Schema`.
5	Enable the checkboxes `Alter Schema` and `Drop Table` and disable the other checkboxes.
6	Go to the Tables tab and click on Add custom query. Enter a query of the following form to select the files of the source: `REPLICATE [File1]; REPLICATE [File2]; REPLICATE ...;`
7	Click on Save Changes.

The column mappings in the job settings, will be set automatically and do not have to be changed.

Using DataBridgeAgent

If you want to use DataBridgeAgent for loading data into a MS SQL Server database, the custom query has to mirror the output of the connector you are using. For example, for the SAP connector for Purchase-to-Pay discovery accelerator, the query would look like this:

REPLICATE [Accounting_documents_base_raw];
REPLICATE [Events_base_raw]; 
REPLICATE [Goods_receipt_base_raw]; 
REPLICATE [Invoice_items_base_raw]; 
REPLICATE [Invoices_base_raw]; 
REPLICATE [Payments_base_raw]; 
REPLICATE [Purchase_order_items_base_raw]; 
REPLICATE [Purchase_orders_base_raw]; 
REPLICATE [Purchase_requisitions_base_raw];

Execution

To run the extractor, execute the script cdata-extraction.ps1 that can be found in the scripts folder of the connector repository. For more information on running the extractor script, see Running a SQL connector.

UiPath Process Mining

The UiPath Process Mining Guide

CData Sync extractions