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 must 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 the export item option to export Db connections metadata.

Create Connection 1

Connect Talend to SQL Server Example

The Connect & Create SQL Server Connection option opens the New Talend Database Connection window. Please fill in the Talend Database connection name, the purpose of this connection, and a 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 in to connect.

  • Db Version: Either you can choose Open Source JTDS or DB specific.
  • Login: Use your username to log in to the Server.
  • Password: Enter the password for the above username.
  • Server: Name of the Server instance.
  • Port: For Microsoft SQL, it is 1433
  • Database: Please enter the database name 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; you must 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.

Export Items Window

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 its Data types etc. Please click the Finish button.

Connect Talend to SQL Server 11

You can see all the SQL tables we selected from the below Talend image.

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.

Choose SQL Server Database

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 must fill in all the details. Here, we use the previously established connection.

Provide Host, Username, and Password

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

click the Repository Content 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).

Choose the component and click ok 16

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

Check the components tab

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.