Migration Of Database From Postgres to MS SQL Servr

Question

I am tasked with migrating some databases from Postgres to MS SQL Server.

I was trying to use the SQL Server Import and Export Wizard as per the link here, https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-a-postgresql-data-source-sql-server-import-and-export-wizard?view=sql-server-ver15.

I was selecting: "SQL Server Native Client 11.0" as the Destination, and "Copy data from one or more tables or views".

I am trying to migrate the data into an existing table.

However when I try and do a migration I get the following error:

I am not a DBA and my Postgres and SQL skills are mediocre at best, so any advice would be greatly appreciated!

I'm guessing you need to spend some time here [Column mapping](https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/column-mappings-sql-server-import-and-export-wizard?view=sql-server-ver15). — Adrian Klaver, Mar 20 '21 at 15:39
It seems the ODBC meta data isn't mapping as expected. See [this question](https://stackoverflow.com/questions/3711028/migrating-from-postgres-to-sql-server-2008) and [blog post](https://learn.microsoft.com/en-us/archive/blogs/dataaccesstechnologies/sql-server-import-export-wizard-fails-while-trying-to-retrieve-the-data-from-pervasive-database). The location of the ProviderDescriptors.xml file is probably under your SSMS installation folder. — Dan Guzman, Mar 20 '21 at 15:41

Dan Guzman · Answer 1 · 2021-03-20T17:42:23.493

The meta data returned by the PostgreSQL ODBC driver isn't mapped correctly by the SSMS ProviderDescriptors.xml file. This file is located in directory "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\ProviderDescriptors" on my SSMS installation.

Using information gleaned from this question and MS blog post, I was able to successfully import from PostgreSQL with the System.Data.Odbc.OdbcConnection ProviderDescriptor config section below. I changed the MaximumLengthColumnName, NumericPrecisionColumnName, and NumericScaleColumnName attribute values in the System.Data.Odbc.OdbcConnection ColumnSchemaAttributes to match the actual names returned by the PosgreSQL ODBC driver (LENGTH, and PRECISION, SCALE).

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

    <dtm:SchemaNames
        TablesSchemaName="Tables"
        ColumnsSchemaName="Columns" 
        ViewsSchemaName="Views" 
    />

    <dtm:TableSchemaAttributes
        TableCatalogColumnName="TABLE_CAT"
        TableSchemaColumnName="TABLE_SCHEM"
        TableNameColumnName="TABLE_NAME"
        TableTypeColumnName="TABLE_TYPE"
        TableDescriptor="TABLE"
        ViewDescriptor="VIEW"
        SynonymDescriptor ="SYNONYM"
        NumberOfTableRestrictions="3"
    />

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

    <dtm:Literals
        PrefixQualifier="&quot;"
        SuffixQualifier="&quot;"
        CatalogSeparator="."
        SchemaSeparator="."
    />
</dtm:ProviderDescriptor>

You may want to save the original ProviderDescriptors.xml file and revert back to it once your import tasks are completed.

Migration Of Database From Postgres to MS SQL Servr

1 Answers1