MS SQL SSIS Dataflow-Task to PostgreSQL Performance

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

MS SQL SSIS Dataflow-Task to PostgreSQL Performance

Postby knuxel » Thu Oct 11, 2012 7:31 am

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.
knuxel
 
Posts: 6
Joined: Wed Oct 10, 2012 6:34 am

Re: MS SQL SSIS Dataflow-Task to PostgreSQL Performance

Postby Moderator » Sun Oct 14, 2012 2:06 pm

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

Re: MS SQL SSIS Dataflow-Task to PostgreSQL Performance

Postby knuxel » Tue Oct 16, 2012 3:25 am

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
 
Posts: 6
Joined: Wed Oct 10, 2012 6:34 am

Re: MS SQL SSIS Dataflow-Task to PostgreSQL Performance

Postby knuxel » Tue Oct 16, 2012 6:43 am

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
 
Posts: 6
Joined: Wed Oct 10, 2012 6:34 am

Re: MS SQL SSIS Dataflow-Task to PostgreSQL Performance

Postby knuxel » Wed Oct 17, 2012 1:06 am

I tried the Server Edition with fast load and in my case it's 25% faster! ;)
Thanks for that!
knuxel
 
Posts: 6
Joined: Wed Oct 10, 2012 6:34 am


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

Who is online

Users browsing this forum: No registered users and 6 guests

cron