Talend ETL Part 1: SQL Server Database to Excel Sheet

Hello All,

Of many ETL tools available in Market, One of the strong tool is Talend. Difference between other ETL tools and tools like Pentaho, Talend, Clover ETL, Adeptia Integration etc, is that they support NO SQL Cross domains, BIG Data, Hadoop etc.

Other ETL tools like, SSIS, Informatica are now coming with their higher versions, which consists of Hadoop Integration.

Basically We can say, there are two databases types.
1) RDBMS (Example: SQL Server, MySQL, Oracle etc)
2) Non RDBMS (Example: MongoDB, InfiniDB etc)

Talend Supports Non RDBMS databases.

Here I would like to share my hands on experience on Talend and how to use it and explain basic components of Talend.

Approx there are 500 components we can find in Talend.

So lets Kick Start from Basics. First lets try to load Data from Microsoft SQL Server to Excel.

Steps:

Step1: Open Talend Studio.

Step 2: Right click on Job Design and Create a new Job by giving some job name.

Step 3: Give the name of Job and click Finish.

Step 4: To connect to Database, Click on DB Connections and Create New connections.

Step 5: Next, give the New Database Connection Name and then click Next.

Step 6: Select Database Type you want to connect using Drop Down button.

Step 7: I have already created a ODBC Driver of MSSQL so I am using the same. You may can take OLEDB connection also. Give the connection details and Check whether connection got established  and then give finish. 

Step 8: Now Under MetaData, We can see the a DB Connection been created under the name Twitter_Data. Now right Click on DB Connection and click On Retrieve Schema.

Step 9: This window pops out, and  Give Next.

Step 10: Now under the database, Expand it and Select the Schema in which your Table exists and under Schema, Select the Table which is required.

Step 11: 

Step 12: After giving finish, we can see the Schema is Created Under MetaData. Now we can drag and drop this component on the Designer Page.

Step 13: Now drag and Drop the Employee Schema onto the Designer. When we drag and drop it, this following window will pops out. Click tDBInput.

Step 14: 

Step 15:   Now  on Palette on Right hand side, Select Files and Select Output.

Step 16:  Now under Files>Output, Select tFileOutputExcel component and Drag and Drop it on Designer Window.

Step 17: After Drag and Drop, Now right Click on EMPLOYEE Component on Designer Mode and Select Row>Main. This will produce a thread/Line which is used to connect the components. Now drag thread and place it over TFileOutputExcel.

Step 18: 

Step 19: Now to configure tFileOutputExcel, Click on the component once and Under Component Properties below (As Shown), Fill the areas which are highlighted which gives details like Path of the Excel Destination File, header Details etc.

Step 20: Now its all done. Now we are ready to run the package. To run the Job, We can Select Run Tab below and can use Run Button to run full Component under Basic Run.

Step 21:  Else to Debug and watch each row by row, We can select DeBug Run under RUN tab and Can run the package.