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.

Connect Talend to SQL Server 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.

Connect Talend to SQL Server 2

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

Connect Talend to SQL Server 3

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

  • Db Version: Either you can choose Open Source JTDS or DB specific.
  • Login: Username to log in to the SQL 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.

Connect Talend to SQL Server 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.

Connect Talend to SQL Server 6

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.

Connect Talend to SQL Server 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).

Connect Talend to SQL Server 9

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

Connect Talend to SQL Server 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.

Connect Talend to SQL Server 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.

Connect Talend to SQL Server 13

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.

Connect Talend to SQL Server 14

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.

Connect Talend to SQL Server 17

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.

Connect Talend to SQL Server 19