Four Stars

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

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.

 

 

  • Data Integration
Tags (1)
11 REPLIES
Six Stars

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

Hi,

Try to use teltmssql input,teltmssqlmap,teltmssqloutput

 

Regards,

rekha

Four Stars

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

Used tELT components. 

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

 

Capture.PNG

Nine Stars TRF
Nine Stars

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

Hi,

Did you try tMSSQLOutputBulkExec to replace tMSSQLOutput?

TRF
Six Stars

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

Hi

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.. 

 

Capture.PNG


it will give good result

Tags (1)
Four Stars

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

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?

Six Stars

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

Hi

The source and destination are on the same server?

Still you are unable to load data?

Four Stars

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

No. They are different servers.
Six Stars

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

Hi 

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

Four Stars

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

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

Four Stars

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

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.

 

Talend.PNG

 

Please suggest me on this approach if i am going wrong

Six Stars

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

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