TMW Smartlink Data Exchange Agent (TMWDX)

TMW SmartLink Data Exchange Agent (TMWDX)

User Guide

Version 1.6

TMW Proprietary Information

Revision History

Table of Contents

TMW SmartLink Data Exchange Agent (TMWDX)

User Guide

Version 1.6

TMW Proprietary Information

Revision History

Introduction

Application Installation

Application Overview

Setup

Source Tab

Work Tabs Associated with Stored Procedures

Import Types

Field Names

Lookups

Log Messages

Email Messages

Settings

Scripting

Debugging

Operation

Logging

Appendix A – Custom Date-Time Formats

Appendix B - Expressions

EXPRESSION SYNTAX

USER-DEFINED VALUES

STRING OPERATORS

WILDCARD CHARACTERS

FUNCTIONS

CONVERT

LEN

ISNULL

IIF

TRIM

SUBSTRING

Appendix C – DX Scripting Command Tips and Tricks

REGEX

Link

PARSE

SENDFILE

Introduction

This document describes the setup and operation of the TMW SmartLink Data Exchange Agent (TMWDX). TMWDX is a Microsoft VB .NET based application used to exchange data with external systems and the TMWSuite database.

It uses a script to translate data in delimited or fixed position flat files, or in Microsoft Excel spreadsheets. A typical use is to import orders for transportation services into TMWSuite.

The TMWDX agent is not dependent on the TMWSuite database. It operates with MS SQL 2000 or greater database stored procedures.

TMWDX can replace the TMW Microsoft Excel-based Order Import Toolkit, with the appropriate script.

Application Installation

NOTE: TMWDX requires that the .NET framework 1.1 be installed on the computer on which it will run.

As well, the TMWDX.sql file must be applied through MS Query Analyzer to the TMWSuite database. The TMWDX.sql will be found in the DX folder in the distribution.

Application installation itself is accomplished with a standard Microsoft Windows installation script, Setup1.msi in the DX folder. Double-clicking on this file starts the installation.

When the title page displays, hit “Next.” Clicking “Cancel” will cancel the installation.

If the installation options displayed are satisfactory, click “Next”. Clicking “Cancel” will cancel the installation. Clicking “Back” will go back to the previous step.

Click “Next” to proceed with the installation on the next displayed page. Clicking “Cancel” will cancel the installation. Clicking “Back” will go back to the previous step.

Installation progress is then displayed. Installation takes only a few seconds. If you wish to cancel, click the “Cancel” button on the page.

When the installation is complete, a completion screen will be displayed. Click “Close”.

When the TMW Data Exchange application is run for the first time, the connection screen will be displayed. Once a good connection to the TMWSuite database is confirmed, the application will automatically start.

Application Overview

The figure below shows the general flow for order processing in TMWDX.

TMWDX is a script-driven “robot” that executes stored procedures:

1. Data from an external source is placed in the “Source” tab.
2. The script in the “Setup” tab processes the “Source” data one row at a time.
3. The script moves data to the various tabs that act as holding tanks.
4. At the appropriate point in the script, an “EXEC tabName” command is issued.
5. The data in the current row of the tab referenced in the “EXEC” are used as the parameters for the stored procedure associated with the tab.
6. Successive execution of the various tabs put the data on the database.
   

TMWDX is similar to an Excel workbook. It has tab pages with grids like spreadsheets to hold the script, settings, incoming data, and outgoing data. Under the “Edit” menu item, clicking on “Edit/Edit Setup” shows the script and other settings.

Scripts are typically loaded from base scripts for various import types into SQL tables, and modified to suit the particular application. The following figure shows a sample script.

If “Edit Setup” is not checked, the setup is hidden, as in normal operation, shown in the next figure:

For the most typical imports, after a standard script is installed and modified for any custom needs, standard data exchange stored procedures are associated with the tabs to hold imported data.

After setup, the import can run unattended. There is both an error log table, and an email messaging system included in the import to report on errors and other significant events as they occur.

Setup

The first tab shows as “Status” when not in setup mode. If “Edit Setup” is checked in the Edit menu, the tab shows as “Script”, displaying current processing script. When other items are selected from the “File” menu item, the records associated with that item are displayed in the tab, and the tab name changes accordingly. Setup items from the File menu include:

• Import types,
• Field names to clearly identify the data fields being imported,
• Scripts to run processing and translation,
• Lookups for string translation,
• Log message templates, including those for error messages.
• SMTP Mail message templates.
• Settings, and
• Cross references (xref) (work in process).

Whenever data is changed in the grid in the setup tab, the Update Table button must be clicked to save the settings. This button changes color to red when an update is needed. During the update, the button is yellow. The button is shown circled in red in the figure below:

