Having done conversions for over 13 years, we knew that data conversion is ranked just above toilet cleaning; so if this tool was going to be a success it would have to protect the person performing the conversion from as many of the common pitfalls as possible. If you do everything "right" no one notices. If you "mess up", your name will be widely known, and not in a good way. Conversions come in all shapes and sizes. From "Can you load the 100 names on this spreadsheet?" to "We need to move our ERP data to a new software package." The data conversion software needs to support large and small applications. Often today, there is a need for process automation that involves data import. We decided that the designer should be able to plan for an unmonitored process, a process that requires user interaction, a service, or finally, something that could be called 'stand alone' by another program. This system would have to be visual in nature, yet capable of running when there was no screen to write to. The system should support standards and be able to adapt to new standards when they come out. The system needs to be robust and able to connect to a work flow process system if that is desired. This system should also be able to run with any database.

The solution:

We concluded that a suite of "Objects" whose properties could be set at design time, with properties to control the operation at run time, were best. The suite needed actual objects and not just an engine interpreting a script. Visual objects (like user input objects) would be available, but not recommended for unmonitored processes. The objects could be loaded into a variety of environments to execute, while being designed by a common designer.

Features designed to make the conversion a success:

1.   Wizards.

Wizards allow us to set up complex SQL and data mapping without the misspellings and capitalization problems that hamper most processes. Wizards also speed up the set up for beginners or experienced users of the software.

2.   Debugging.

By turning off individual Objects or the entire branch under an Object, the programmer can hone in on glitches and debug his Package.

3.   Logging.

Logging to screen. By writing our log file to the screen in the designer, the programmer can monitor the progress of his script. Since we dump the SQL statement and the error messages to the screen, the programmer can diagnose the problem and correct his SQL with ease.

4.   Parameters.

We support parameter files so that specific scenarios can be set up and defaults which are dropped into the variables in the script can be preset. This will insure an identical run for testing regression of newer packages.

5.   Documentation.

We provide an auto-documentation wizard which allows the programmer, with a few mouse clicks, to produce HTML documents with an index and all the properties of the objects in a clean readable format. This document makes it easy to compare different versions of a package to see what has changed.

6.   Security.

Consultants and large corporations are often reluctant to use a tool that their clients or users can access easilly. To alleviate this concern, the 2.0 tools support a user name and password for each file that controls the ability to edit, document, or even see the objects and their properties.

7.   SQL Worksheet

A separate program that is a SQL worksheet allows for faster development against complex data environments.

The 1.x tools

In the 1.x version of these tools, the objects all resided in separate files and called each other as needed; this had limitations for both passing data and state information, and version control was sometimes difficult.

The 2.xTools

With the 2.0 tools, all the objects were incorporated into a single tree. Data was now easy to pass between objects of different types, and state information was easier to track.

The 2.0 tools have the additional ability to ask the user questions at runtime. (This is not recommended for a process that is going to run unattended)

Objects vs. Hand Written Code

The object oriented model has many advantages over hand-writing code. With one-off code the programmer will not spend the time optimizing the conversion engine to the degree that we have. With one-off code no one will document the code like our tools do, because we automate it. No one is going to preserve the code the way we do. (You can't lose the source code, because it's what you are running.)

Objects vs. Scripts

The object model has many advantages over the scripted code in most ETL tools. Interpreted scripts are by definition, slower than executed code. Most scripting languages also have a steep learning curve before that language is useful. Additionally, most ETL tools cost a bundle.

How we came up with the name "Package"

When naming our creation, we didn't like "scripts" because there is so much more capability in the object model than in a text-based scripting engine, and we didn't want to call it a "program" because we had more capabilities than that too. We called this file a package because we can wrap up an entire conversion: user input, additional files, executables, and the actual objects themselves into one neat "Package" and thus, the "Packager" was born.

How we came up with the User Interface (UI).

The user interface was a direct result of observing web sites and explorer in Windows^TM. Many web sites have a navigational window on the left side of the screen, and as you select options on that side, the other side of the screen changes. Explorer in windows works the same way; as you select Directories, the files on the right side of the window change. We decided that we wanted to have an interface with a navigational tree on the left side and the properties on the right, much like these sites. For working with COBOL and CSV files, we wanted to have a wider area. We decided that the bottom of the screen should host an optional pane to allow support for the visual editor for flat files. This section of the screen is only available when an object that is a part of the parser is selected on the tree. These areas of the screen can be rearranged by the end user to be in other positions.

What is the execution order?

All the objects are executed from left to right, top to bottom. A parent object is executed before its children and with the exception of the Iterate, Branch, and Transaction objects, the parent has no interaction with the children's code. Execution order is also affected by these objects:

• Iterate can call its children over and over.
• A Branch object may call one or more, or none of its children.
• Transaction objects may call all of their children inside of a single database transaction.

Why online?

Some of our competitor's software sells for as much as $250,000.00 per license. By placing the software online, we eliminate the cost of distribution and packaging. Our updates are automatic, and we can charge by the record for small projects. We can even give away some record operations as a free trial. Boxed software just can't do that.