ETL Tasks at the Source Level

This topic relates to the following sections:

• Mapping When Using Syniti Migrate ETL Tool

• ETL Jobs

• ETL Jobs Details

• Execution of the ETL Jobs List

• ETL Version Selection

• ETL Tasks at the Release level

• ETL Tasks at the Dataset level

• ETL Tasks at the Target level

• ETL Tasks Add Complex Rule Manually

Overview

ETL Jobs are accessed from all four levels of the Mappings page. Each ETL icon when clicked initially builds the job tasks and views and displays that level of the overall ETL Job with rows of Job Tasks linked together. View the following page by navigating to Migrate > Mappings on the Syniti Migrate Homepage. This section discusses the ETL Jobs from the level of Sources.

The source level ETL Jobs list contains a single row for each task of the working table conversion in the sequence they run. Each task contains an Action (Source Table Truncate, Source Table Insert, Source Xrefs, Update Rules, Target Table Delete, and Target Table Insert as examples) that performs against the view that is built within the database for that task. The tables used in these views are auto-generated as part of the mapping process. The system dynamically in real-time generates a procedure to run within the ETL Job. The system uses the View definition and rule Metadata to construct the same rule action. The views must contain columns aliased to match the target fields in the Affected Table such that all fields may be updated / deleted / inserted as expected. Any fields that do not match a field in the Affected Table is ignored.

The ETL Job at Source level contains these tasks which are performed as actions in Jobqueue steps:

• Truncate removes all values and resets the working table.

• Insert adds all rows of data from the srcDatasource to the working table for this object.

• Update Rule auto generates from complex XREF Rules and Rules. (of type SQL or Procedure)

• Source Xrefs perform the Insert and Update functions for XTVALUEMAP table. (all Insert and Update XREF rules store within)

• Target Delete truncates the target table in groups of 10,000 records filtered by Datasource.

• Target Insert inserts all relevant records from the working table.

The user ensures that the view is complete - the system auto generates each view as they are added as tasks, but they all must be completed and accurate for successful use. Execute either the entire source ETL job or each individual task.

Note

In the case of an Update, Insert, or Delete rule, these may be run against any working database in the current server. The fields Affected Datasource and Affected Table designate the target of the rule and can apply to SQL Server, HANA DB, or Oracle DB systems. The views are stored in the Rule Datasource (working database of the object), however, they pull from a different or Affected Datasource (any other database of the same server).

Note

The Breadcrumb path in the header links to higher levels of the ETL Job chain. The user may jump up from Source to Target or to the Dataset level details for ETL Tasks with a click on the task name.

Prerequisites

Prior to working with ETL Job tasks at the source level, there are steps to prepare the system for using Syniti Migrate ETL. Refer to section Execution of the ETL Jobs for details of proper set up.

The ETL task for Xref Rules depends upon the table XTVALUEMAP which is stored within snapshot management Datasource SRCCONSTRUCT. This snapshot datasource must be built prior to the build of the ETL Tasks for the source level, or the system defaults a view that fails during the ETL Task run. Refer to section Add a CONSTRUCT Snapshot Datasource for details.

Source Transform

At this level, the source for the target may be run as one execute in the sequence of the task list, or the individual tasks may be run. Click the Execute icon within the header to run all tasks, or click on the Execute icon for the specific task. These lists contain many tasks, so to stop the ETL Job at a task, edit that task to provide active Debug. Refer to section Setting a Task for Debug for details.

Note

Click the breadcrumb path to move to the linked page for the higher level of this ETL Job list.

Within header of this page there are Quick Use icons:

Icon	Name	Use
	ETL Build	This icon generates build for ETL Tasks at this level
	View Mapping	Click to change pages to view the related mappings of this object
	Add	This icon adds a new task to the current Task Type
	Refresh	Refresh updates the page to the latest data
	Search	Allows for a search of the Tasks grid by Column
	Execute	The execute icon in header processes the list of tasks in the sequence shown.

The ETL Job grid displays the following Columns:

ETL Tasks stored within Mappings

Within Edit Mappings, there are unlimited amounts of updates that may be made against each field. Once a Rule is saved, it displays in a grid for ETL Tasks for Mapping Rules page as shown below.

The ETL Rules grid displays the following Column Headers:

