We're noticing some odd behavior with our PGNP 1.4 Server Edition driver (not the trial version). Using SQL Server Integration Services 2008 R2, we're attempting to extract all 3.3 millions rows from a Postgres database (version 9.2.4) table and store them into a SQL Server 2008 R2 table. The Postgres source table has 38 columns of various data types (e.g. numerics with varying precisions, character varying, date, timestamps of various precisions, etc...).
In some cases, rows fail to be provided by the Postgres OLE DB Source component:
- The source table has three columns that make up its primary key, which are all numeric. All 3.3 million rows are returned as expected by Postgres when selecting only these columns.
- Only 1.2 million rows are returned when selecting all columns on the table. We expect all 3.3 millions to be returned by the source.
- Only 1.8 million rows are returned when selecting all columns except character columns. We expect all 3.3 millions to be returned by the source.
- Only 1.9 millions rows are returned whens electing all columns except character columns and numerics with precision explicitly specified. We expect all 3.3 millions to be returned by the source.
No errors of any kind are logged in SSIS. The ETL completes "successfully" each time. No network interruptions occur. The source table is static and unchanging during the ETL process. The Data Flow Task is simply an OLE DB Source component piping data directly to an OLE DB Destination component. We are not using a trial version of the trial, which I understand has row limitations.
Also, this issue seems to impact only the largest tables on the source system. Tables holding more than 500 MB seem to have the problem. The table size is determined using the pg_relation_size() function.
Any help or points in the right direction will be much appreciated.