Errors when transfering large tables via fastload

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

Errors when transfering large tables via fastload

Postby wmair » Tue Oct 23, 2012 1:16 am

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?
wmair
 
Posts: 2
Joined: Mon Sep 03, 2012 3:02 am

Re: Errors when transfering large tables via fastload

Postby Moderator » Tue Oct 23, 2012 10:07 pm

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!
User avatar
Moderator
Site Admin
 
Posts: 306
Joined: Wed Oct 29, 2008 11:27 pm


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 1 guest

cron