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

10 REPLIES
Seven 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

-----------
Four 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?

Seven 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

-----------
Four Stars

Re: Loss of rows under sql server inserted from talend

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

Seven 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

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

Four 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

Seven Stars TRF
Seven 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
Four 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 !