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

[Anonymous]

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.

[bhagyarekha]

Hi,

Try to use teltmssql input,teltmssqlmap,teltmssqloutput

Regards,

rekha

[Anonymous]

Used tELT components. 

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

[TRF]

Hi,

Did you try tMSSQLOutputBulkExec to replace tMSSQLOutput?

[bhagyarekha]

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

---

it will give good result

[Anonymous]

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?

[bhagyarekha]

Hi

The source and destination are on the same server?

Still you are unable to load data?

[Anonymous]

No. They are different servers.

[bhagyarekha]

Hi 

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

[Anonymous]

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