Connect Talend to SQL Server

In this Talend section, let me show you the steps to connect Talend to SQL Server with an example.

In order to connect Talend to SQL Server, you have to create metadata under the Db Connections folder. And to do so, right-click on the Db Connections and select Create a connection from the context menu. Use export item option to export Db connections metadata.

Create Connection 1

Connect Talend to SQL Server Example

Clicking on the Create connection option opens the following New Database Connection window. Please fill the Talend Database connection name, the purpose of this connection, and the brief description of the connection.

Database Connection 2

Within the second page, please use the drop-down menu to select the required DB Type. Here, we are selecting Microsoft SQL Server.

Select Database Type 3

Selecting the SQL Server will show you the following properties and you have to fill them to connect.

  • Db Version: Either you can choose Open Source JTDS or DB specific.
  • Login: Username to log in to the Server.
  • Password: Enter the password for the above username.
  • Server: Name fo the Server instance.
  • Port: For Microsoft SQL, it is 1433
  • Database: Please enter the name of the database that you want to use in this connection.

Before clicking the finish button, please Test the connection by clicking the Test connection button. Next, click the Finish button.

Database Connection Details 4

From the below Talend screenshot, you can see the newly created SQL connection. It is just a connection, and you have to retrieve the Tables and Views from the database.

Connect Talend to SQL Server 5

Right-click on the connection will open the context menu. Clicking on the Export Items opens the following window. Please use this window to export the Talend Jobs, Models, and schemas.

Let me read the tables in the Database. For this, right-click on the connection and select the Retrieve Schema.

Connect Talend to SQL Server 7

The following window will open. Here, you can select the types by check-marking the Table, View, and Synonyms. Please click on the Next button.

Select Tables and Views 8

Use this window to select the schema(s). Please use the Name Filter textbox to search for the table or view name. Or please expand the SQL Tutorial (Database name).

Select Schema 10

Next, select the schema (dbo, hr, finance, etc.) and select the required tables. Here, we are selecting a few tables.

Choose DB Tables 10

This page helps you to check or inspect each table and their Data types etc. Please click the Finish button.

Connect Talend to SQL Server 11

From the below Talend image, you can see all the SQL tables that we selected before.

View the DB Tables 12

Read Database Table in Talend (tDBInput)

Please drag and drop the Talend tDBInput field and select the Microsoft SQL Server in the Components tab. Or use the DB specific field.

Now you can see all the options that you have to fill. If you haven’t already created a connection in the Metadata or repository, you have to fill all the details. Here, we use the previously established connection.

Please change the property Type from Built-In to Repository, and use the Browse button to select the connection.

Connect Talend to SQL Server 15

The other option is to drag and drop the required table from Metadata to Job design. Here, we are pulling the Employ table, and you can see the Components window. Please select the DBInput(Microsoft SQL Server).

Connect Talend to SQL Server 16

Both the above-specified option will display the following window. I mean, they fill all the required fields to connect with the Database.

Next, we used tLogRow with Table Mode to show the data inside the Employ table.

Connect Talend to SQL Server 18

Once you click the Run button, you can see the data.