Creating your first EZETL project
Let’s do something simple first, and, then, we will move on to a more complicated (but, hopefully, to a more rewarding) stuff. For this one, let’s create a project that will be moving data from one table to another in your MS SQL database. Also, for the sake of this exercise, let’s ignore the fact that you can do the same in the SQL Management studio using a relatively simple query.
First, here is what you’ll need:
- A Microsoft SQL Server instance – SQL Express would do just fine
- A database, which I will be calling “EZETLTest”.. but feel free to use any other database name
- A couple of tables so you could move data from one table to another. Let’s call them SourceTable and DestinationTable. You can use the SQL below to create those tables:
CREATE TABLE SourceTable
CREATE TABLE DestinationTable
- Before we go any further, let’s add some data to the SourceTable
INSERT INTO SourceTable (Id, Name) VALUES (1, ‘First’)
INSERT INTO SourceTable (Id, Name) VALUES (2, ‘Second’)
So far so good – we are now done with the preparations, so we can move on to creating an actual EZETL project.
I’m assuming you’ve already installed the software, you have added your license keys. If not, please refer to the EZETL Installation article to see how those steps are done before proceeding to the rest of this post.
Let’s open EZ ETL
Notice that, when you open it, you already have “New Project” open by default. You can give it a different name:
And you can give it a description:
In both cases, just move mouse to one of the properties on the right side and click in the corresponding text box. You will either be able to work directly in that text box, or, as it happened above for the “description”, you’ll be presented with a popup window where you can enter desired details.
Now let’s save the project so we don’t lose our work.
Notice that you can also open an existing project.. you can create a new one.. Or you can save your project into a different file (“save as” option).
Now at this point you might want to ask how/where the data is saved. And the answer is: EZETL projects are saved as “xml” files. Some parameters might be encoded, others might not be encoded. As a rule of thumb, you can assume that sensitive data may show up in those files if you take no precautions.
There are a couple of things you can do:
- Store your projects in a secure location – use file access security to make sure nobody else can get to those files
- Do not put any sensitive information into those files. For example, when creating various connections to CRM, Database, etc, use integrated authentication – that way you won’t have to leave user names/passwords in the project file
- Set “For Distribution” to “True” in the project properties:
This one can be a little tricky, though. It will be up to the specific data component to define properties which cannot be saved in the project file when “for distribution” is set to true. For example, this will prevent MS SQL connection parameters or Dynamics CRM connection parameters from being saved. However, this feature is, likely, more useful for project distribution rather than for protecting the data in general since you would use it to create projects you want to send to somebody else. Just like we do it with sample projects here, for example.
But we’ve really got sidetracked. Remember we just changed the name & description, saved the project.. And we have not done any work with the data yet – that’s what we are going to do next.