HELLO WORLD! Moving the data..

 

Now that we have the project saved, let’s see how to make our data move from one MS SQL table to another.

First of all, remember that “For Distribution” option? Make sure it’s set to false. You don’t want to lose your connection settings while working on this project.

clip_image001

Now, on the left side, expand the “Database” tab in the component inventory area:

clip_image002

And, then, double click on the MS SQL Connection component in the component inventory. You should see a MS SQL Connection component added to the project designer area:

clip_image004

Let’s define connection properties by clicking on the “advanced” button(which you can see on the screenshot above):

clip_image006

You may have to use different SQL server settings – it depends on how it’s all set up in your environment. Still, once you have provided the server name (and, optionally, user name/password), you can click that refresh button and, assuming all the connection details are correct, you will need to choose from the list of databases:

clip_image008

So, select the database.. Click “OK”. You will notice that component’s connection string has been updated:

clip_image010

That’s it, we have our SQL connection configured.

Now we need to add two data sources (one for the source table, and one for the destination table).

You can do it in just the same way you did it with the dataconnection.. First, re-arrange MS SQL Connection on the designer surface so it’s in the left top corner (use mouse for that – just drag the component across designer surface):

clip_image012

Then, double click on the MS SQL Data Source and move new component a little up to the left:

clip_image014

In the “Name” text box, type in “Source Table”:

clip_image016

Add another SQL Data Source component and call it “Destination Table” this time:

clip_image018

Now, let’s connect all those components on the surface. There are connection points on the left, top, right, and bottom sides of each component. If you click on one of the connection points, hold left mouse button down and start moving the mouse, you’ll see a connection pointer.. Keep moving the mouse until you reach your target components, then let it go. Let’s set up two connections: from MS SQL Connection to Source Tabe and from Source Table to Destination Table:

clip_image019

The reason we want to have a connection between MS SQL Connection and the Source Table is that we will control project execution sequence that way. Even though no actual data will be passed from MS SQL Connection component to the Source Table.

Now let’s configure the Source Table component.

Do this:

– Select “Source Table” component. You can select any component on the designer surface and have a look at its properties by clicking on that component.

– Leave “Action” as “Select”

– Choose MS SQL Connection from the “Connection” dropdown

– Once you’ve selected the connection, choose “Source Table” for the “Table” property

clip_image021

For the “Destination Table” component, use “Insert” action and “Destination Table”:

clip_image023

We are almost there – just need to configure field mappings from the source to the destination.

While “Destination Component” is still selected, click “Advanced” button in the “properties” area:

clip_image025

This is where you can configure mappings. You can see how Id and Name fields are available at the destination.. And you just need to choose the source fields which will be used as a source:

clip_image027

Click OK. The project is ready – you can start it and see how your source table data goes to the destination table.

Before you do, save the project just in case (File->Save)

Then, from the Project Menu, select “Start”:

clip_image028

It’ll take a moment, and you’ll see this kind of picture on your screen:

clip_image030

As you can see, exactly 2 records have been moved from the Source Table to the Destination Table as a result. You might want to verify it in the SQL Management Studio just in case..

There is also one magical record that was passed from the SQL Connection to the Source Table. We don’t need to worry about that one, though, it we wanted to capture it, we could. There is some information about the connection in that record, but it’s of no interest for now.

And that’s it – our first “Hello World” project is up and running. It might not be of much practical use, but it shows how EZETL works and how easy it is to create ETL packages in this tool.

Posted in EZETL MANUAL and tagged .

Leave a Reply

Your email address will not be published. Required fields are marked *