Six Stars

Extract from FTP server, Transform, and Load into Azure SQL database

Hi there,

 

Here is what we are basically looking for :

 

1. Import CSV file from FTP or any other server (might be from azure itself)
2. Perform some data quality / preparation operations like cleansing, matching, visualising, etc.
3. Export transformation result directly to Microsoft Azure database table.
 
Is is possible with any of the available Talend tools, if so which one is that ?
 
Thanks.
  • Big Data
  • Data Integration
Tags (2)
1 ACCEPTED SOLUTION

Accepted Solutions
Nine Stars

Re: Extract from FTP server, Transform, and Load into Azure SQL database

Talend has a host of Internet tools which allow you to retrieve files from remote servers. The database components let you extract from or write to a database. Once retrieved, data can be transformed using dozens of components, some specific and some general.

Retrieval and storage:
tFTPGet
tAzureStorageConnection
tAzureStorageGet
tAzureStoragePut
tAzureStorageInputTable
tAzureStorageOutputTable

Transformation:
tMap
tUniqRow
tAggregateRow
tSortRow
tFilterColumns
tFilterRow
tReplace
...

7 REPLIES
Nine Stars

Re: Extract from FTP server, Transform, and Load into Azure SQL database

Talend has a host of Internet tools which allow you to retrieve files from remote servers. The database components let you extract from or write to a database. Once retrieved, data can be transformed using dozens of components, some specific and some general.

Retrieval and storage:
tFTPGet
tAzureStorageConnection
tAzureStorageGet
tAzureStoragePut
tAzureStorageInputTable
tAzureStorageOutputTable

Transformation:
tMap
tUniqRow
tAggregateRow
tSortRow
tFilterColumns
tFilterRow
tReplace
...

Six Stars

Re: Extract from FTP server, Transform, and Load into Azure SQL database

Thanks for your reply @cterenzi.

The list of components given by you seem to be helpful. But my main concern is regarding tool. Till now I've just used Talend Data Preparation tool and found there are certain limitations which can not fit for our requirements.

Which tool should I go for, wherein I can use these components :

1. Talend Data Integration, or

2. Data Data Quality, or

3. Other tool

Nine Stars

Re: Extract from FTP server, Transform, and Load into Azure SQL database

I'm only familiar with Open Studio for Data Integration. It can automate a wide variety of tasks, and can handle data validation, matching and cleansing. It's not really a visualization tool, though.

Maybe it's possible to integrate DI with the Data Quality tool? I would ask @xdshi on questions about the product line.

Four Stars

Re: Extract from FTP server, Transform, and Load into Azure SQL database

Talend DI will handle all these tasks/components easily. But confirmation from @xdshi will help better.


Regards,
Mohit
Six Stars

Re: Extract from FTP server, Transform, and Load into Azure SQL database

Thanks Smiley Happy
Waiting for confirmation from @xdshi
Moderator

Re: Extract from FTP server, Transform, and Load into Azure SQL database

Hello,

From your job requirement description, you need to use both DI(extract data from FTP, tranform and load) and DQ(cleansing, matching, visualising) bundles.

 

Which talend product are you using? Talend subscription solution for data prep?

Best regards

Sabrina

Six Stars

Re: Extract from FTP server, Transform, and Load into Azure SQL database

Hello,

I'm done testing our requirements with Talend Data Preparation free desktop. I found its helpful in term of data cleansing, matching, etc., but has certain limitations like row limit = 30K only, can't sort dataset, etc. So didn't give a try to subscription solution of data prep.

Thanks.