topic [resolved] tMSSqlOutputBulkExec conversion error for int, always in line 517641 in Talend Studio

[resolved] tMSSqlOutputBulkExec conversion error for int, always in line 517641

Anonymous — Tue, 07 Oct 2014 17:02:09 GMT

Hello,
I've been trying to make a bulk insertion in a SQL Server table, but failing because no matter what I do, the result is always the same: java.sql.SQLException: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 17641, column 13
The original field is a nullable int (in MySQL) and I'm inserting a nullable int (in SQL Server). The biggest value it has is 1428.
Until now I've tried the following:

Change the Code page ("OEM", "RAW", "ACP"), the Data File Type ("char", "native") and the Encoding ("UTF-8", "unicode", "ISO-8859-15") of the tMSSqlOutputBulkExec component
Inserting the same int fot all the registers. Tried 1 and new Integer("1")
Splitting the total of insertions in parts of 500.000 registers. The total is almost 8 million, so I thought MAYBE it was a matter of memory, but it failed again in the second batch in the line 17641. Only this time, to effectively split the table I order it before splitting it, so the register in that line was different, and the value in the problematic column was different. The strange this is that a lot of registers before said line have the same value in that specific column, but the error always presents ifself in THAT line.

The 517641 line in the mssql_data.txt file looks like this:
;2;89909;509410018692;15;04-05-2011;4;;4;;4;; 655;703;4
And with the third attempt in the list, the 17641 line in the mssql_data.txt looks like this:
;2;89510;515118016329;15;04-05-2011;89;;89;;89;; 684;660;89
Additionally, when I restrict the insertion only to the registers with the problem, so the bulk insert is reduced to about 50 lines, it inserts without any problem at all. And I have executed a very similar job in the past, with successful results, from the very same origin table but with a different table as destination for the data.
Nothing seems to work, and I've started to have nightmares with that 517641 number. Am I doing something wrong? Has anyone encounter a problem like this before? What's with that line number?
Thanks in advance for any help or guidance you can provide.

Re: [resolved] tMSSqlOutputBulkExec conversion error for int, always in line 517641

Anonymous — Wed, 08 Oct 2014 13:12:43 GMT

hi,
perhaps try first to use the bulk load from SQL command ; could be something like this

BULK
INSERT db.yourtable
FROM 'C:/yourFiletoInsert.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)
GO

And see if you got same errors.
regards
laurent

Re: [resolved] tMSSqlOutputBulkExec conversion error for int, always in line 517641

Anonymous — Wed, 08 Oct 2014 16:51:38 GMT

Thank you Laurent for your answer.
Since my post I have been searching for non printable characters in the file (because I read in some forums that BOM, for example, can cause the kind of error I'm facing) without any luck.
I tried what you suggested, using this script:

BULK
INSERT DB.MyTable
FROM 'C:\Users\Public\Documents\mssql_data.txt'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
GO

The firs time I tried with just '\n' as row terminator, because that's what I had configured in the tMSSqlOutputBulkExec component in my job, but I got:

Msg 4866, Level 16, State 1, Line 2
The bulk load failed. The column is too long in the data file for row 1, column 15. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

So I changed it for '\r\n', but got the same result.
The last column is a not nullable int, so it madeno sense to say it's TOO LONG, because the firs line of the file looked like this:

;2;698471;113186005032;1;01-10-2011;33;;33;;33;;116;1271;33

Then I changed it for '-FINALFINAL-', and again tried the manual bulk insert and got this:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 517641, column 13 (field).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 689068, column 13 (field).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 715321, column 12 (field).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 715322, column 12 (field).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 715323, column 12 (field).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 715331, column 12 (field).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 721779, column 12 (anotherField).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 724515, column 12 (anotherField).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 727204, column 12 (anotherField).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 784293, column 12 (anotherField).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 784444, column 12 (anotherField).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

When Talend generates the file and tries to bulk insert it I keep getting the same error in the same line, and the error seems to be the same when I try to do the insertion manually. I don't know if it affects that I'm using SQL Server 2012.

Re: [resolved] tMSSqlOutputBulkExec conversion error for int, always in line 517641

Anonymous — Thu, 09 Oct 2014 09:32:44 GMT

it seems that you have problem of structure and data type ..

type mismatch or invalid character
truncation

Profile your data sources to find none-compliant lines.
you have to correct data & structures if necessary.

Re: [resolved] tMSSqlOutputBulkExec conversion error for int, always in line 517641

Anonymous — Mon, 13 Oct 2014 14:29:38 GMT

Thank you for your answer.
Never mind the truncation, is just an observations field that is too long, and that I can easily resolve.
The type mismatch or invalid character was the real problem, because, as I mentioned before, it was just an int, and in the 517641 line there were no strange characters that made the bulk insert attempts fail. BUT, I went again to the file to check this new ordered 571641 line, and found that in the previous column there's a ";" within the text. ";" was my field terminator.
So I change it to "]" and it worked! Silly me, with this mistake taking hours of my time, but I still think is super strange that I wasn't able to see the line with the actual problem, until I include an ordering in the query that brings the data from the source table.
Marking this as solved, thank you very much for your attention, Laurent.

Re: [resolved] tMSSqlOutputBulkExec conversion error for int, always in line 517641

Anonymous — Tue, 14 Oct 2014 10:00:21 GMT

glad that you have solved your problem.
for 57164 and previous line, perhaps it's because , the count start from zero ...
regards
laurent

Re: [resolved] tMSSqlOutputBulkExec conversion error for int, always in line 517641

Anonymous — Tue, 14 Oct 2014 20:19:02 GMT

No... I checked the lines around that infamous 517641, but nothing seemed out of place.
Perhaps the bulk insertion orders before it inserts?