LONG: How to migrate data from MS-SQL7 to PostgreSQL 7.0

This is how to get MS-SQL7 to copy data (either whole tables, or from
queries) into PostgreSQL. There are other ways of doing this (like
pgaccess), but this describes how to get the SQL server itself to do the
job.

The key to this is Microsoft's DTS (Data Transformation Services). This
is (for once) a useful utility that allows you to transfer & transform
data from one source to another. The beauty of it, is that the data can
come from any source, to any destination, which is what we will use
here.

Prerequisites:

MS-SQL7.0 running under NT4 Server, SP6A
PostgreSQL ODBC driver 6.40.00.07 installed on the SQL server.
PostgreSQL 7.0 Final running under Linux, SuSE 6.3
Step 1:

If it doesn't exist, create the database on the PostgreSQL server.

Then on the NT box, create an ODBC Data Source. Set the Database,
Server, User Name & Password fields as normal.
Under Driver, uncheck ReadOnly, and check Parse Statements and Unknowns
as LongVarChar.
Under Advanced, clear ReadOnly, but under OID Options, check Show Column
and Fake Index.

NB: You may not need to do all of the above (other than ReadOnly :) )
but they are the settings that worked for me.

Step 2:

Open SQL Server Enterprise Manager, and expand the source SQL server.
Right click Data Transformation Services, and select New Package. You
should be presented with the DTS Package editor.

Now, under the Data menu, select Microsoft OLE DB Provider for SQL
Server. Select the authentication scheme and select the source database.

Back under the Data menu, select Other Connection. Select the Data
Source created in Step 1.

Step 3:

Click once the SQL server icon, then while holding down the Control key,
click the PostgreSQL icon. Then under Workflow, select Add Transform. An
arrow should appear showing the direction the data will flow.

Double click the arrow to show the Transformation properties.

Under the Source tab, you have two choices. Either select a table name,
in that case you are copying an entire table, or select SQL Query, and
enter a select statement to limit or customise the data.

Under the Destination tab, select the table you want to copy into.

Now this is where we have a problem. If the destination table doesnt
exist, DTS has a Create Table button. However, I couldn't get it to
create the table correctly. This was because it wraps the table name and
the field names within double quotes. Postgresql then creates the table,
but it interprets the quotes to mean "don't convert the names to
lowercase". Later, when DTS does the copy, it doesn't include the
quotes, so postgres duely lowercases the names, and basically it fails.
So, when using the Create Table facility, manually lowercase the table
and column names before clicking OK to create the table. Also, make sure
the column types match. It seems that DTS loves the first column to be
an int4, even if the source isn't.

Now, under the transformation tab you should see each column from the
source (on the left) connected by an arrow to a destination column (on
the right). Now, here you can change which one you want it to copy to,
or even write a short script to convert, merge a column, etc. Normally
you can leave it asis, but the script capability is really useful.

Once you are ready, save the package, then click go.

You should then see a nice little animation of some cogs mangling your
data, and if all's well you should get notification that it's completed.

Errors:

When errors occur, double click the entry that failed, and it displays
the error message. The most common ones are where a transformation
failed. Usually this is caused by a wrong data type on the destination
table, or if using a script on a column, it's not converting properly.

--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.