Your best option is to use the Postgres ODBC driver.
Best driver to use to connect to a postgres database from Sql server integration services.
We want to use Sql server integration services (SSIS) to read data from a postgres database. The postgres database is version 9.6. We are using SSIS version 2012 but can upgrade to a later version.
Please can someone advise on what type of database connection to use (OLEDEB/ODBC) - and what postgres driver we might need.
Many thanks
2 additional answers
Sort by: Most helpful
-
ZoeHui-MSFT 32,081 Reputation points
2021-07-26T05:48:18.873+00:00 Hi @Greg Booth ,
Please refer the documentation, you would need to follow these steps to connect SSIS to a Postgres database:
1.Get the PostgreSQL ODBC driver, either with Stack Builder or using ODBC
2.Connect to PostgreSQL with the PostgreSQL ODBC driver (psqlODBC), using the proper connection string, typically Driver={PostgreSQL ODBC Driver(UNICODE)};Server=<server>;Port=<port>;Database=<database>;UID=<user id>;PWD=<password>
Also you may refer postgresql-ssis-task-import-2008.rst for details.
Regards,
Zoe
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October -
si li 20 Reputation points
2023-07-15T07:50:03.6633333+00:00 Why not try the PostgreSQL foreign data wrapper?
You can access SQL Server from PostgreSQL using tds-fdw.
CREATE FOREIGN DATA WRAPPER tds-fdw;
CREATE SERVER mssql_svr FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername '127.0.0.1', port '1433', database 'tds_fdw_test', tds_version '7.1');
CREATE FOREIGN TABLE mssql_table (id integer, data varchar) SERVER mssql_svr OPTIONS (table_name 'dbo.mytable', row_estimate_method 'showplan_all');
INSERT INTO mssql_table SELECT * FROM pgsql_table;