From: | Artashes Khachatryan Galust <artashes(dot)khachatryan(at)digitain(dot)com> |
---|---|
To: | "pgsql-odbc(at)postgresql(dot)org" <pgsql-odbc(at)postgresql(dot)org> |
Subject: | Problem with Bulk Insert ODBC driver |
Date: | 2020-04-29 13:45:17 |
Message-ID: | AM7P189MB0600813032E4405E639CB42AF9AD0@AM7P189MB0600.EURP189.PROD.OUTLOOK.COM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
Hi,
I try to sync data from MS SQL to postgres with SSIS. I configure ODBC destination using official driver for postgres.
You can see my driver version in attached driver.png file.
My SSIS package just gets data from a single MS SQL table and transfer it to Postgres. You can find DDL of my postgres table in attached table.sql file.
The problem is that data sync is done row by row, instead of bulk as expected. I understand this by profiling my postgres server file data is in sync process and there was only a single insert like this
INSERT INTO "public"."DailyGgrNgr" ( "Date","PlayerId","GameId","GGR","NGR","BetCount","WinCount","BetAmount","WinAmount") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
When I change the destination to another postgres ODBC driver it works fine, so the problem is not in the package.
According to Microsoft official documentation<https://docs.microsoft.com/en-us/sql/integration-services/data-flow/odbc-destination?view=sql-server-ver15#BKMK_odbcdestination_loadoptions> - "If you select Batch and the provider does not support this method, the ODBC destination automatically switches to the Row-by-row mode." - so it seems like the ODBC driver does not support bulk insert/update, or I don't configure it properly.
My Postgres version is PostgreSQL 12.2.
Please let me know if You need some additional information.
Regards,
Artashes Khachatryan
[Solodev Icon]
Notice: the data sent by this e-mail address (the correspondence) is of informative nature and cannot confer rights and impose duties, give rise to any responsibility for the represented company. The commercial information contained in this correspondence, including price offers, may relate to the represented company, as well as the partner companies.
Attachment | Content-Type | Size |
---|---|---|
driver.png | image/png | 101.9 KB |
table.sql | application/octet-stream | 317 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Gilmour | 2020-05-09 20:21:20 | newer (11+) psqlODBC install on old system |
Previous Message | Artashes Khachatryan Galust | 2020-04-29 13:42:29 |