The first thing you need to do once you have a Talend Project created is define your connections that data will be extracted from or where it will be written to.
Microsoft SQL Server is a good example of a database that many users will need to work with in ETL, so we will cover how to add that to your project in Talend.
1. Create the Connection in Talend Repository
In your open project, look for the Repository. This is where you will find many different objects available within your Talend project. Specifically for adding a connection, we are going to expand the Metadata category within the repository. Then you will need to right-click on “Db Connections” and choose “Create Connection”
2. Enter MS SQL Connection Name
You will then be prompted with a dialog box that will walk you through creating your connection. In the first box which is step 1/2, enter at least a name for your connection and then click Next.
3. Enter MS SQL Connection Details
Next, enter the details needed to define your connection. Make sure to use SQL server authetication by entering a Login and Password. Currently, Windows Authentication with SQL Server 2008 is not consistent. After you have entered the details click “Finish”
You will now see your connection under Metadata in the Talend Repository
The first step in creating an ETL project in Talend Open Studio for Data Integration is to create the actual project definition. The project serves as a container for resources, including database connections, job scripts and contexts. When the application is launched, it will use a default folder in the installation directory named workspace. When a project is created, the project exists in a sub-directory in the workspace directory.
See the screenshot from Talend Open Studio for Data Integration 5.1:
Notice we have typed in a name of “OurFirstProject” in the Create a New Project field. When we click the Create… button we get a dialog box that comes up and allows us to add a Project Description. Clicking Finish then saves our project and gives us a new view in Open Studio as seen below.
At this point we can highlight the project we want and click Open, or we can use any of the other features. When you arrive at this screen as the opening screen, simple click Create… to start a new project.
There is a bug in Talend Open Studio for Data Integration version 5.1.1 when adding a database connection. It has to do with the jdts component used for Microsoft SQL Server connections. When adding a new connection you may get one of the following errors:
The above error usually appears the first time you hit the button to “Check” the connection. The second and subsequent attempts will produce a different error message as follows:
This is a bug in Talend Open Studio for Data Integration that has a fix already implemented in the future 5.1.2 version.