topic Loss of rows under sql server inserted from talend in Talend Studio

Loss of rows under sql server inserted from talend

INESBK — Wed, 17 May 2017 08:52:22 GMT

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.

Re: Loss of rows under sql server inserted from talend

vapukov — Wed, 17 May 2017 12:06:17 GMT

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

Re: Loss of rows under sql server inserted from talend

INESBK — Wed, 17 May 2017 13:31:58 GMT

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?

Re: Loss of rows under sql server inserted from talend

vapukov — Wed, 17 May 2017 14:57:10 GMT

1) commit affected if You use tMSSQLConnection

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

Re: Loss of rows under sql server inserted from talend

INESBK — Wed, 17 May 2017 15:03:36 GMT

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

Re: Loss of rows under sql server inserted from talend

vapukov — Wed, 17 May 2017 15:15:20 GMT

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

Re: Loss of rows under sql server inserted from talend

INESBK — Wed, 17 May 2017 15:20:58 GMT

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

Re: Loss of rows under sql server inserted from talend

Anonymous — Thu, 18 May 2017 09:28:11 GMT

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

Re: Loss of rows under sql server inserted from talend

INESBK — Thu, 18 May 2017 09:51:14 GMT

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

Here is a screenshot :

Re: Loss of rows under sql server inserted from talend

TRF — Thu, 18 May 2017 09:58:20 GMT

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

Re: Loss of rows under sql server inserted from talend

INESBK — Thu, 18 May 2017 10:23:53 GMT

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 !