From: David Parenteau <dparenteau@axper.com>
To: Paul van Rixel <pvanrixel@gmail.com>, "pgsql-odbc@lists.postgresql.org"
	<pgsql-odbc@lists.postgresql.org>
Subject: Re: Data transfer from PG to SQL Server
Thread-Topic: Data transfer from PG to SQL Server
Thread-Index: AQHXXTN9pZ9VHyy2O0C2yTYe7cKGNasLsUK/
Date: Wed, 9 Jun 2021 13:53:55 +0000
Message-ID: 
 <YQXPR0101MB1301EA345FE85FA8929EE81CC2369@YQXPR0101MB1301.CANPRD01.PROD.OUTLOOK.COM>
References: 
 <CALXfQ0=ER9SzpPFGE8H5Gm5DgmMT0c+WMmk9cWkHnrfO44ULCA@mail.gmail.com>
In-Reply-To: 
 <CALXfQ0=ER9SzpPFGE8H5Gm5DgmMT0c+WMmk9cWkHnrfO44ULCA@mail.gmail.com>
Accept-Language: en-US
Content-Language: en-US
Content-Type: multipart/alternative;
	boundary="_000_YQXPR0101MB1301EA345FE85FA8929EE81CC2369YQXPR0101MB1301_"
MIME-Version: 1.0
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 680faf72-88af-44d1-6c04-08d92b4e05d0
X-MS-Exchange-CrossTenant-originalarrivaltime: 09 Jun 2021 13:53:55.4014
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 25a5f507-f32a-4534-ba73-9466da238c43
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 bBDHS8C7RUKnMDQ3Oj7LOzmiwHY+ESQCofBUos/MumCg0srrFbsO4HgIkzP9eX9UefNq7rcF/shyEYh4WDOx0g==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: YQBPR0101MB1140
List-Id: <pgsql-odbc.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-odbc@lists.postgresql.org>
List-Owner: <mailto:pgsql-odbc-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-odbc>
Archived-At: 
 <https://www.postgresql.org/message-id/YQXPR0101MB1301EA345FE85FA8929EE81CC2369%40YQXPR0101MB1301.CANPRD01.PROD.OUTLOOK.COM>
Precedence: bulk

--_000_YQXPR0101MB1301EA345FE85FA8929EE81CC2369YQXPR0101MB1301_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Maybe you could use a Scheduled Powershell script to get your data using Po=
stGres ODBC and save them in SQL Server using the Bulk Copy object? Otherwi=
se, getting data to json/csv files and then BULK INSERT these file sinto SQ=
L Server might also be wuick based on my experience.

Ex:
$bc =3D new-object ("System.Data.SqlClient.SqlBulkCopy") $cn

NOTE: On my side, to get my ODBC long calls to work I had to set the tcp ti=
meout managed by pgbouncer to 120 instead of default.

HTH.

David

________________________________
From: Paul van Rixel <pvanrixel@gmail.com>
Sent: Wednesday, June 9, 2021 8:48 AM
To: pgsql-odbc@lists.postgresql.org <pgsql-odbc@lists.postgresql.org>
Subject: Data transfer from PG to SQL Server

Hi all,

I'm looking for the best way to transfer data on a weekly basis to a SQL Se=
rver Instance which will be the staging area for BI reporting. A SQL Server=
 Linked Server configuration is because of security issues not allowed so n=
ow I need some help!

What I did previously was installing the  Postgres ODBC Driver 12_02 versio=
n, configuration is ok because a test is successful.  So next step was to c=
onfigure a linked server and in that way I can see the tables. To get the d=
ata from straigtforwarded tables without JSON or XML columns I used openque=
ry from SQL Server. That works for 80% of the tables. I also see in the Pos=
tgresql logfiles that I'm connected and read that table.
Large tables (> 3GB) however did not transfer!
Also tried with Export/Import wizard from SQL Server Managememnt Studio but=
 also from SSIS. Both failed.

