Loss of rows under sql server inserted from talend

Seven Stars

Loss of rows under sql server inserted from talend

Hi,

I created a job that allows to insert nearly 24 000000 lines in sqlserver. After running I found the 24 000000 line, but bizarrely today I find only nearly 14 000 000 line.

In fact there is a loss of lines and I havn't changed anything !

 

Is there anyone who can help me please?

Thanks in advance. 

Forteen Stars

Re: Loss of rows under sql server inserted from talend

most real reason - wrong forms (length, type, nulls and etc) from source

 

depending from settings, SQL output use batch size for insert, it is much faster (dramatically faster) but at same time - with any error all batch rows will be  rejected

so, check -log files, check source data for compliance with Your output schema

-----------
Seven Stars

Re: Loss of rows under sql server inserted from talend

I checked everything you just said but I suspect it is because of commit because I didn't make the component tMsSqlCommit.

Does it affect the job?

Forteen Stars

Re: Loss of rows under sql server inserted from talend

1) commit affected if You use tMSSQLConnection

2) if commit missed - You will have 0 records, but You have some of them

-----------
Seven Stars

Re: Loss of rows under sql server inserted from talend

During my first run, i ddidin't use neither tMSSQLConnection or tMSSQLCommit.

Forteen Stars

Re: Loss of rows under sql server inserted from talend

if You do not use tMSSQLConnection, You can not use Commit, it managed by tMSSQLOutput (Advanced) and it auto commit for selected number of records, like 10 000 per batch

-----------
Seven Stars

Re: Loss of rows under sql server inserted from talend

Yes that's what I did at the beginning, but since I lost lines I thought maybe that's because of commit :/ 

Because I have no errors at the sql server log file and at tStatCatcher in Talend, so i don't understand the problem Smiley Sad

Moderator

Re: Loss of rows under sql server inserted from talend

Hello,

Is there any data truncation issue printed on console?

You can right click on tMssqlOutput, select Row, and then Reject to see if there is any record information rejected by your sql server.

Note:The reject row can't be used together with the 'die on error' option and 'use batch size' option in advanced setting tab of tMssqlOutput component, uncheck these two option and then you can use the reject row.

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seven Stars

Re: Loss of rows under sql server inserted from talend

I have already solved the problem of data truncation and i made a table for reject row.

Here is a screenshot :

job.PNG

Fifteen Stars TRF
Fifteen Stars

Re: Loss of rows under sql server inserted from talend

Sorry for the question, but how are you sure the input contains 24,000,000 rows?

TRF
Seven Stars

Re: Loss of rows under sql server inserted from talend

When the execution is complete, I made this query (select * from my_tabel) and i got nearly 24 000000 lines.

The next day I launched the same request again in SQLServer and i obtained only nearly 13 000 000 and even when I run another time the job i noticed that the number of rows inserted under sqlserver change randomly ! 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now