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.
Now, on the left side, expand the “Database” tab in the component inventory area:
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:
Let’s define connection properties by clicking on the “advanced” button(which you can see on the screenshot above):
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:
So, select the database.. Click “OK”. You will notice that component’s connection string has been updated:
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):
Then, double click on the MS SQL Data Source and move new component a little up to the left:
In the “Name” text box, type in “Source Table”:
Add another SQL Data Source component and call it “Destination Table” this time:
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:
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.
– 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
For the “Destination Table” component, use “Insert” action and “Destination Table”:
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:
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:
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”:
It’ll take a moment, and you’ll see this kind of picture on your screen:
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.