Migrating from Postgres to SQL Server 2008

sql-server postgresql ssis data-migration
reach4thelasers picture reach4thelasers · Sep 14, 2010 · Viewed 22.2k times · Source

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

Gord Thompson picture Gord Thompson · Nov 18, 2016

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 Files\Microsoft SQL Server\100\DTS\ProviderDescriptors\ProviderDescriptors.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.

Related questions

What is the PostgreSQL equivalent for ISNULL()

In MS SQL-Server, I can do: SELECT ISNULL(Field,'Empty') from Table But in PostgreSQL I get a syntax error. How do I emulate the ISNULL() functionality ?

Read More
How to do a Postgresql subquery in select clause with join in from clause like SQL Server?

I am trying to write the following query on postgresql: select name, author_id, count(1), (select count(1) from names as n2 where n2.id = n1.id and t2.author_id = t1.author_id ) from names as n1 group by name, …

Read More
Best practices for SQL varchar column length

Every time is set up a new SQL table or add a new varchar column to an existing table, I am wondering one thing: what is the best value for the length. So, lets say, you have a column called …

Read More