Execution of the ETL Jobs List

This topic relates to the following sections:

• Mapping When Using Syniti Migrate ETL Tool

• ETL Jobs

• ETL Jobs Details

• ETL Version Selection

• ETL Tasks at the Release level

• ETL Tasks at the Dataset level

• ETL Tasks at the Target level

• ETL Tasks at the Source level

About Executing the ETL Jobs List

Syniti Migrate ETL Jobs perform the job steps that are otherwise performed within SAP Data Services. The Syniti Migrate ETL tool is available for use in SQL Server, HANA, or Oracle databases. These steps are performed within the pathway Migrate > Mappings at the Release, Dataset, Target, and Target Source levels. Each level is discussed in the linked pages with details of the job steps that are included in each.

This ETL Tool stores all of the rules in a grid to build the conversion of data by automatically generating Truncate, Insert, and Update rules based upon the mappings completed for the dataset. It provides the process for running and storing all of the report data, and it stores the Export file produced that is used to load this conversion to the target system. Every task of the conversion may be sorted to produce the exact sequence required.

Syniti Migrate ETL Tool specific steps:    

• The XML Builds and Views are not visible as they are not relevant for this process.

• SQL Server Pass-Thru views are generated as part of the Import Dataset process based upon the target table assigned check tables.

• The user manually builds out the Views for all Rules, XREF Rules based upon the naming suggested in the ETL.

• The user manually completes the Stored Procedure that is automatically generated from the ADD RULES within each Rule in Mappings.

• The user maintains the order of all steps of the conversion within the ETL Jobs page.

• The user executes the ETL Job process to run the conversion through SQL Server to update the working table and target table for all changes.

If there are changes made to the mappings, these changes are updated automatically within the views and stored procedures for all mappings of type Copy, Default, XREF. The Rules and XREF Rules are set as manual updates but also provide for comments to assist in building the views and stored procedures.

Once a Release has been setup within Migrate > Project Setup to designate the Syniti Migrate as the ETL Tool, the Datasets stored in this Release run through the Extraction, Translations, and produce the export Loading file using Syniti Migrate system.

Project Setup: Release: Edit

Prerequisites for Running ETL Jobs

1. Mappings for the Target must be completed to the point that the user is ready to process through the ETL. Refer to section Mapping the Fields of a Datasource for details of this process.

2. The Target Table and Working Tables should have been automatically generated as part of the Import and Add Target Source processes. Refer to section Create Target Tables for details and refer to section Create WorkingTables for details.

3. The System views for all Value Mappings should have already been created for all mappings of type XREF or Rule XREF. Refer to section System Views for details.

4. Complete the auto generated update rules within the database.

   The user is responsible to complete all RULE and RULE XREF views for this ETL process within the database. During initial ETL build process, the Update_Rules require views to filter and then setup the update field values for these Rules. A view is built in the working database for all Update_Rules (including a mapping RULE, RULE XREF). These views are a shell and they do not run successfully until they are completed by the user - building the actual update scripting in the view. The example below is an UPDATE_RULE view as built by automation.

   

   Working Database: Update Rule: Auto built by system

   This example is of a RULE_XREF update view that auto generated and fail unless the user modifies and completes this view.

   

   Working Database: Update Rule XREF: Auto built by system

Timing for Running the ETL Job

View the following page by navigating to Migrate > Mappings from the Syniti Migrate Homepage.

Once the prerequisites have been completed, the user may access the ETL Jobs page from within each of these panels:

• ETL Tasks at the Release Level

• ETL Tasks at the Dataset Level

• ETL Tasks at the Target Level

• ETL Tasks at the Source Level

Click the ETL   icon to display the ETL Jobs List for the selected level. The first time an ETL icon is clicked, a background job builds out that level of tasks and steps automatically as well as building the Views within SQL Server for all SOURCE_ETL tasks.

Mappings: Dataset: ETL

While viewing the ETL Job page for a level, click on the Execute  icon within the Header bar to start the job. The job queue processes until it stops at a hard error and then the message provides details of the failure.

Note

