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 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.
Within the second page, please use the drop-down menu to select the required DB Type. Here, we are selecting Microsoft SQL Server.
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.
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.
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.
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.
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).
Next, select the schema (dbo, hr, finance, etc.) and select the required tables. Here, we are selecting a few tables.
This page helps you to check or inspect each table and their Data types etc. Please click the Finish button.
From the below Talend image, you can see all the SQL tables that we selected before.
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.
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).
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.
Once you click the Run button, you can see the data.
