none
Working with a PostgreSQL database as a OLE DB destination RRS feed

  • Question

  • Hello:

    I am using VS 2019 and trying to move data from SQL Server to PostgreSQL 11.3. When I try to create a OLE DB Destination pointing to a PostgreSQL database using PostgresSQL Ole DB Provider, the initial Catalog is disabled. 

    Can someone tell me how I can enable it so I can choose the database table I am interested in?

    As you may know, PostgreSQL structure is quite different. It can have multiple databases and each database can have any schemas.

    Thank you.

    Venki

    Friday, May 31, 2019 10:18 AM

Answers

  • Hi Venki,

    I do not think the OLEDB driver you use is the appropriate one because as far as I know the PostgreSQL maintainers did not implement support for the Microsoft's stock OLDEDB driver.

    I know one exists as a commercial offering https://www.postgresql.org/download/products/2-drivers-and-interfaces/

    Long story short, the approach I (and looks many others) successfully used is ODBC.
    Please see https://stackoverflow.com/questions/1370326/migrate-data-from-ms-sql-to-postgresql

    that lists the following instructions:

        Firstly, you need to install the PostgreSQL ODBC Driver for Windows. It's very important to install the correct version in terms of CPU arch (x86/x64).

        Inside Management Studio, Right click on your database: Tasks -> Export Data

        Choose SQL Server Native Client as the data source.

        Choose .Net Framework Data Provider for ODBC as the destination driver.

        Set the Connection String to your database in the following form:

        Driver={PostgreSQL ODBC Driver(UNICODE)};Server=;Port=;Database=;UID=;PWD=

        In the next page, you just need to select which tables you want to export. SQL Server will generate a default mapping and you are free to edit it. Probably you`ll encounter some Type Mismatch problems which take some time to solve. For example, if you have a boolean column in SQL Server you should export it as int4.



    Arthur

    MyBlog


    Twitter

    Friday, May 31, 2019 7:10 PM
    Moderator
  • Hi Venki,

    The following links will be helpful.

    SSIS: Connect to PostgreSQL

    Connect to a PostgreSQL Data Source

    Best Regards,

    Mona Lv


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by TheVenkster Tuesday, June 18, 2019 10:40 AM
    Monday, June 3, 2019 1:49 AM

All replies

  • Hi Venki,

    I do not think the OLEDB driver you use is the appropriate one because as far as I know the PostgreSQL maintainers did not implement support for the Microsoft's stock OLDEDB driver.

    I know one exists as a commercial offering https://www.postgresql.org/download/products/2-drivers-and-interfaces/

    Long story short, the approach I (and looks many others) successfully used is ODBC.
    Please see https://stackoverflow.com/questions/1370326/migrate-data-from-ms-sql-to-postgresql

    that lists the following instructions:

        Firstly, you need to install the PostgreSQL ODBC Driver for Windows. It's very important to install the correct version in terms of CPU arch (x86/x64).

        Inside Management Studio, Right click on your database: Tasks -> Export Data

        Choose SQL Server Native Client as the data source.

        Choose .Net Framework Data Provider for ODBC as the destination driver.

        Set the Connection String to your database in the following form:

        Driver={PostgreSQL ODBC Driver(UNICODE)};Server=;Port=;Database=;UID=;PWD=

        In the next page, you just need to select which tables you want to export. SQL Server will generate a default mapping and you are free to edit it. Probably you`ll encounter some Type Mismatch problems which take some time to solve. For example, if you have a boolean column in SQL Server you should export it as int4.



    Arthur

    MyBlog


    Twitter

    Friday, May 31, 2019 7:10 PM
    Moderator
  • Hi Venki,

    The following links will be helpful.

    SSIS: Connect to PostgreSQL

    Connect to a PostgreSQL Data Source

    Best Regards,

    Mona Lv


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by TheVenkster Tuesday, June 18, 2019 10:40 AM
    Monday, June 3, 2019 1:49 AM
  • Mona:

    I tried following the steps in the link SSIS: Connect to PostgreSQL. When I set up a User DBS and test the connection, I am able to connect. However, when I try to use it in SSIS, I get the following error message:


    Monday, June 17, 2019 9:48 PM
  • Looks like you need .Net over ODBC and build the proper connection string

    Connect to PostgreSQL with ODBC before


    Arthur

    MyBlog


    Twitter

    Tuesday, June 18, 2019 1:55 AM
    Moderator
  • Arthur:

    I don't understand your comment. What should I do to resolve the issue regarding 'Architecture mismatch between Driver and Application"?

    I am using the 64-bit version of the ODBC driver and the connection string I created seems to be correct.

    Venki

    Tuesday, June 18, 2019 10:12 AM
  • I am using the 64-bit version of the ODBC driver

    SSDT is a 32 bit application and can only use 32 bit OleDB Provider / ODBC entries; that's what the error message with "architecture mismatch" means

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, June 18, 2019 10:35 AM
  • Olaf:

    Thanks. That resolved the architecture mismatch. Using the 32-bit driver, I went little further. I was able to retrieve all the tables from the PostgreSQL database. But when I select a table and retrieve existing data, I get an error:

    TITLE: Microsoft Visual Studio
    ------------------------------
    Exception from HRESULT: 0xC020204A
    Error at Data Flow Task [ODBC Destination [19]]: SQLSTATE: 42703, Message: ERROR: column "med?????o" does not exist;
    No query has been executed with that handle;
    Error at Data Flow Task [ODBC Destination [19]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    By the way, I was able to use the same DSN to retrieve the data by linking the table to an Access database.

    venki!

    Wednesday, June 19, 2019 6:42 PM