Errors when transfering large tables via fastload

More
9 years 3 months ago #9812 by wmair
I am getting a number of errors when trying to copy data from MS-SQL to Postgres 9.1. The strange thing about it is that these errors dont appear for smaller tables, but abort the transfer for all larger tables after about 9900 rows. Errors look like:

Error: 0xC0202009 at Copy Data to Frontend, Portfoliohistory FE [1623]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "PostgreSQL Native Provider" Hresult: 0x80004005 Description: "ERROR: invalid input syntax for integer: "2010-12-22"
CONTEXT: COPY portfoliohistory_pfh, line 257, column id_cho: "2010-12-22"

and

Error: 0xC0202009 at Copy Data to Frontend, portfolio perf risk FE [1548]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "PostgreSQL Native Provider" Hresult: 0x80004005 Description: "ERROR: extra data after last expected column
CONTEXT: COPY portfolio_perf_risk_ppk, line 51: "42|-3|1|1|t|259524666.802000|2|Market Value BoP|2010-12-22|100"

I have checked the source data for delimiters - there are none. I am using 1.4.0.3076 SE

PGNProfiler throws the following error for the extra data error:

Client SQL
PQputCopyData
Error
ERROR: extra data after last expected column
CONTEXT: COPY portfolio_perf_risk_ppk, line 51: "42|-3|1|1|t|259524666.802000|2|Market Value BoP|2010-12-22|100"


UPDATE: i changed my tables and added a single unique identifier instead of having a combined PK of 5 values. The Error has changed to:

Error: 0xC0202009 at Data Flow Task, Position perf risk FE [53]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "PostgreSQL Native Provider" Hresult: 0x80004005 Description: "ERROR: invalid input syntax for integer: "2010-12-22"
CONTEXT: COPY position_perf_risk_ppr, line 3291, column id_cho: "2010-12-22"
".

i.e. fastload messes up the fields and tries to insert a date value into an integer field instead of its correct place in the row

UPDATE II:

Obviously Fastload does not work if Tables contain NULL values i dont assume this is intentional behavior as there is a "keep nulls" parameter to be set?

Please Log in or Create an account to join the conversation.

More
9 years 3 months ago #9813 by Moderator
Could you e-mail DDL for position_perf_risk_ppr table and, if possible, some test rows to support? It looks like a bug.

Thank you!

Please Log in or Create an account to join the conversation.

Time to create page: 0.043 seconds
Powered by Kunena Forum