Configure a source connection to connect to your source. Configure the source connection on the Connect Source page. You can create a new connection or select an existing connection.
The following image shows the Connect Source page:
To create a source connection, click New Connection. You can also select an existing connection. After you choose the connection, you might have to configure additional connection settings like Row Limit and Use queryAll that vary based on the connection type. For more information about connection properties, see Connections.
By default, Data Integration adds only the source objects you want to include. You can configure this feature using options in the Source Objects field.
For some connection types, Data Integration automatically detects primary key fields and watermark fields. Watermark fields identify the records that were added or changed since the last task run.
To increase performance by removing unnecessary data, by default Data Integration adds only the source objects you include in the source location.
Use the following guidelines when customizing your source:
Remove unnecessary data from the data flow.
When you create a task, it's best to remove unnecessary source objects, fields, and records from the data flow. Removing unnecessary data decreases the time it takes to run a task and helps to minimize rejected records.
You can choose which source objects to read on the Connect Source page. You can also select the fields to exclude and configure filters to exclude unnecessary records.
To prevent duplicate target rows, configure primary key fields.
Primary key fields uniquely identify records in the source and target objects. When you re-run a task, the task uses the primary key fields so that it can update existing rows and insert new rows into the target tables. If the source objects don't have primary key fields defined, the task inserts rows into the target tables, but it cannot update existing rows, which can lead to duplicate rows in the target tables.
Data loader tasks can automatically detect primary key fields for most connection types. You can also select the primary key fields manually. Configure primary key field options on the Connect Source page.
To process only new and changed data, configure watermark fields.
Watermark fields are date/time or numeric fields that identify which records were added or changed. If the source objects don't have watermark fields defined, the task must process all records in the source objects each time the task runs, which increases the task processing time.
Data loader tasks can automatically detect watermark fields for most connection types. You can also select the watermark fields manually. Configure watermark field options on the Connect Source page.
The following table lists the possible primary key and watermark field configurations and the expected results:
Primary key and watermark field configuration
Result
Primary key fields configured.
Watermark fields configured.
Changed records updated, new records inserted into the target tables (upsert).
Recommended configuration for best performance.
Primary key fields configured.
Watermark fields not required.
Changed records updated, new records inserted into the target tables (upsert).
Impacts task performance because the task must perform a full scan on the source.
Primary key fields not required.
Watermark fields configured.
All records inserted into the target tables. Records that already exist are duplicated.
Primary key fields not required.
Watermark fields not required.
All records inserted into the target tables. Records that already exist are duplicated.
Impacts task performance because the task must perform a full scan on the source. This is the least recommended configuration.
Selecting source objects
After you configure a new connection or select an existing connection, you select the objects to read.
To load the data as quickly as possible, configure the task to read only the source objects that you need to process. Reading data from fewer source objects decreases the time it takes to run the task.
You can configure the objects to read on the Connect Source page under Define Objects.
By default, the task reads data only from the objects you include in the source location. If you want to read data from most objects in the source location, choose Include some or Exclude some, then select the objects that you do or don't want to read. If you want to read data from all objects, choose Select all. If you choose Select all a data loader task can read up to 2000 objects.
If you choose to read all objects or exclude some objects, you'll need to enter the source path for some source types. If you entered a source path when you created the connection, the default source path is the same as the one you entered when you created the connection. Otherwise, the source path is empty by default. If you plan to read all objects or exclude objects in different locations, enter the path to the parent container.
To configure the objects to exclude or include, click the plus sign (+) icon in the Excluded Source Objects or Included Source Objects area. After you select objects, the Excluded Source Objects or Included Source Objects area displays the objects you excluded or included. To delete an object, click the Delete icon in the row that contains the object.
Source path syntax
The source path syntax varies based on the connection type. To find the source path syntax, click Edit.
For most source types, you enter the source path in the format <container or bucket name>/<folder name>/<subfolder name> or <database name>/<schema name>. If you enter a source path for an Amazon S3, Azure Blob Storage, or Azure Data Lake Storage Gen 2 source, you'll need to append a slash character at the end of each folder name to distinguish the folder from a file. So, for these sources, the source path format is <container or bucket name>/<folder name/>/<subfolder name/>, for example, bucket1/folder1//folder2//folder3/.
For more information about connection properties, see Connections.
Defining the source format
If you connect to a file source, you'll need to configure formatting options such as the file format, schema source, or delimiter.
Configure the formatting options on the Connect Source page under Define Source Format. If you don't see the Define Source Format area, then you don't have to configure formatting options for your source.
When you configure formatting options, you can preview the data to ensure that the formatting options are correct. You can select the fields to use in the data preview.
Selecting source fields
By default, a data loader task reads the fields only from the sources you include and loads them to each corresponding target. You can exclude unnecessary fields for one or more source objects so that they are not written to the target. Excluding unnecessary fields from the data flow helps to increase task performance.
Configure the fields to exclude on the Connect Source page under Exclude Fields. To exclude fields, click the plus sign (+) icon in the Excluded Fields area, and then select the source object and the fields to exclude.
The Excluded Fields area displays the fields you excluded for each source object. To update the excluded fields for a source object, click the excluded fields for that source object. To delete all excluded fields for a source object, click the Delete icon in the row that contains the source object.
Configuring filters
You can configure filters for one or more source objects so that only records that match the filter conditions are written to the target. The task processes the records that match all of the filter conditions.
Configure filters on the Connect Source page under Define Filters. To add filters, click + in the Filter Conditions area. In the Configure Filters dialog box, select the source object and configure one or more filter conditions.
This image shows the Configure Filter dialog box:
To add a filter condition, click the plus sign (+) icon. Each filter condition contains a field, operator, and value. You can use the following operators in a filter condition:
•= (equals)
•< (less than)
•> (greater than)
•<= (less than or equal to)
•>= (greater than or equal to)
•!= (not equals)
Enter date values in the format YYYY-MM-DD HH24:MI:SS.
If you add multiple filter conditions, the task evaluates the filter conditions in the order in which they appear in the Configure Filters dialog box.
For example, you want to extract records from the Orders table only when the city is New York and the order date is later than January 1, 1990. Select "Orders" as the source object, and configure the following filter conditions:
•City = New York
•OrderDate > 1990-01-01 00:00:00
The Filter Conditions area displays the filters you added for each source object. To update the filters for a source object, click the filters in the row that contains the source object. To delete all filters for a source object, click the Delete icon in the row that contains the source object.
Selecting primary key fields
Data loader tasks can automatically detect primary key fields for many source types. You can also choose your own primary key fields, or set them as not required.
Primary key fields uniquely identify each record in a source object. For example, in a CUSTOMERS table, the values in the CUSTOMER_ID column uniquely identify each customer. A source object can have one primary key field, a composite key that consists of several primary key fields, or no primary key fields.
Primary key fields are needed if you want to update rows when you re-run the task. If there are no primary key fields, the task inserts records into the target tables, but it cannot update rows.
Select primary key fields on the Connect Source page under Define Primary Keys.
You can select primary key fields in the following ways:
Automatically detect from source
The task identifies the primary key fields automatically in each source object that has them. This is the default option for many types of sources. This option is not available for file-based sources.
If you choose this option but there are no primary key fields in the source objects or the task can't detect the primary key fields, then the task inserts records into the target tables.
Primary key fields not required
Select this option if you want to perform a full load every time the task runs. For example, you want to read data from database tables that have primary key fields, but you don't want to update rows in the target when you re-run the task.
When you select this option, the task inserts records into the target tables.
Enter manually
Select this option to choose the primary key fields in each source object. If you select this option, you must also specify whether the source objects have the same primary key fields or different primary key fields.
If the source objects have the same primary key fields, select The same across all sources. Then, click Choose to select the primary key fields.
If the source objects have different primary key fields, select Different across sources. Then, click the plus sign (+) icon in the Primary Key Fields area and select the source object and the primary key fields. Repeat this process to select primary key fields for other source objects.
The Primary Key Fields area displays the primary key fields for each source object. To update the primary key fields for a source object, click the primary key fields for that source object. To delete all primary key fields for a source object, click the Delete icon in the row that contains the source object.
Selecting watermark fields
Data loader tasks can automatically detect watermark fields for many source types. You can also choose your own watermark fields, or set them as not required.
Watermark fields are date/time or numeric fields that identify the records that were added or changed since the last task run. For example, you might select the MODIFIED_DATE column as the watermark field for a source table. A source object can have one or no watermark fields.
If you want the task to load only new and changed data, you'll need to make sure that watermark fields are configured. If there are no watermark fields, the task processes all fields in the source objects.
Select watermark fields on the Connect Source page under Define Watermark Fields.
You can select watermark fields in the following ways:
Automatically detect from source
The task identifies the watermark field automatically in each source object that has one. This is the default option.
If you choose this option but there are no watermark fields in the source objects or the task can't detect the watermark fields, then the task processes all records each time the task runs.
Watermark field not required
Select this option if you want the task to processes all records each time the task runs.
Enter manually
Select this option to choose the watermark field in each source object. If you select this option, you must also specify whether the source objects have the same watermark field or different watermark fields.
If the source objects have the same watermark field, select The same across all sources. Then, click Choose to select the watermark field.
If the source objects have different watermark fields, select Different across sources. Then, click the plus sign (+) icon in the Watermark Fields area and select the source object and the watermark field. Repeat this process to select watermark fields for other source objects.
The Watermark Fields area displays the watermark field for each source object. To update the watermark field for a source object, click the watermark field for that source object. To delete the watermark field for a source object, click the Delete icon in the row that contains the source object.
