MS SQL SSIS Dataflow-Task to PostgreSQL Performance

[knuxel]

Hi,

I'm transfering about 60 million rows in a SSIS-Dataflow-Task from a MS SQL 2008 R2 Database to a PostgreSQL 9.1 Database with the PGNP.1 Desktop Edition under Windows 7 64 Bit.
There are only 2 columns in the table (INT and INT) and it's really slow. Only about 10.000 rows per second.
Is there a hint for a better performance?

System: Windows 7 64 Bit (both Databases on the same machine)
Source DB: MS SQL-Server 2008 R2
Destination DB: PostgreSQL 9.1

Thanks for help.

[Moderator]

For fast transfer use connection string parameters: Extended Properties="BULK_METHOD=COPY;BULK_INSERT=2000;..."

Also, I recommend using Server Edition since it supports "FastLoad" (read Developer's Manual for more details). And the SE uses a different "cursor engine" which handles rows significantly faster.

[knuxel]

Thank you for your reply. It's definitely faster now, but with an error in another table:
I have to convert smallmoney to numeric (19, 2). The TOP 1999 rows are inserted successfully, but if I select the TOP 2000 rows, I get an overflow error typ numeric. It works, if I set the "BULK_INSERT=2000;" statement higher than the number of rows in the table (still with the Desktop Version), but it's slow again.

Table (1.7 Mio entries):
INT, tinyint, smallmoney
smallmoney converted to numeric (19, 2)

Error:
Fehler: 0xC0202009 bei Datenflusstask, PostgreSQL-DB 1 1 6 1 [328]: SSIS-Fehlercode 'DTS_E_OLEDBERROR'. OLE DB-Fehler. Fehlercode: 0x80004005.
Ein OLE DB-Datensatz ist verfügbar. Quelle: 'PostgreSQL Native Provider' HRESULT: 0x80004005 Beschreibung: 'FEHLER: Feldüberlauf bei Typ »numeric«
DETAIL: Ein Feld mit Präzision 3, Skala 0 muss beim Runden einen Betrag von weniger als 10^3 ergeben.
CONTEXT: COPY productstats, Zeile 1, Spalte ng: »4864«

Thanks for help.

[knuxel]

I found a workaround: It works with explicit converting tinyint to numeric (3, 0)!
Is there a fast way to transfer the data type smallmoney to PostgreSQL?

[knuxel]

I tried the Server Edition with fast load and in my case it's 25% faster!
Thanks for that!