I try to sync data from MS SQL to postgres with SSIS. I configure ODBC destination using psqlodbc_12_01 driver downloaded from here
My SSIS package has a single data flow task which just gets data from a single MS SQL table and transfer it to Postgres. Here is MS SQL table
CREATE TABLE [dbo].[DailyGgrNgr](
[Date] [DATETIME] NOT NULL,
[PlayerId] [INT] NOT NULL,
[GameId] [INT] NOT NULL,
[GGR] [DECIMAL](18, 4) NOT NULL,
[NGR] [DECIMAL](18, 4) NOT NULL,
[BetCount] [INT] NOT NULL,
[WinCount] [INT] NOT NULL,
[BetAmount] [DECIMAL](18, 4) NOT NULL,
[WinAmount] [DECIMAL](18, 4) NOT NULL
And this the PostgreSQl table
CREATE TABLE public."DailyGgrNgr" ( "Date" timestamp NOT NULL, "PlayerId" int4 NOT NULL, "GameId" int4 NOT NULL, "GGR" numeric(18,4) NOT NULL, "NGR" numeric(18,4) NOT NULL, "BetCount" int4 NOT NULL, "WinCount" int4 NOT NULL, "BetAmount" numeric(18,4) NOT NULL, "WinAmount" numeric(18,4) NOT NULL
The problem is that data sync is done row by row, instead of bulk as expected. I understand this by profiling my postgres server 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)
SSIS Destination is configured to process rows in batches as you can see from the schreenshot below
I believe the problem is with driver because when I use other commercial drivers they works fine. Also according to Microsoft - "If you select Batch and the provider does not support this method, the ODBC destination automatically switches to the Row-by-row mode."
I wonder if there is a possibility to configure the driver to process the rows in batches instead of row by row or is there another free ODBC driver for postgres that allows batch processing.
My Postgres version is PostgreSQL 12.2, SQL Server 2016.