Export data from Postgres to SQL Server using SSIS

By:   |   Comments (18)   |   Related: More > Integration Services Development


Sometimes it is necessary to migrate data from Postgres to SQL Server.  Unfortunately, there is not too much information on the Internet about this topic. In this tip we will outline the steps to import a single table from Postgres to SQL Server with SQL Server Integration Services.


In this example, the database used to import data is the 'Postgres' database which is installed by default. We will use this database to export data from the students table in Postgres to SQL Server with SQL Server Integration Services.

Here are the prerequisites needed:

  • Install the Postgres Database.
  • Install the PGAdmin.
  • Install the Postgres OLE DB Provider on the machine where SQL Server Integration Services will run, because this will install a Postgres OLE DB provider.
  • Install or have access to SQL Server Enterprise or Standard edition.  In this example, I am using SQL Server 2008 R2, but SQL Server 2005 is an option as well.

In this example, a table named students will be created in Postgres and then it will be imported to the Adventureworks sample SQL Server database.

Demonstration - Export data from Postgres to SQL Server

Let's walk through a step by step example to export data from Postgres to SQL Server.

  1. Open the PostgreSQL>pgAdmin III tool.
  2. Start the pgAdmin tool

  3. In the pgAdmin III tool, right click on the PostgreSQL instance and select the 'Connect' option.
  4. Connect to the Postgres database in pgAdmin III

  5. Specify the login and password.  The 'postgres' user is a super user created by default. In this tip, the 'postgres' user will be used.

  6. Open the Server tree to PostgreSQL | Databases | postgres | Schemas| public| tables and right click and select New Table.
  7. Create a New Table in Postgres

  8. In the 'Name' field enter 'Students' and in the 'Owner' combo box, select 'postgres
  9. '.
    Specify the table properties in Postgres

  10. In the columns Tab, press the 'Add' button.
  11. Add columns to a table in Postgres

  12. Add the 'ID' column with data type integer and 'Name' with the data type character(30)
  13. Create the ID and name columns in Postgres

  14. Click the 'Constraints' tab, select the 'Primary Key' option in the combo box and press the 'Add' button.
  15. In the New Primary Key window, select the 'ID' column in the combo box and press the 'Add' button.
  16. Create a Primary Key on a table in Postgres

  17. Click the 'students' table in the Object browser and press the View the data in the selected object icon.
    View the data in a Postgres table

  18. Add data to the Student table.
  19. Add data to the Postgres table

  20. Now, we have a Student table in Postgres database with data. Now, let's import the data to SQL Server using the Integration Services. Open the SQL Server Business Intelligence Development Studio and open a new Integration Services Project.
  21. Create a SSIS package to export data from Postgres

  22. In the Toolbox drag and drop the Data Flow task to the design pane.
  23. Drag and drop the Data Flow in SQL Server Integration Services

  24. Double click in the Data flow Task in order to create a Data Flow.
  25. In the toolbar drag and drop the OLE DB Source and OLE DB Destination and join both tasks with the green arrow.
  26. Add the Ole DB Source and Destination to the SSIS Data Flow Tab

  27. Double click on the OLE DB Source task and press the New button to create a new OLE DB connection manager..
  28. Add the OLE DB Source properties in SSIS

  29. In the Configure OLE DB Connection Manager, press 'New'.
  30. Create a Data Connection in SSIS

  31. In the Connection Manager, select the Native OLE DB\PostgresSQL Native Provider. Note that this option will be available only if you installed the prerequisites specified at the beginning of this tip.

  32. In the Server or File name, specify the name of the Postgres Server.  In this example, the localhost is used.

  33. Select the option 'Use a specific user name and password'.

  34. Check the allow saving password option.

  35. In user name specify the user to connect to postgres and the user password.  In this example, the postgres user is used.

  36. In the initial catalog, select the database used to create the table.  Remember in step 4, we used the postgres database to create the students table.
  37. Specify the Postgres connection properties in SQL Server Integration Services

  38. Press OK twice.

  39. In the OLE DB Source Editor, in the name of the table or the view, select the "public"."Students" table and press OK.
  40. Specify the Table Name in the SSIS OLE DB Source Editor

  41. In the design pane, double click the OLE DB Destination task.

  42. In the OLE DB connection Manager, press the New button
  43. Configure the table properties in the SSIS OLE DB Destination Editor

  44. In the Configure OLE DB Connection Manager, press 'New
  45. '.
    Select the Data Connection in SSIS for Postgres

  46. In the Connection Manager, select the Native OLE DB\SQL Server Native Client
  47. .
  48. In the Server name specify the name of the SQL Server Instance name.  In this example, the localhost is used.

  49. Specify the log on to the server options in order to login to SQL Server.  In this example, the windows authentication is used.

  50. In the select or enter the database name combo box, select the SQL Server database where you want to import the Postgres Students table.  In this example, the Adventureworks will be used and press OK.
  51. Specify the destination Database in SQL Server Integration Services

  52. In the OLE DB Destination Editor, in the Name of the table or view press the New button./li>
    Select the table destination in SSIS

  53. In the Create table windows, use this code and press OK:
  54. CREATE TABLE [Students] (
    [ID] int,
    [Name] nvarchar(30)

    Create the students table in SSIS

  55. In the OLE BD Destination Editor, click the Mapping page and press OK
  56. Map the tables in SSIS between the source and destination

  57. Now the project is ready to start. Press the start debugging icon. If everything is successful, the tasks should be colored green and the numbers of rows imported should be displayed.
  58. Run the SSIS package

  59. In order to verify that the table was imported, in SQL Server Management Studio, go to the Adventureworks database (or the database that you select in step 32) and verify that the students table was created. Open the table to verify that the data is correct.
  60. Verify the table was created with the data in SQL Server Management Studio
  61. In this tip, you have learned how to import tables from Postgres to SQL Server.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Sunday, February 13, 2022 - 4:15:51 AM - Cal Back To Top (89781)
A good article on using those tools to transfer data.

I have developed an alternative tool: SQLpipe. It is an open-source solution that can transfer data from PostgreSQL to SQL Server.

Here is a step by step guide on using it transfer data from PostgreSQL to SQL Server: https://sqlpipe.com/transfer-data-from-postgresql-to-sql-server/

Saturday, February 15, 2020 - 2:35:21 PM - Mario Hernandez Back To Top (84524)

You can use SSMS Import Data... Connect to Postgress as a source, SQL as destination... you will copy all the schema and data in one pass.

Tuesday, November 22, 2016 - 2:00:37 AM - eric81 Back To Top (43821)

 I am attempting to export data from Postgres database to SQL Server 2016.  I've installed the 32-bit, and 64-bit drivers which show up when I go to odbc adminstrator however in Visual Studio, OLE DB Source there are no options.  Any ideas?


Thursday, February 11, 2016 - 4:00:31 PM - Jason Back To Top (40653)

Hi Daniel, 

This post is SUPER helpful!  


I have PostGres installed on a Linux server, and PGAdmin and SQL Server installed locally on Windows.   


How do I copy the 100K+ rows / rsults of a SQL statement from PostGres to SQL Server?  


Monday, September 14, 2015 - 6:45:23 PM - Artur Back To Top (38678)

Does someone have PGNP OLEDB provider?

Friday, October 17, 2014 - 3:32:00 PM - Daniel Back To Top (34998)

Trial version of the driver is limited to 100 rows.

Friday, October 17, 2014 - 2:13:09 PM - Jared C Back To Top (34996)

How do you export a database from Microsoft SQL Server Management Studio to PostgresQl

Thursday, June 26, 2014 - 12:07:31 PM - Mukund A Back To Top (32416)

Whenever you integrate data from other databases, always receive the data in work table or staging table. On the staging table/work table, apply business rules, clean them up and then insert/update the records to your application table. This way you can address the formatting, validations more appropriately.

Thursday, February 20, 2014 - 10:29:44 AM - Giovanni Massi Back To Top (29513)
I am working on a PostgreSQL DB and I will need to migrate this to a MS SQL 2008 R2. The difference I have is, although the information is generally the same, the tables used to house the information will be different and some of the information itself will also be formatted differently. What suggestions do you have so I can try and move the information successfully?

Tuesday, May 21, 2013 - 1:25:29 AM - feralymph Back To Top (24054)

Nice tutorial. For people using PGNP OLEDB provider, that is 3td pary software, perhaps using some odbc in oficial site:




Friday, March 22, 2013 - 8:20:25 PM - Gaius Gracchus Back To Top (22978)

This is a lot of work for a single table -- I wonder how you would import an entire database if it had many, many complex tables and constraints.

Wednesday, January 16, 2013 - 5:38:39 AM - b Back To Top (21475)


Is it possible exporting data from Ms SQL db to Postgresql db using stored procedure? Is any stored procedure that can export data from one Ms SQL Table to Postgresql Table?

Sunday, August 26, 2012 - 3:29:28 PM - Varsha Back To Top (19231)

Thanks for the info., Daniel!!

Friday, August 24, 2012 - 5:22:24 PM - Daniel Back To Top (19220)

Yeah, I recieve that message all the time with the trial version, but it works after the message.

Friday, August 24, 2012 - 11:00:46 AM - Varsha Back To Top (19214)


Im trying to export data from Postgresql db to SQL db using SSIS:

I had installed the trial version of the Postgre SQL native provider.While creating a new connection , Im getting the below error while trying to test the connection to the Postgresql server and database.

 " This trial version of PGNP OLEDB Provider is for the evaluation
purposes only. It may not be distributed or copied except for backup purposes."

Could you please let me kniow if I had missed out anything or is this an limitation of the trial version??

Any help on this??!!



Wednesday, April 25, 2012 - 11:30:58 AM - Kirthika Back To Top (17106)

K K.I suspected the same.thank alot craig.

Wednesday, April 25, 2012 - 10:18:18 AM - Craig Back To Top (17102)

Trial version of the driver is limited to 100 rows. You need to buy it to get full access to the data.

Also, postgres doesnt have an automatic concept of clustered indexes like sql server does. So your data will not comeback in the order of the primary key by default.

Wednesday, April 25, 2012 - 6:49:02 AM - Kirthika Back To Top (17096)


I tried tis. everything is working fine. But it's copying only 100 rows. it's also randomly not in any sequence. pls share the possible reasons. 

Note: I tries with Trial version of PGNP OLEDB provider(PGNP-

get free sql tips
agree to terms