Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Export data from Postgres to SQL Server using SSIS


By:   |   Last Updated: 2012-04-03   |   Comments (16)   |   Related Tips: More > Integration Services Development

Problem

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.

Solution

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


Last Updated: 2012-04-03


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 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

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

Does someone have PGNP OLEDB provider?


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

Trial version of the driver is limited to 100 rows.


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

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

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
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

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

http://www.postgresql.org/ftp/odbc/versions/msi/

 

 


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

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

Hi,

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

Thanks for the info., Daniel!!


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

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

HI,

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??!!

Thanks

 


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

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


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

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

Hi 

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-1.3.0.2256).


Learn more about SQL Server tools