postgresql - Migrating from Postgres to SQL Server 2008

Question

I need to migrate a database from Postgres 7 to SQL Server 2008. I am familiar with the SSIS Import and Export wizard but I am stumped about how to define the data source or define the data provider.

What is the best way to migrate Postgres to SQL Server, and how do I define data sources/drivers for postgres?

Answer 1

I was having problems using the Import Wizard in SQL Server 2008 R2 to import tables from PostgreSQL. I had the PostgreSQL ODBC driver installed, so for the Data Source in the Import Wizard I chose ".Net Framework Data Provider for Odbc" and supplied the DSN name for my PostgreSQL database. The wizard found the tables okay, but when I went to perform the import I got the error

Column information for the source and destination data could not be retrieved.

“Billing” -> [dbo].[Billing]:

– Cannot find column -1.

I found the solution in the Microsoft blog post here. Apparently the problem is that various ODBC drivers use different attribute names when reporting column metadata. To get the import to work I had to edit the "ProviderDescriptors.xml" file, which was located at

C:Program FilesMicrosoft SQL Server100DTSProviderDescriptorsProviderDescriptors.xml

In the ...

<dtm:ProviderDescriptor SourceType="System.Data.Odbc.OdbcConnection">

... element I had to change the attributes from ...

<dtm:ColumnSchemaAttributes
    NameColumnName = "COLUMN_NAME"
    OrdinalPositionColumnName="ORDINAL_POSITION"
    DataTypeColumnName = "TYPE_NAME"
    MaximumLengthColumnName = "COLUMN_SIZE"
    NumericPrecisionColumnName = "COLUMN_SIZE"
    NumericScaleColumnName = "DECIMAL_DIGITS"
    NullableColumnName="NULLABLE"
    NumberOfColumnRestrictions="4"
/>

... to ...

<dtm:ColumnSchemaAttributes
    NameColumnName = "COLUMN_NAME"
    OrdinalPositionColumnName="ORDINAL_POSITION"
    DataTypeColumnName = "TYPE_NAME"
    MaximumLengthColumnName = "LENGTH"
    NumericPrecisionColumnName = "PRECISION"
    NumericScaleColumnName = "SCALE"
    NullableColumnName="NULLABLE"
    NumberOfColumnRestrictions="4"
/>

That is, I had to tweak the MaximumLengthColumnName, NumericPrecisionColumnName, and NumericScaleColumnName attribute values to "LENGTH", "PRECISION", and "SCALE", respectively.

Once that change was made the import from PostgreSQL to SQL Server ran successfully.