topic Re: Data loading from MS SQL Server DB to other MS SQL Server DB is very low compared to SSIS in Talend Studio

Data loading from MS SQL Server DB to other MS SQL Server DB is very low compared to SSIS

Anonymous — Thu, 18 May 2017 06:13:52 GMT

HI All,

My requirement is to synchronize the data between two different MS SQL Servers. My source table has 3.8Million rows.

When I tried to load to data from Source to Target below are my observations with Talend Vs SSIS

1. With Primary Key on Target Table: SSIS has taken ~6hrs, Talend has taken ~4.5hrs

2. Without primary Key on Target Table: SSIS has taken 59Sec only, Talend has taken ~1hr (Max of 350Rows/Sec) and still running

Below are my questions:

A) Why Talend Data load is very slow compared to SSIS even though it is big amount of data?

B) Do we have any components like FastLoad/BulkInsertLoad for SQL Server in Talend?

Note: I am inserting data, there are no updated as it is first time loading. I did not have any blockings on my SQL Servers. I have used tMSSQLInput, tMSSQLOutput components in Talend job design.

Re: Data loading from MS SQL Server DB to other MS SQL Server DB is very low compared to SSIS

bhagyarekha — Thu, 18 May 2017 06:29:33 GMT

Hi,

Try to use teltmssql input,teltmssqlmap,teltmssqloutput

Regards,

rekha

Re: Data loading from MS SQL Server DB to other MS SQL Server DB is very low compared to SSIS

Anonymous — Thu, 18 May 2017 07:26:45 GMT

Used tELT components.

But no data is loaded in output table. See screen shots..

Re: Data loading from MS SQL Server DB to other MS SQL Server DB is very low compared to SSIS

TRF — Thu, 18 May 2017 07:48:00 GMT

Hi,

Did you try tMSSQLOutputBulkExec to replace tMSSQLOutput?

Re: Data loading from MS SQL Server DB to other MS SQL Server DB is very low compared to SSIS

bhagyarekha — Thu, 18 May 2017 08:14:32 GMT

it will work .see proper mapping and query is correct or not and settings

@Dhanraj wrote:

Used tELT components.

But no data is loaded in output table. See screen shots..

it will give good result

Re: Data loading from MS SQL Server DB to other MS SQL Server DB is very low compared to SSIS

Anonymous — Tue, 23 May 2017 07:03:45 GMT

For ELTMSSQLInput i have given my Source MS SQL server Database connection.
For ELTMSSQLMap I have given Target MS SQL Server Database Conncetion
For ELTMSSQLOutput I have given Target MS SQL Server Database Conncetion
Also the query which was shown in Execution window of above screenshot is proper.

Is there any correction needed in above connection mapping?

Re: Data loading from MS SQL Server DB to other MS SQL Server DB is very low compared to SSIS

bhagyarekha — Tue, 23 May 2017 07:46:51 GMT

The source and destination are on the same server?

Still you are unable to load data?

Re: Data loading from MS SQL Server DB to other MS SQL Server DB is very low compared to SSIS

Anonymous — Tue, 23 May 2017 07:49:12 GMT

No. They are different servers.

Re: Data loading from MS SQL Server DB to other MS SQL Server DB is very low compared to SSIS

bhagyarekha — Tue, 23 May 2017 08:37:58 GMT

As of my knowledge we need to use one server only for these componets elt i hope.am not sure

Re: Data loading from MS SQL Server DB to other MS SQL Server DB is very low compared to SSIS

Anonymous — Tue, 23 May 2017 10:13:01 GMT

After multiple tries, I have observed that, ELT Components will be used to load the bulk amount of data within same SQL Servers and same database.

Even if we have multiple metadata connections in Repository on same database, it is not working.

As my requirement is to load the data between two different servers ELT approach will not work

Re: Data loading from MS SQL Server DB to other MS SQL Server DB is very low compared to SSIS

Anonymous — Tue, 23 May 2017 10:17:16 GMT

Hi,

I have also tMSSQLOutputBulkExec components. But it requires Remote File path where the data from my source table(server) was already loaded.
I tried to load the data into file first and then used tMSSQLSQLBulkEXEC component to load the data. But it throwing error.

Please suggest me on this approach if i am going wrong

Re: Data loading from MS SQL Server DB to other MS SQL Server DB is very low compared to SSIS

Anonymous — Tue, 23 May 2017 10:35:49 GMT

HI,

Can you please try to keep the file on a different drive?

Because in Windows the files at Desktop not accessible by some of the programs

The error suggests me that.

Cheers!

Gatha

Re: Data loading from MS SQL Server DB to other MS SQL Server DB is very low compared to SSIS

Anonymous — Thu, 17 Jan 2019 13:55:35 GMT

Hi,

Why it takes around 1 hour to load data from one db table to other db table in Azure? Also there is need to change the batch size and JVM argument size for allocating 4 GB space to talend job to execute for 7 million data. While SSIS does the same in just 15 minutes. Do anyone have solution for this? I am using Talend Open Studio V7.0.1

Thanks,

Dhara