Add a Complex Rule

Refer to section ETL Tasks Add Complex Rule Manually for details of this process that works the same at all levels of the Mappings to include Release, Dataset, Target, and Source levels.

Edit a Complex Rule

A click on the Edit  icon provides a window to make changes to the Description, Comment or any of the fields within section Execution. The Active toggle may be updated from the grid.

Within the details for ETL Rules, the user may alter the field values as described below.

When finished with edits, click the Save  icon to complete.

Source Xref Rules

The ETL Job automatically generates a task called Source Xrefs within the Target Source ETL Job for all of the XREF rules. This includes every Xref and RuleXref mapping to provide an Update and Insert rule for each field mapped. As shown in the images below, there may be many rows of these automatically generated rules that build for each Active field that is set within Administer > Value Mapping Config for the Check Table as Active for Value Mapping. A prerequisite to this step is building the SRCCONSTRUCT snapshot datasource as it is required to store the XTVALUEMAP table.

In the case of the RuleXref, additional Complex Rules are added to the ETL Task list to populate the ZXREF_FIELDNAME prior to running through the Source Xref rules that INSERT to the XTVALUEMAP table all new pulled legacy values and then UPDATE within the Working Table all zXREF_FIELD values from the Target value in XTVALUEMAP table.

Note

If Check Tables are flagged as not active for Value Mapping, then these rules do not automatically generate as part of the build process, and hide should the toggle change after they are built.

Click the Source Xrefs link to display the details page with a list of the current active mapped fields as Rule Xref or as Xref that promote the INSERT and UPDATE of values from Value Mapping.

Click Edit  icon to view the details for running these rules. INSERT Rule - the Affected Datasource is always set to MIGRATE and the Affected table points to XTVALUEMAP.

Note

Datasource MIGRATE is built within Catalog > Datsources and it points to the base database for the project within Syniti Migrate SQL Server. In this way, the Insert Rule passes these values into the Migration base database to the XTVALUEMAP table.

The tab for Rule DDL provides scripting of the Insert rule.

Click Edit  icon to view the details for running the UPDATE Rule - the Affected Datasource are set to the Working database within Oracle DB, and the Affected Table is set to the Working table of this object.

Complex Rule for Cross Database Insert

Refer to section Cross Database or Cross Server ETL Rules for Delete, Insert, or Update for Cross Reference rules that insert from one database to another as a Complex Rule.

Errors Generated by the ETL Job Tasks

Once the ETL Job queue has been started, the system validates each row of tasks for required details such as whether the view or table exists in the database. In this example, a rule has not been set up in the database but is referenced within a job task. If the user clicks OK, the ETL Job remains inactive.

The link in the above message opens the Debug Log for this job queue and the message displays the issue. The working table is not yet created for this mapping.

As the ETL Job Tasks execute, the Validation Status ID updates as Complete, Error, or Unknown. In the case where a task is marked as not active, the status will show as Unknown. The Jobqueue Steps tab displays each Step with a link to the Debug Log for details of the step processing.

Click the Debug Log icon to view the details of that tasks processing.

Setting Task for Debug

In a source that contains hundreds of tasks that run as the ETL Job, setting a task for Debug is very helpful. The ETL Job can complete the steps prior to the task set for debug, and stops at the task set for debug to allow for review of the table data and updates made to that point. This is set at the task level. Click the Edit icon for the task that marks the review as Debug active. Any task type may be set as Debug active.

This toggle remains active until it is set to inactive. To process the ETL Job with an active Debug indicator, click the Execute icon from within the header bar to process the entire source. A warning message requests response. In this case, select Continue With Breakpoint to ensure the job stops at the debug task.

A new job queue is created and the ETL Job processes to the breakpoint. The user validates the rules and updates to this point. Once ready, click the CONTINUE JOB  button to proceed to the next breakpoint or end of tasks. Each time the job completes, it sends the user an email confirmation of job status.

A warning message to continue from the debug step must be confirmed with OK to proceed.

The job continues through from the last stop. The job is requeued.

The ETL Job either stops at the next Debug, fails, or in this case, completes as expected.

Execute the ETL Job at the Source Level

Refer to section Migrate > Mappings > Migration ETL Job > Execution of the ETL Jobs List for details of this process.