Build XML Scripting for Source data

This topic relates to the following sections:

• Mappings

• Mapping the Fields of a Datasource

• Mapping When Using an Exterior ETL Tool

• Download Individual XML Files

• Create Working Tables

• Working Databases in Migrate

Overview

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

XML Scripting is imported to the SAP Data Services system for use in the extract, translation, and loading of data. This section is relevant for projects using SAP Data Services as the ETL Tool.

This section provides the steps for generating XML scripting that defines the projects, workflows, dataflows, and queries for each of the Target Source Working Tables within Mapping of the Dataset.

Prerequisite to Building XML Code for Source Data

Prior to the build of XML scripting for the Sources (Working table actions), the user should run the BUILD XML steps from within Migrate > Mappings > Release Dataset (Refer to section Migrate > Mappings > Build XML Code for Dataset) to create the target tables in the database and build the XML import code for the ETL tool for all target and overall project build. The Target Source table is assigned to the Mapping Dataset, and the working table is built in the database as part of this process. This section builds out the XML Scripting for the working table steps for Releases that use Data Services for the ETL Tool.

Once the mapping of the source to target fields is complete, the process for building XML code can begin.

Should the user attempt to Build XML for the Source table prior to running Build XML for the Dataset, the message below displays. The process fails until the Dataset XML scripting is generated.

BUILD XML Button

Note

The BUILD XML job builds tables and views in the database. If the user adds fields to the table, the BUILD XML job adds these new fields at the end of the columns of an existing table.

Note

The import of XML scripting to Data Services is meant as an initial create process. Once the Project in Data Services has been modified from the original import file, additional imports of this file will OVER WRITE those changes with the data pulled from Syniti Migrate. As the user builds manual queries, they should manually add or change the structure in Data Services.

From the Sources panel in the Mappings section, click on the Edit icon to view Target Source window. Click the BUILD XML button to generate the Source XML scripting for Data Services for the working database. Click OK to generate XML load files.

Once the job is initiated, a "Build XML Successful" message appears at the bottom right of the Mappings multi-panel page. The next step is to view the XML scripting as well as checking the Job Queue and Debug log to ensure that the automation table builds are complete and successful. Refer to sections - Administer > Job Queue and Administer > Debug Log for more details on these topics.

Note

The working table builds within the database during this process if there is a working database setup and if the connection to the database is active. Should the working table already exist, the process does not rebuild the table. If there are new fields added to the Dataset and imported to the Mappings page, the Build XML process ALTERS the working table to add these fields at the end of the columns. Within the Debug Log for this Job Queue, the DDL scripting is provided as a step if the table is being built. If the table is already built, the scripting is not present in the Debug Log steps. See example of the Debug Log for Build XML in Mappings.

Datastore for Cross Reference

Within the XML loadfile for the Target Source is scripting that calls on the Datastore xAppMigrate. This Datastore joins to the database of same name that is stored within Migration database of Syniti Migrate application on Sql Server. The IT Lead builds out this Datastore as part of the initial setup of the Data Services system. This Datastore provides for Data Services the tables required for XREF insert (XTVALUEMAP). The imported XML files do not run successfully if this datastore is not in place.

Refer to section Migrate > Build xAppMigrate Datastores for more details of this setup and use.

Data Element Translation using Datatype XREF

In the process of building the XML for use in SAP Data Services and building the DDL for use in the working database, the Data Elements go through a translation from the source code to the target code. There are a few notes to keep in mind

• All Datasets that are built within Syniti Migrate Dataset Design shows formatting of the Data Elements as SAP for the Tables and Fields tab and as the target database (any working database) for the Migration tab.

• If the user should IMPORT the Dataset from another project, then the Translation of the Data Elements would copy in from the originating project. In this case, the user may find that the DDL Scripting defaults to all NVARCHAR(255).

Job Queue Details for Source Build XML

The Build XML process within Mappings > Source produces an Executable Job within the Job Queue as well as multiple Debug Log postings.

Note

The BUILD XML job ONLY builds new, in the database, tables and views that do not already exist. Also, if the user adds more fields to the table, the BUILD XML job adds these new fields at the end of the columns of an existing table. It does not over write tables.

Note

The import of XML Scripting from a BUILD XML job to SAP Data Services OVER WRITES all scripting of same with new import.

Job Queue Errors from Build XML

The user needs to monitor both the Job Queue and the Debug Log to determine why the job contains errors. In a few cases the issue is very clearly defined, but at times it may take pasting the Create statement into the database to provide a detailed error message. However, most of the common errors are identified and provide accurate description.

Although the Job may show Status of ERROR, the build still may be complete. The ERROR could be in the actual building of the working table in the database. At this point, it is helpful to manually attempt to build the same script in the database to see the issue and find resolve. From within the Debug Log the scripting of the DDL table may be copied and then pasted into an open query in the database to attempt to build the table manually.

The Database results in gaining the new working table.

Additional Reference Information on the Process for Building XML within a Source

For details of the process of importing XML scripting into the ETL tool, see the associated ETL tool document -

• Auto generating DDL script into the HANA Database tool. Refer to section The Data Migration Process > Migration Using SAP HANA DB as the Database

View XML Button

After generating the Target Source XML scripting, click the VIEW XML  button to display the files. This build generates the Source tables, Source Dataflows, the XTVALUEMAP table and insert process, the Target Insert Dataflow, and the Source Workflow scripting.

The XML file types for Source Mappings are below:

It is at this point that the user clicks the GET LOADFILE  button to generate and download the XML file to the local downloads folder. This file may now be imported to the ETL tool to build out the overall project and components to convert the data at the target level.

Note

There is the option to download the individual XML files for specific Data Flows and Work Flows depending on the need. The LOAD FILE XML file at the top contains all the associated Work Flows, Data Flows and Datastore table definitions.

The XML Load Date is updated each time the BUILD XML is run. The DDL is generated each time it's built, so those tasks are basically in sync. The actual DDL jobs are created as Background Jobs in the JobQueue, so you can review the progress and status of the execution in the JobQueue.

Download Individual XML Files

Download the XML file by clicking the XML  button adjacent to the selected XML file type. After clicking the button, the message "Generated XML file successfully" should appear at the bottom of the page and the file should be downloaded to the local download folder. Refer to Download Individual XML Files for more information.

Download XML Button

After generating the Target Source XML, click the DOWNLOAD XML button to download the entire file to the local drive. This generate and save to the local Download directory the XML file for use in import to the ETL.

Subsequent Steps

• Once the Target Source XML scripting is built, the user should download the files.

• Within Data Services, build the Datastores for xAppMigrate and xAppMigrate_Webservices.

• Together with the Dataset XML scripting file, import the XML scripting files into the SAP Data Services system as Dataset and then Target Source files.

• There maybe a situation where a singular file needs to be imported from Syniti Migrate into the Data Services system. The user can also Download Individual XML files.