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