public inbox for [email protected]
help / color / mirror / Atom feedData transfer from PG to SQL Server
2+ messages / 2 participants
[nested] [flat]
* Data transfer from PG to SQL Server
@ 2021-06-09 12:48 Paul van Rixel <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Paul van Rixel @ 2021-06-09 12:48 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Data transfer from PG to SQL Server
@ 2021-06-09 13:53 David Parenteau <[email protected]>
parent: Paul van Rixel <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: David Parenteau @ 2021-06-09 13:53 UTC (permalink / raw)
To: Paul van Rixel <[email protected]>; [email protected] <[email protected]>
Maybe you could use a Scheduled Powershell script to get your data using PostGres ODBC and save them in SQL Server using the Bulk Copy object? Otherwise, getting data to json/csv files and then BULK INSERT these file sinto SQL Server might also be wuick based on my experience.
Ex:
$bc = 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 timeout managed by pgbouncer to 120 instead of default.
HTH.
David
________________________________
From: Paul van Rixel <[email protected]>
Sent: Wednesday, June 9, 2021 8:48 AM
To: [email protected] <[email protected]>
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 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-06-09 13:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-09 12:48 Data transfer from PG to SQL Server Paul van Rixel <[email protected]>
2021-06-09 13:53 ` David Parenteau <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox