Best driver to use to connect to a postgres database from Sql server integration services.

Greg Booth 1,276 Reputation points
2021-07-23T11:52:56.543+00:00

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

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,438 questions
0 comments No comments
{count} votes

2 additional answers

Sort by: Most helpful
  1. 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


  2. 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;

    0 comments No comments