Converting your data from MS SQL Server 7 to PostgreSQL 7.1.x

This page contains historical information or deprecated articles.

by Ryan C. Bonham <ryan@ryanbonham.com> v1.00, Last updated 5th January 2002

Prerequisites

• MS SQL 7 (of course)
• RedHat Linux 7.1
• PostgreSQL 7.1 RPM's
• PostgreSQL ODBC Client

Directions

Follow the steps below to convert your Microsoft SQL Server 7 data to PostgreSQL.

From Your Redhat Machine

• As root, create a postgres user.

adduser postgres

• Install the PostgreSQL RPM's on your machine.

• Start PostgreSQL for the first time.

service PostgreSQL start

• Set PostgreSQL to run at system Startup.

chkconfig --add PostgreSQL

• Login as the Postgres User

su postgres

• Create a Blank Database

createdb Databasename

From your SQL 7 Server or a machine with the SQL administration tools installed

1. Install the PostgreSQL ODBC Drivers
2. Create A New User or System DSN (Data Source)
   On Windows 2000, you can find this under the "Administrative Tools" Control Panel
   1. When Prompted for Driver to use, select "PostgreSQL"
   2. For Data Source enter the name you wish to give this connection. (e.g. Pgsql)
   3. For Database enter the name you used in Step 6 above
   4. For Server enter the IP or name of your PostgreSQL Database Server
   5. Leave the Username and password blank
   6. Under the Driver Options Select "Parse Statements" and Uncheck "Bools as char"
   7. Under "DataSource" Options select "Row Versioning"
3. Open the SQL 7 Enterprise Manager
4. Expand your server and select "Data Transformation Services"
5. Open the "Local Packages"
6. Select New package from the Action Menu
7. Select "Microsoft OLE DB Provider for SQL Server" from the Data Menu
   1. Pick Your SQL Server and database you wish to export and press ok
8. Select "Other Connection" from the Data menu
   1. For Data Source select "PostgreSQL"
   2. Select the DSN we created above (Pgsql)
   3. Enter your username (postgres)
9. Select both Connections you just created
10. Select "Add Transformation" from the Workflow menu
11. Double click the Transformation Line that was just created
    1. On the sources tab select the table you wish to export
    2. On the destination tab select Create new
    3. Check the DataTypes and Column Lengths. Make any corrections and press ok
    4. On the Transformation screen make sure the source columns are pointing to the correct destination columns. You can change the Column mappings by selecting the appropriate columns and using the delete and insert buttons on the screen.
    5. Check the options on the Advanced Tab. You shouldn't need to make changes
    6. Press Ok
12. Select Save from the package menu
13. Select Run from the package menu
14. Your data should now be exported. If you get any errors double check the data type and lengths are correct on the table you created in Step 11