SSIS: Connect to PostgreSQL

There is a great blog post on Connecting to a PostgreSQL Instance Using SQL Server Integration Services which you’ll want to read.  I will recap the steps directly from this post:

Installation of both drivers (32-bit & 64-bit) is identical. Here are the basic steps to get the driver working:

  1. Download the latest .msi file for Windows from this location: http://www.postgresql.org/ftp/odbc/versions/msi/
  2. Run the msi file on your SQL Server.
  3. Launch the ODBC Administrator Utility and choose the type of data source you need; File, System or User and click Add.
  4. The ODBC Administrator will present a list of drivers. Scroll to the bottom and you will see two options for PostreSQL; ANSI and Unicode. Select the version you need and click Finish.
  5. The Administrator will present a screen on which you must supply a database name, server name, user name and password.
  6. After you have supplied values for these fields, click the Datasource button and make sure the Use Declare/Fetch box is checked. The driver will fail to retrieve larger datasets if you do not check this box. I have not yet found a satisfactory answer for why this is so.

Now you are ready to build a new connection manager in SSIS and hook it to the PostGRES data source you just created. Use the following settings when building out the connection manager:

  1. Select the .Net ProvidersODBC Data Provider.
  2. Select the “Use connection string” radio button. Using the values you configured in the ODBC Administrator, build a connection string as follows: Dsn=PostgreSQL35W;uid=User1
  3. Enter the User name and Password in the fields provided.
  4. Test the connection and you should be ready to go.

 

Here are a few bits of information I’d like to add to this excellent blog post, mainly for my own purposes should I run into this again:

I noticed on the PostgreSQL msi page that there were several versions of the drivers.  I took the most recent of the 32bit, psqlodbc_09_01_0100-1.zip.  The 64 bit drivers have “64 bit” in the name.

The 32-bit ODBC drivers for PostgreSQL get installed here C:Program Files (x86)psqlODBC

After installing the 32 bit driver per the instructions, in your Visual Studio solution be sure to change your Project Properties – Debugging – Run64BitRuntime property to False. This will force it to run in 32-bit mode.   

If you are triggering your package from SQL Server Agent, set the SQL Server Agent job to run in 32-bit mode

For SQL2012, be sure to put the password in a Project Parameter so it is retained in the package on deployment to Integration Services, and doesn’t get stripped out due to package protection.

17 thoughts on “SSIS: Connect to PostgreSQL”

  1. Martina,

    I am happy that you found my article: Connecting to a PostgreSQL Instance Using SQL Server Integration Services, to be helpful and useful. I like your summarization of my post and the additional information you supplied.

    Best,
    David 🙂

  2. Martina and David,

    I am preparing for new project which required pulling data from PostgreSQL instance using SSIS. After searching web for solution, I found your articles and they are very detailed and helpful.
    Thank you so much for sharing your experience and knowledge.

    Sincerely,
    Vadim

  3. Martina and David,

    Martina, In your summary I noticed that you used the 32 bit PostgreSQL driver for SQL 2012. Was that because you’re server was running a 32 bit version of Windows or because the 64-bit driver crashes the 64 bit ODBC Administrator? At least thats what is happening for me on my Windows Server 2012 Standard box.

    David, as per your response to my post on your origional blog, I did look for other posts specific to Windws Server 2012 and did find one that suggested installing both the 32 and 64 bit drives, setting up the 32 bit ODBC connection then copying the 32 bit regestry settings to the 64 bit registry. I can see how that would work, but it seems like a bit of a hach.

    Thanks,

    1. Hi Jamie,

      I can’t remember specifically why I chose the 32 bit, but it was not because it was crashing the 64 bit ODBC Administrator. Good luck with finding a more elegant solution.

      Cheers,
      Martina

  4. Hello,
    Good article.

    But do you know how can I connect postgres with SSAS?

    Best Regards,
    Duarte Gomes

    1. I haven’t done that. Can you not just follow the instructions, but instead of building the connection manager in SSIS, you could create a connection in Visual Studio in your SSAS project in much the same way?
      Cheers,
      Martina

  5. Thanks for this post, I have been battling with this for over a week, tried dotConnect for postgre, Npgsql, and other alternatives but was unable to connect. However after reading this i was now able to properly configure my ODBC and it worked.

  6. Hi,
    Good post.
    I have a scenario where i have to import data from 30 PostgreSQL tables to 30 SQL Server tables using SSIS. Any suggestions on how can I achieve this?

    1. I haven’t done it, but have you tried using the import wizard in SSMS? You should be able to set up the .Net ODBC Provider connection there, once you’ve installed the drivers. In the wizard you can simply map all 30 tables at once and save (don’t run) the SSIS package that is created. Open up the saved package and make the changes to 32 bit and then run.

      If you can’t set up the connection in SSMS, You might have to manually create each task in SSIS.

  7. Hi there, thanks for the good article. My team recently developed new ODBC drivers including PostgreSQL ODBC driver and it would perfect if you include the link to this driver in your article. Here is the PostgreSQL ODBC driver https://www.devart.com/odbc/postgresql/
    We tested it and it works fine. If you can’t include it in the main article please at least don’t delete my comment. Let the people know about new alternative to a standard ODBC driver.
    Thank you in advance and keep writing good articles.

  8. I know this is an old article, but it’s the most helpful one I’ve found so far, and I wondered if you could help me. We’re trying to read from a postgres database via an SSIS package in SQL Server 2014 (Visual Basic 2015). We’ve gotten the ODBC connection set up, and I can see it in SSIS and create an ODBC connection manager. But then, as far as I can see there’s no type of source i can use that will use that ODBC connection manager. Every blog I can find just says to use the ODBC source, but I don’t have an ODBC source option. Is this something I would have needed to install extra?

    I can set it up as an ADO source, using the DSN, but then it won’t give me a list of tables, only the ‘SQL command’ option. And I don’t really want to hard code SQL statements for all of my jobs.

    Have you done this in SQL Server 2014/VB 2015, and do you have any tips?

  9. Iam very happy after find this post and really thank you. Thanks for sharing this Informative content. Well explained. Got to learn new things from your Blog. SQL Server Integration Services (SSIS) is the anchor in a growing suite of products that make up the Microsoft SQL Server Business Intelligence (BI) platform.

  10. Martina:

    I am trying to connect to a PostgreSQL using its Ole DB driver. I am using it in a VS SSIS project. The connection is successful but the Initial catalog is disables. As a result, I am not able to select a table as a Source or Destination.

    Can I seek you help in resolving this issues?

    venki

  11. This is an exclusive post about PostgreSQL and the use of foreign data wrapper actually overcomes and helps people solve the most complex problems and errors.

Leave a Reply

Your email address will not be published. Required fields are marked *