Source Tab

The “Source” tab contains a data grid displaying the current data to import. The script works against this data.

Work Tabs Associated with Stored Procedures

These tabs are the temporary destination for the data. Each data field copied into a cell in the data grid in a given tab represents a parameter for stored procedure associated with at tab.

When the script invokes an EXEC with a particular tab name, it executes the SQL stored procedure using the parameters contained in current row in that tab. There is any EXEC …ALL command that will execute all the rows.

In the figure below, the drop down box is used to select the stored procedure to be tied to that tab. When a procedure is selected, the tab automatically refreshes with the stored procedure’s parameters are column headers. The application automatically remembers the selection. It is not necessary to click the “Update table” button.

Any of the Work tabs can be renamed through the Edit menu, by selecting the Edit tab name menu item. The tab name is then used as a reference to that destination in the script. These tab names are remembered until changed, and are stored in the Settings table.

Any of the Work tabs can be renamed through the Edit menu, by selecting the Edit tab name menu item. The tab name is then used as a reference to that destination in the script. These tab names are remembered until changed, and are stored in the Settings table.

Type in the new name and click the “GO” button, and the name changes:

Import Types

Import types tie all the parts for each type of import together. Specifying at least one import type is mandatory.

Set up Import Types by selecting the “Edit/Edit Setup” menu item, then selecting the “File/Import Types” item.

When more than one import type has been set up, each can be selected through the drop down box circled in red in the figure below.

Creating an import type consists of selecting “Edit/Edit Setup,” then “File/Import Types.”

A screen like that below is displayed.

You must fill in the parameters for the new import as in the table below.

Field Names

The Fields setup is used to name and define the columns specifying the format of the data coming in. This data will be placed in the Source tab, and the columns there will be labeled with the column names specified.

Set up Fields by selecting the “Edit/Edit Setup” menu item, then selecting the “File/Field Names” item.

Lookups

Lookups substitute one data value for another.

Set up Lookups by selecting the “Edit/Edit Setup” menu item, then selecting the “File/Lookups” item.

Log Messages

The templates in the Log Messages setup are used to translate return codes, successful or failed, into messages. The dx_errormsg field contains a message string with embedded column names enclosed by brackets (“{}”) to specify where values from the tab name in the dx_command field should be inserted in the message.

Email Messages

The dx_messagebody field contains a message string with embedded column names enclosed by brackets (“{}”) to specify where values from the tab name in the dx_command field should be inserted in the message.

Settings

Several items are controlled from the Settings table, including tab names, the specification of user variables and user defined commands. The screen below shows the settings table.

The Application column is always TMWDX. The settingSection column is the import id.

If the settingKeyword is “UserVars”, the settingValue will appear as a column in the UserVars tab once the Update Table button is clicked, and TMWDX is restarted. The setting Default column will set the default value for this variable.

If the settingKeyword is “UserCommands”, the settingValue will become a new command invoked from the script. The setting Default column will set the SQL stored procedure to invoke when this command is encountered in the script. The new command will be effective once the Update Table button is clicked, and TMWDX is restarted.

The entries with a settingKeyword beginning with “Work” and ending with ProcName are the records of the stored procedures, shown in the settingValue column, associated with the respective work tabs. These are maintained normally by selecting a stored procedure from the drop-down list on the tab with which you want the stored procedure linked.

The entries with a settingKeyword beginning with “tpWork” are the names, in the settingValue column, of the tabs set through “Edit/Edit Tab Name” on the menu bar.

There are special settings to influence TMWDX behavior.

The settingKeyword PreImportTask, with a task specified in the setting value field runs that task prior to importing data.

The settingKeyword PostImportTask, with a task specified in the setting value field runs that task after importing data.

The settingKeyword AddArchDTTM, with a setting value of a date time format (see appendix A), will append that date time to the import file name when it moves it to the archive folder, should an archive folder be specified.

For example, with an import file of InFile and a datetime format of _yyyyMMdd_HHmmss_fff, the InFile is archived as InFile_20051005142133_325, if it was archived at 14:21:33.325 on October 5, 2005.

Scripting

Imports are carried out under the control of scripts for each process. These scripts contain simple commands to:

These commands:

• Move data from the “Source” tab page to the other stored procedures tab pages,
• Test the data on any tab page,
• Convert data with expressions,
• Look up substitution for data fields
• Control the flow of script commands,
• Change the current position on each page,
• Execute the stored procedure associated with each tab page.
• Run special functions.

The following figure gives a screen showing a partial script.

The table below contains the use and syntax of each scripting command.