Migrating from Postgres to SQL Server 2008

I wish you the best of luck in trying to import from PostgreSQL into SQL Server using SQL Server Import and Export Wizard. However, I have read numerous message board threads with people having trouble getting it to work. For example:

• Import Data from Postgresql to SQL Server 08 Error

Here is the most helpful thread that I have found on the topic:

• Import data from postgreSQL into SQL server 2005

---

To help someone who might be trying to achieve similar goal as mine. Instead of selecting the “PostgreSQL OLE DB Provider” in the data source drop down menu of SQL Server Import and Export Wizard, select “.Net Framework Data Provider for Odbc”

Then you have to make a DSN and provide a ConnectionString. Following ConnectionString worked for me

Driver={PostgreSQL};Server=localhost;Port=5432;Database=TestMasterMap;Uid=postgres;Pwd=;

To make a DSN you have to go into Administrative Toolsà Data Sources (ODBC) and create a user DSN. Once this is done you can supply the DSN name in the DSN text box of SQL Server Import and Export Wizard.

---

One commenter claimed that it worked, but that he got "Out of memory while reading tuples" errors on big tables. So for tables with more than 3 million rows, he had to break the import up into 3 million row chunks.

Also, there's a link to the native .NET provider for PostgreSQL in that thread.

Personally, if this is something that I only had to do once, and if I understood the schema and the data fairly well, I would try:

1. export the data from PostgreSQL as flat files
2. create the schema in SQL Server (without PKs or constraints)
3. use the SSIS Import/Export Wizard to import the flat files
4. then create PKs and necessary constraints

It might take you less time to do the above than messing with SSIS Import/Export Wizard and PostgreSQL for days (but it would be nice if those tools worked!)

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.