Interface Export

 

The details outlined in this document represent the requirements to export data from MTech to another system. This document summarizes the base concept for all exports, however each export will be uniquely defined based on the requirements. The assigned Project Manager can provide sample interface requirements for many types of  export interfaces.

 

 

The following procedures and options are covered in this document:

Create an Interface

The Create an Interface section contains the following components:

 

  1. In Admin>System>Interfaces, select Interfaces.
  2. Select to create a new interface.

 


  1. Enter an Interface Name for the interface. Alphanumeric, 50 characters maximum.
  2. Select the Interface Type as ``Export`.
  3. Select the Interface Class ID as required for the export.
  4. Interface Description is an optional location to enter a description for the export to identify the data that is being exported as well as any other details related to the export.
  5. The Schedule flag will be selected if there is a schedule associated with the interface. If the flag is not selected, then there is no schedule assigned and the interface must be run manually.
  6. Web Service is currently not an available option.
  7. Last Run Status displays the status after the interface was last executed. If 'NA' appears as the status, the interface has never been run. Otherwise, the field will display 'Success' or 'Failed'.
  8. Last Run Date indicates the date that the interface was last executed. The default date of 11/30/1899 will be displayed until the interface is run for the first time.
  9. Creation User ID displays the user that created the interface.
  10. The Prompts section is intended to be used for Web Service which is currently not an option.
  11. Click the in the menu bar to complete the remaining requirements for the export interface.

 

General Export Configuration

  1. Selecting the Wizard option will open the General Export Configuration.

 


  1. Export Name and Export Type will be derived from the initial screen.
  2. Select the Destination Type. The current options available are Database, File, FTP and OData.
  3. Enter the number of lines that will be viewed in the export preview in the Export Preview Amount. This data will be viewed to validate that the source data appears correct.
  4. Select the required option for Last Run Status Option. This option determines what the status will be if no records are exported. The options available are NA or Failed.
  5. Click Next to move to the Source Configuration screen.

 

Source Configuration

  1. In the Source Configuration screen, select the Source Definition Method. Detail Query Builder, Master Query Builder and Summary Query Builder will all use system interface classes to develop queries to extract the data. Free Form SQL will be created manually without the use of interface classes.
  2. Click on the ellipse on the Source Definition to select the fields required to be exported. This will open the report class with all of the available fields.
  3. Define prompts if required.
  4. Close and save the definition.

 

     

  1. Select the Persist Notifications flag if the prompt data is to be saved. Not selecting this option will clear all data from the prompts.
  2. Click on the ellipse in the Intermediate SQL field if additional SQL statements must be run against the data being exported to change or modify fields.
  3. Post Process Update SQL is used to update the SQL after the records are exported. Click on the ellipse to define the SQL statement. This option will not execute from the wizard.
  4. The Post Process Update Temp Table Name is the name of the temporary table used to stage the data before the export.
  5. Post Process Update SQL Option determines the conditions of when the Update SQL will be initiated.
    1. Default - Update SQL will run after export completes. If error notifications exist, Update SQL will not run.
    2. Run Always - Update SQL will run regardless of whether the export fails or succeeds.
  6. Select this option to use the Destination Connection String in the Post Process Update SQL. If the option is not selected, the process will use the current connection string. This option cannot be selected if the Destination Type is set to File.
  7. Click Preview Data to view the export data. The user will be prompted for any defined prompts which can be viewed on the Prompts tab.
  8. Click Next to move to Destination Configuration.

 

Destination Configuration

Depending on the Destination Type selected in the General Configuration, the Destination configuration screens will have different requirements. Click on the following Destination Type to view the relative requirements:

Database

  1. In the Destination Connection, select the ellipse to open the Connection Builder.

 

    1. Select the Database type. Options will be Microsoft SQL Server, Microsoft Access or Oracle Database.
    2. Select the required Server.
    3. Select the Authentication. Options will be Windows Authentication or SQL Server Authentication.
    4. If required, enter the Login and Password.
    5. Select the required Database.
    6. Click OK.

     

     

  1. Select the Destination Table.
  2. In the Insert/Update Option, select one of the following options.
    1. Append to Existing Data
    2. Delete and Replace Existing Data
    3. Update Existing Data
  3. The Initialize Destination Table determines if the destination export table is deleted. If selected, the process will delete the destination table. User supplied prompts will be used to create the filter for the table to be deleted. If the option is not selected, then the process does not delete the destination table.
  4. Export All or Nothing determines how the records are exported. If the option is selected, the entire export will fail if any record fails. If not selected, only failed records will not export.
  5. Click Next to move to the Field Mapping screen.

    6.  

     

  6. In the Field Mapping screen, map the source fields to the destination fields. At least one field must be selected as the Unique Field.
  7. Once all of the field have been mapped, click Next to move View Any Export Error.
  8. Select Test Export to view the export data. Errors will be generated if data errors exist.
  9. Click Next to move to E-Mail Configuration or Finish to complete the interface definition if e-mail is not required.

 


File

  1. Select the Directory Path Name for the data to be exported.
  2. Select a Data Server if the UNC patch requires credentials. If the export file is local, leave the field blank.
  3. Select the File Name that will receive the export data.
  4. The File Extension will default from the selected file.
  5. Enter the Date Format to Append the current date and time to the file. An example would be: YYYY-MM-DD_hh_mm_ss.
  6. Full Path Preview provides the user with the complete destination location for the export data.
  7. Export to File Format indicates the type of file that will receive the export data. Options are CSV, Fixed Field, Pipe, Tab or xml.
  8. Select Output Column Headers if the data headers are to be exported to the export file. Do not select the option if the headers are not required.
  9. Select Do Not Create File If No Records if the file is not to be created where no data exists. Leave field blank if the file is always to be created.
  10. Click Destination File Preview to view the data that will be exported.

    11.  

     

  11. Click Next to move to the E-Mail Configuration screen or Finish to complete the interface definition if e-mail is not required.

FTP

Prior to using FTP as the required Destination Type, the FTP server must be set up in Admin>System>Servers.

 

 

  1. Select the FTP Server that will receive the data.
  2. Select the Directory Path Name for the data to be exported.
  3. Select the File Name that will receive the export data.
  4. The File Extension will default from the selected file.
  5. Enter the Date Format to Append the current date and time to the file. An example would be: YYYY-MM-DD_hh_mm_ss.
  6. Full Path Preview provides the user with the complete destination location for the export data.
  7. Export to File Format indicates the type of file that will receive the export data. Options are CSV, Fixed Field, Pipe, Tab or xml.
  8. Select Output Column Headers if the data headers are to be exported to the export file. Do not select the option if the headers are not required.
  9. Select Do Not Create File If No Records if the file is not to be created where no data exists. Leave field blank if the file is always to be created.
  10. Select Next to move to E-Mail Configuration or Finish to complete the interface definition if e-mail is not required.

 

OData


  1. In the Destination Service Root Address, enter the required destination address.
  1. Select the Destination Entity Set from the available options.
  2. In the Insert/Update Option, select one of the following options.
  3. Click Next to move to the Field Mapping screen.

 

     

  1. In the Field Mapping screen, map the source fields to the destination fields. At least one field must be selected as the Unique Field.
  2. Once all of the field have been mapped, click Next to move View Any Export Error.
  3. Select Test Export to view the export data. Errors will be generated if data errors exist.
  4. Click Next to move to E-Mail Configuration or Finish to complete the interface definition if e-mail is not required.

 


E-Mail Configuration

  1. Select the Send E-Mail flag if there is to be an e-mail sent when errors occur in the process.
  2. In the To: field, enter the e-mail addresses that are to receive the e-mail separated by a semi-colon.
  3. In the Cc: field, enter the e-mail addresses that are to copied on the e-mail separated by a semi-colon.
  4. The Subject must be defined if the Send Email option is selected. If this is not acceptable, enter the text that is to appear in the e-mail subject line.
  5. Click Next or Finish to complete the wizard process.

 

Execute Interface

The Execute Interface option runs the interface definition and exports the data from MTech. Options for generating the interface are as follows:

 

  1. From Admin>System>Interfaces, select the interface and right-click to select Execute Interface.
  2. From Admin>System>Interfaces, select the interface and select Options>Execute Interface from the tool bar.
  3. From Admin>System>Interfaces, edit the interface and select Options>Execute Interface from the tool bar.

Interface Error Logs

The Interface Error Logs provide details related to number of records exported and specifies any errors that may be incurred during the export process. There are two access the logs. The 'View Logs' option displays all logs that have been generated from the interface. The 'View Last Log' only displays the last generated log. Options for viewing the interface logs are as follows:

 

  1. From Admin>System>Interfaces, select the interface and right-click to select View Logs or View Last Log.
  2. From Admin>System>Interfaces, select the interface and select Options>View Logs or Options>View Last Log  from the tool bar.
  3. From Admin>System>Interfaces, edit the interface and select Options>View Logs or Options>View Last Log  from the tool bar.

 

This will display the Export Error Log.

 

Export Log
  1. Interface displays the name of the interface that has generated the error log.
  2. Run Date Time indicates when the interface was executed.
  3. The Status will display how many records were Exported based on the total records.
  4. The Data Source represents the name of the source file that contained the data.
  5. Error Message will display any error messages that relate to the overall interface configuration.
Export Log Errors

This section displays errors related to the actual data that is being Exported.

  1. Record Display Name indicates the table record that the data is being Exported.
  2. Invalid Property Name will provide the details for any errors that are generated from the interface.
  3. Error Detail provides a description related to the error so that the user can determine how to resolve.

 

Scheduling Interfaces

There is an option to schedule interfaces for those that are required on a regular basis. Before the scheduler can be used, the Job Service Scheduler must be installed. The following topics are covered in this section:

Install Job Scheduler Service

  1. The user will be provided with a file for the installer named MTechSystems.JobScheduler.Setup. msi.
  2.  Double-click to start the installation process and install in a folder dedicated to the installer. The job scheduler will install as a Windows service called MT Job Schedule Service.
  3. Configure the Job Scheduler Service with a user that has permissions to read and write from the Export and export folders. This can be accomplished by selecting the Log On tab in Service Properties and use This Account option to define the user. A domain service account may also be created for this purpose.

    4.  

     

  4. Edit the MTechSystems.JobScheduler.Service.exe.config file in the installation folder of the MT Job Scheduling Service (completed in Step 1). Edit the datasource.default.connectionstring value to match the values found in the mtech.cnn file.

    5.  

  5. Edit the Proteinexe.exe.config to reflect the server:port specified from the MTechSystems.JobScheduler.Service.exe.config.

    6.  

     

  6. Ensure that the Protein folder and subfolders have read/write/modify permissions for all users that intend to use the program.

Configure Scheduler

This section will demonstrate how to assign a schedule to the interface.

 

  1. To schedule a job, select the required job from the index and right-click to select Schedule Job.
  2. This will launch the Job Scheduler Wizard for the selected interface.
  3. If no triggers exist, select the Create Trigger tab. In the Trigger field, enter a name for the schedule trigger, such as Daily, Weekly, Hourly and select Create Trigger.
  4. If Triggers exist, click the Select Trigger tab and select the required trigger for the interface.
  5. Click Next to move to the configuration of the General details.

 

  1. Trigger Name will default from the selected trigger as read-only.
  2. Job Name will display the system code for the interface that is being scheduled.
  3. Job Group will display the type of interface. The field will display Export Group or Export Group depending on the type of the interface.
  4. In the Trigger Description, enter a description for the schedule.
  5. Select one of the following options for Trigger Type.
    1. Cron Trigger - used to schedule jobs that recur based on calendar-like notions.
    2. Simple Trigger - used to schedule jobs that occur at a specific moment and recurring in regular intervals.
  6. Enabled will default to selected to indicate that the schedule will be executed. Un-check the option if the schedule is no longer required to be run.
  7. Select the Trigger Priority. Options will be High, Medium or Low.
  8. Select Next to move to Conditions. The Conditions screen will be different for Cron Trigger or Simple Trigger.

 

Cron Trigger

  1. Select the option for the schedule as Daily, Weekly or Monthly. Depending on the selected option, the configuration screen will appear with different options.

Daily

  1. In the Start Field, enter the start date and time for the schedule.

  2. In the Recur Every (Days), enter the number of days before the next schedule runs.

 

 

Weekly

    1. In the Start field, enter the start date and time for the schedule.
    2. Enter the recurrence in the Recur Every (weeks) field.
    3. In the On the Days field, select the days that the day(s) of the week that the schedule is to run.

 

 

Monthly

  1. In the Start field, enter the start date and time for the schedule.

  2. Select the Months that the schedule is to run.

  3. Days allows the user to define the day of the month that the schedule will run. For example, the schedule will run on the 15th day of the selected month(s).

  4. Week In Month allows the user to define the week in the month that the schedule will be run. Options are First, Second, Third, Fourth or Last. Select Not Set if this option is not required.

  5. Days in Month allows the user to select the day in the month that the schedule will be run. In the example below, the schedule will run on the last Saturday of every month.

 

  1. In the Advanced Setting section, set the Repeat Task Every to '0' as it is not required for this feature.
  2. In the Advanced Setting section, set the Repeat Task Every to '0' as it is not required for this feature.
  3. Set the Seconds, Minutes or Hours field to None as it is not required for this option.
  4. Select the required option For Duration of.
  5. Enter the date and the time that the schedule will Expire.

    6.  

Simple Trigger

  1. Enter the Start Time for the schedule.
  2. If there is a defined End Time, enter the time in this field, otherwise leave the field blank and the interface will run continuously based on the other parameters.
  3. Enter the frequency in the Repeat Task Every field.
  4. Select the time frame in Seconds, Minutes or Hours field.
  5. There are two options in Trigger Repeat Mode. Repeat Indefinitely indicates that the schedule will continue to be run to infinity or until the schedule is disabled. Specified Count will run the schedule a specified number of times.
  6. If the Trigger Repeat Mode is set to Specified Count, enter the number of times the schedule is to run. Otherwise, leave the default value of '0'.
  7. Simple Trigger Misfire Instruction determines what will happen if the schedule cannot be run. None indicates that it will just run on the next cycle. Trigger Now indicates that the schedule will continue to determine if it can be run immediately after the initial schedule cannot run.

 

Scheduled Jobs

  1. Once the job schedule has been configured, the schedules can be viewed in Admin>System>Jobs Scheduled.
  2. Select the required job and click on the child grid to review the Triggers and Error Logs.