MIME-Version: 1.0
From: Paul van Rixel <pvanrixel@gmail.com>
Date: Wed, 9 Jun 2021 14:48:58 +0200
Message-ID: 
 <CALXfQ0=ER9SzpPFGE8H5Gm5DgmMT0c+WMmk9cWkHnrfO44ULCA@mail.gmail.com>
Subject: Data transfer from PG to SQL Server
To: pgsql-odbc@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000829b0305c454b335"
Archived-At: 
 <https://www.postgresql.org/message-id/CALXfQ0%3DER9SzpPFGE8H5Gm5DgmMT0c%2BWMmk9cWkHnrfO44ULCA%40mail.gmail.com>
Precedence: bulk

--000000000000829b0305c454b335
Content-Type: text/plain; charset="UTF-8"

Hi all,

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

What I did previously was installing the  Postgres ODBC Driver 12_02
version, configuration is ok because a test is successful.  So next step
was to configure a linked server and in that way I can see the tables. To
get the data from straigtforwarded tables without JSON or XML columns I
used openquery from SQL Server. That works for 80% of the tables. I also
see in the Postgresql 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
Server 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

--000000000000829b0305c454b335
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi all,<div><br></div><div>I&#39;m looking for the best wa=
y to transfer data on a weekly basis to a SQL Server Instance which will be=
 the staging area for BI reporting. A SQL Server Linked Server configuratio=
n 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=C2=A0 Postgres =
ODBC Driver 12_02 version, configuration is ok because a test is successful=
.=C2=A0 So next step was to configure a linked server and in that way I can=
 see the tables. To get the data from straigtforwarded tables without JSON =
or XML columns I used openquery from SQL Server. That works for 80% of the =
tables. I also see in the Postgresql logfiles that I&#39;m connected and re=
ad that table.</div><div>Large tables (&gt; 3GB) however did not transfer!<=
br></div><div>Also tried with Export/Import wizard from SQL Server Manageme=
mnt Studio 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 wit=
hout Linked Server or other paid tools like pgOledb? I could not find answe=
r so that&#39;s why I&#39;m here!</div><div><br></div><div>Any help would b=
e appreciated!</div><div><br></div><div>Best regards,</div><div><br></div><=
div>Paul van Rixel</div><div>DBA</div></div>

--000000000000829b0305c454b335--