So my simple question is how to transfer Postgresql data/tables to SQL Serv=
er without Linked Server or other paid tools like pgOledb? I could not find=
 answer so that's why I'm here!

Any help would be appreciated!

Best regards,

Paul van Rixel
DBA

--_000_YQXPR0101MB1301EA345FE85FA8929EE81CC2369YQXPR0101MB1301_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-=
1">
<style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo=
ttom:0;} </style>
</head>
<body dir=3D"ltr">
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255);">
Maybe you could use a Scheduled Powershell script to get your data using Po=
stGres ODBC and save them in SQL Server using the Bulk Copy object? Otherwi=
se, getting data to json/csv files and then BULK INSERT these file sinto SQ=
L Server might also be wuick based
 on my experience.<br>
<br>
Ex:&nbsp;</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255);">
$bc =3D new-object (&quot;System.Data.SqlClient.SqlBulkCopy&quot;) $cn</div=
>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255);">
<br>
NOTE: On my side, to get my ODBC long calls to work I had to set the&nbsp;<=
span style=3D"box-sizing:border-box;font-family:&quot;Segoe UI&quot;, syste=
m-ui, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, sans-serif=
;font-size:14px">tcp timeout managed by pgbouncer to 120 instead of default=
.</span></div>
<div style=3D"color: rgb(0, 0, 0); background-color: rgb(255, 255, 255);"><=
font face=3D"Segoe UI, system-ui, Apple Color Emoji, Segoe UI Emoji, sans-s=
erif"><span style=3D"font-size: 14px;"><br>
</span></font><font face=3D"Calibri, Arial, Helvetica, sans-serif"><span st=
yle=3D"font-size: 12pt;">HTH.</span></font></div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255);">
<br>
</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255);">
David</div>
<div>
<div id=3D"appendonsend"></div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<hr tabindex=3D"-1" style=3D"display:inline-block; width:98%">
<div id=3D"divRplyFwdMsg" dir=3D"ltr"><font face=3D"Calibri, sans-serif" co=
lor=3D"#000000" style=3D"font-size: 11pt;"><b>From:</b> Paul van Rixel &lt;=
pvanrixel@gmail.com&gt;<br>
<b>Sent:</b> Wednesday, June 9, 2021 8:48 AM<br>
<b>To:</b> pgsql-odbc@lists.postgresql.org &lt;pgsql-odbc@lists.postgresql.=
org&gt;<br>
<b>Subject:</b> Data transfer from PG to SQL Server</font>
<div>&nbsp;</div>
</div>
<div>
<div dir=3D"ltr">Hi all,
<div><br>
</div>
<div>I'm looking for the best way to transfer data on a weekly basis to a S=
QL Server Instance which will be the staging area for BI reporting. A SQL S=
erver Linked Server configuration is because of security issues not allowed=
 so now I need some help!</div>
<div><br>
</div>
<div>What I did previously was installing the&nbsp; Postgres ODBC Driver 12=
_02 version, configuration is ok because a test is successful.&nbsp; So nex=
t step was to configure a linked server and in that way I can see the table=
s. To get the data from straigtforwarded tables
 without JSON or XML columns I used openquery from SQL Server. That works f=
or 80% of the tables. I also see in the Postgresql logfiles that I'm connec=
ted and read that table.</div>
<div>Large tables (&gt; 3GB) however did not transfer!<br>
</div>
<div>Also tried with Export/Import wizard from SQL Server Managememnt Studi=
o but also from SSIS. Both failed.</div>
<div><br>
</div>
<div>So my simple question is how to transfer Postgresql data/tables to SQL=
 Server without Linked Server or other paid tools like pgOledb? I could not=
 find answer so that's why I'm here!</div>
<div><br>
</div>
<div>Any help would be appreciated!</div>
<div><br>
</div>
<div>Best regards,</div>
<div><br>
</div>
<div>Paul van Rixel</div>
<div>DBA</div>
</div>
</div>
</div>
</body>
</html>

--_000_YQXPR0101MB1301EA345FE85FA8929EE81CC2369YQXPR0101MB1301_--