The ETL Job may be changed to run in a different Milestone (Ex: Mock1, Mock2) as shown by the tab for ETL Version displayed in the header bar below. Refer to section ETL Version Selection for details.

Mappings: Dataset: ETL Jobs List

Maintaining the ETL Jobs List

During the project, continual modifications to the rules (adding, removing, changing the existing rules) necessitates rebuilding the ETL Jobs list at any or all levels. This is a manual task for the user and one that should be performed whenever changes are made to the views built, the field mappings, or table changes.

The specific updates to creation/rebuild/refresh works:

• When a job is initially created, all of its child jobs are also created.

• When a user manually "rebuilds" a job, all of its child jobs are also rebuilt.

• If a new job requires building as part of the Release, the system performs a full "refresh" of the entire ETL "package"...This includes:

• Updates to all 4 interface-level jobs and their children (TRANSFORM, TARGET_EXPORT, etc). During this refresh, any missing jobs are created, and any existing jobs have their parent-child relationships updated by adding/removing ETL_RUN tasks as necessary.

• The Release Transform job is updated, to make sure it includes RUN_ETL tasks for all Datasets.

Execute the ETL Jobs List

Regardless of the level of ETL from which the Jobs are executed, the process is the same.  Once the ETL Jobs List page is displayed, the user may click on the Execute  icon in the header bar to run the Job for all of the listed tasks, or they may click on the Execute  icon for the specific task whether it be a Transform of an entire target or a transform of a single rule. 

ETL Jobs List:  Dataset level for multiple Targets

Within header of this page there are Quick Use icons:

Icon	Name	Use
	ETL Build	This icon runs through the build for ETL Tasks at this level .
	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 runs through the list of tasks in the sequence shown.

The ETL Job grid displays the following Columns:

Another example of ETL Jobs List for the Target Source level provides all of the rules that execute to build the data for the target source listed in the breadcrumb path in the header.

ETL Jobs List:  Target Source level tasks

A Warning window displays upon clicking the Execute Icon for a task.  Click OK button to proceed. 

ETL Job: Warning for Execution of the Job for a task

A Warning dialog box displays upon clicking the Execute Icon in the header. At this point, a breakpoint may be initiated for steps in the run, or the job may be run without breakpoints.

ETL Job: Warning for Execution of the Job

An additional Warning message displays should there be field mappings that are not yet confirmed as Build Complete.  This warns the user that they need to complete the build process in the database and then update the field mapping for Build Status of Complete.  Click Continue to proceed.

ETL Job: Warning for Mappings with Build Status not Complete

View the ETL Jobqueue Steps

Once the Execute icon is clicked and all warnings are accepted, the page moves to the Jobqueue Steps tab of ETL Jobs page to show the progress of the Job Queue steps.

NOTE:  The first column of this grid provides the Debug Log    icon that opens to the list of Debug Log steps for this Jobqueue Step.

ETL Job: Jobqueue Steps tab: Job in execution

To refresh this page and show the progress of ETL Job Steps, click on the Refresh  icon in the Jobqueue Steps panel. 

ETL Job: Jobqueue Steps tab: Job in execution

And should the Job fail before completion, the Step identifies as Jobqueue Status of ERROR with a clear description of the issue stored within column Status Message as shown below.  Once this step error is resolved, the user can click on the CONTINUE JOB button to continue in the job to the next step. 

ETL Job: Jobqueue Steps tab: Job in ERROR

Once the user corrects the error and then clicks CONTINUE JOB button, this warning message displays.  Click OK to proceed and continue this job run, or click CANCEL to exit this action. 

Note

Only the ERROR task is re-queued to run....All 'COMPLETE' steps are left as is.  The CONTINUE JOB button becomes Inactive again.

ETL Job: Jobqueue Steps tab: Job Continue

Errors While Running the ETL Job

Most errors produced while running the ETL Job have to do with either the Complex Rules that may be incorrectly identified in the View or SP naming - missing or incorrect names, or the fields of the working table are missing.  A few examples of these types of errors are listed below.  Make sure to check the DEBUG LOG for any steps marked in Error as it provides detailed information on the issue.

Missing View or Stored Procedure or named incorrectly

Column Missing

Parameter Value Missing