EZ ETL Project Designer

You can use project designer to create ETL projects. Once a project has been created, you can run it either in the project designer or your can use windows task scheduler and EZ ETL Console application to set up a schedule when the project will run automatically.

Here is what you can do in the project designer:

1. Add a new component to the project

You can add a new component from the toolbox on the left side. All components are organized into groups. Once you have expanded a group, you can double click on the component to add it to the project.

You can organize components into process tasks, you can put them directly into the project, or you can mix those two approaches. There is no requirement to create process tasks to organize project components.

2. Create a connection between different components

You can connect any component to any other component. To do that, just push left mouse button on any of the 4 connection points (left, top, right, bottom ellipses) on the source component and drag new connection to another component.

Connections serve two related purposes.

First of all, all data in your project will flow through those connections. Source components will be putting data records on the outgoing connections, and destinations components will be reading data records from the incoming connections. The same component can be both a source component and a destination component.

Connections also define the sequence of components execution. Sometimes, you will have no records to pass over the connection, but you will still want source component to finish its processing before destination components starts working. It's almost the same as having a record passed over the connection except that there is no real data record this time.

3. Configure component properties

If you select a component on the project designer surface (just click on that component), you will be able to update component properties on the right side. Exactly what properties will be displayed there depends on the type of component you have selected.

You can either update properties directly by assigning some specific values to them, or you can use parameterized syntax where it's allowed to enter text for the component value. Parameters are created dynamically as soon as you prefix your parameter name with "@" sign and put the result in one of the component properties. We will discuss parameters in more details later.

You will also notice "Advanced" button which, if you click it, will preset a popup dialog. Again, what exactly will be preseted there depends on the type of component, and, sometimes, on the selected action and other component properties. However, if there are any parameter or field mappings you need to do for the component, that's also done through the "advanced" button.

4. Configure advanced properties & data mapping rules

If you click on the "Advanced" button in the properties area, a dialog window will pop up. Depending on the selected component, and, also, depending on the selected action (select/update/insert/delete/upsert/execute), there might be different tabs in that windows. Here is an example of data fields mapping tab which will allow you to map incoming field to the destination fields.

And here is how the same advanced dialog will look for a SQL connection component

5. Running the project

Whenever you choose to run your project, just select Project->Start from the project designer menu. The project will start, and, once it's finished, you will be presented with the results. If everything runs smoothly, you won't need to troubleshoot.

However, you may also have to find out what went wrong. If a component fails, it will be highlighted in red. Also, each component (failed or not) will have execution log associated with the component. It will be displayed in the "properties" area, you'll just need to choose "execution log" tab there.

6. Save/Load project files, copy selections, etc

From the project designer menu and/or using keyboard shortcuts, you can do a few other things:
  • Load a project
  • Save a project
  • Create a new project
  • Start project execution
  • Reset all project nodes after the project has been executed (this may be handy if you prefer not to see components in highlighted colors)
  • You can also use Ctrl+C/Ctrl+X/Control+V shortcuts to copy/cut/paste components. Also, if you hold Ctrl button down and click on a number of components, you will be adding/removing them to/from the selection