Dear all,
In Talend 5.3, I have designed a simple job to load forex quotes into a SQL Server database.
The data comes from a csv file and one row has the following structure:
2015.09.01,00:00,8.268400,8.268900,8.267900,8.268100,0
which maps to date, time, open, high, low, close, volume
All data is loaded into the database as VARCHAR.
Job design is straightforward : tFilelist -> tFileInputDelimited -> tMap -> tMSSqlOutput
I have 2 files in the tFilelist directory: one for August and one for September.
At runtime, August file contents get inserted without errors into database. But all rows of the September file got rejected due to Data Truncation.
After a table truncate, and removing August file, the September file got loaded successfully ( +/- 32000 rows). But if I try loading the August file afterwards, I received this "Data truncation" message.
I stripped the file so as to load one row and it gets rejected at tMSSqlOutput component. Even weirder, when successfully loading the first file, an attempt the load the same set a second time fails with "data truncation".
So it looks like something prevents from loading into the database once a set of data has already been inserted. But the error message "Data truncation" is strange.
When diagnosing using a Rejects and tLogRow, I don't see any large value not fitting into the columns. After all, the data has been loaded once before !
Examining the generated code the following jdbc piece generates the error
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:504)
at
insertedCount_tMSSqlOutput_2 = insertedCount_tMSSqlOutput_2
+ pstmt_tMSSqlOutput_2.executeUpdate();
tMSSqlOutput settings are batch size = 10000, commit every 10000, additional jdbc parameters= "useUnicode=true&characterEncoding=UTF-8". But changing one of these values has no functional impact.
So what's wrong ? Should I use another database driver ?
Any ideas ?
Thanks and regards,
Lalaina
