Sql-server – Transfer data from MS SQL to PostgreSQL with SSIS ODBC destination

postgresqlsql serverssis

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

enter image description here

However it still inserts data row by row. Below you can see my ODBC Data Source configs.
enter image description here
enter image description here
enter image description here

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.

Best Answer

Isn't this a case for a tools rethink ? How about BCP from SQL Server to write out a csv text file & Bulk Load on the PostgresSQL side to do the load ?

Related Question