SQL Server Import Export Wizard fails while trying to retrieve the data from Pervasive Database
When trying to import data from Pervasive DB to SQL Server 2008 DB using import/ export wizard we get the following error message :-
"Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider."
When I clicked on Edit Mapping button, the Import Export Wizard failed with the below error message
===================================
Column information for the source and destination data could not be retrieved.
"Billing" -> [dbo].[Billing]:
- Cannot find column -1.
(SQL Server Import and Export Wizard)
===================================
Cannot find column -1. (System.Data)
------------------------------
Program Location:
at System.Data.DataColumnCollection.get_Item(Int32 index)
at System.Data.DataRow.get_Item(Int32 columnIndex)
at Microsoft.DataTransformationServices.Controls.ProviderInfos.MetadataLoader.LoadColumnsFromTable(IDbConnection myConnection, String[] strRestrictions)
at Microsoft.SqlServer.Dts.DtsWizard.OLEDBHelpers.LoadColumnsFromTable(MetadataLoader metadataLoader, IDbConnection myConnection, String[] strRestrictions, DataSourceInfo dsi)
at Microsoft.SqlServer.Dts.DtsWizard.TransformInfo.PopulateDbSourceColumnInfoFromDB(IDbConnection mySourceConnection)
at Microsoft.SqlServer.Dts.DtsWizard.TransformInfo.PopulateDbSourceColumnInfo(IDbConnection mySourceConnection, ColumnInfoCollection& sourceColInfos)
After doing a live debug by attaching the wizard to Windbg, We figured out that the schema information is not correctly matched between the source and destination types
SSIS requires the below column information for a DataTable from System.Data.ODBC DBConnection.GetSchema for mapping the source and destination columns.
COLUMN_NAME
ORDINAL_POSITION
DATA_TYPE
IS_NULLABLE
CHARACTER_MAXIMUM_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
We get the values correctly mapped for the below columns
COLUMN_NAME
ORDINAL_POSITION
DATA_TYPE
IS_NULLABLE
However, we don’t see the values get assigned to the below columns from the metadata which leads to the failure of SSIS package.
CHARACTER_MAXIMUM_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
When we looked into pervasive ODBC driver schema information, we got some similar results for the above columns as,
CHAR_OCTET_LENGTH
PRECISION
SCALE
When the ODBC metadata is being loaded… Pervasive only implemented these column attributes:
SSIS is looking for these (the defaults for ODBC providers). Column_Size is not implemented by this ODBC driver and Decimal_Digits isn’t either. When not found, we get the -1 error
In order to resolve this issue you need to configure ProviderDescriptors.xml file which is present in the below path
C:\Program Files\Microsoft SQL Server\100\DTS\ProviderDescriptors\ProviderDescriptors.xml
For the generic .NET ODBC provider (System.Data.Odbc.OdbcConnection), we should be looking for:
COLUMN_SIZE (for both the character length and the precision)
DECIMAL_DIGITS (for the scale)
We need to modify the mapping for the generic provider as shown below: (Please take a backup copy of ProviderDescriptiors.xml file before making any changes)
<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="""
SuffixQualifier="""
CatalogSeparator="."
SchemaSeparator="."
/>
</dtm:ProviderDescriptor>
After modifying the mapping file, re-run the wizard and this should work.
Author : Praveen (MSFT), SQL Developer Technical Lead , Microsoft
Reviewed by : Jason(MSFT), SQL Escalation Services, Microsoft
Comments
Anonymous
November 24, 2010
I'm experiencing the same problem when retrieving data from Cobol tables with the Transoft Windows ODBC Driver. I've also attached Windbg to DTSWizard, however cannot determine, as you have done, how schema information is incorrectly matched. Perhaps you could elaborate more?Anonymous
November 24, 2010
I'm experiencing the same problem when retrieving data from Cobol tables with the Transoft Windows ODBC Driver. I've also attached Windbg to DTSWizard, however cannot determine, as you have done, how schema information is incorrectly matched. Perhaps you could elaborate more?Anonymous
February 24, 2013
Oh, by the way, that's fix an issue for an import of data from a PostgreSQL 9.x to SQLSERVER 2008 with the PostgreSQL unicode ODBC Driver.Anonymous
March 09, 2013
please ans, when i importing access databse to sql server 2008 R2, following error occured, Could not retrieve table list.
ADDITIONAL INFORMATION: The type initializer for 'Microsoft.DataTransformationServices.Controls.ProviderInfos.MetadataLoader' threw an exception. (Microsoft.DataTransformationServices.Controls)
Anonymous
April 09, 2014
This is worked for me, thanks a lot..I am impressedAnonymous
December 15, 2014
I changed the ProviderDescriptors.xml as mentioned as above but the problem is still exist. Please help.- Anonymous
November 06, 2018
If you installed the 64-bit version of SQL Server, the ProviderDescriptors.xml resides in both under Program Files and Program Files (x86). Typically SQL Management Studio is 32bit, and runs the DTS wizard as 32bit. So change the file in the Program Files (x86) tree. Also don't make a copy of the file in the same folder. all files, even if not named *.xml, are loaded and can override your changes.
- Anonymous
Anonymous
January 27, 2015
I did same amendment as mentioned as above. But it still have same errors. Please advise.- Anonymous
May 19, 2016
When I did it, I had about 8 copies of the file in different directories. When I found the right one, it worked for the three items listed. I am still having an issue with the OrdinalPositionColumnName="ORDINAL_POSITION" item.- Anonymous
May 24, 2017
Did you ever figure out what to do with the OrdinalPositionColumnName=”ORDINAL_POSITION” ? I am trying to import from Oracle RDB using ODBC driver and keep getting that error- Anonymous
November 06, 2018
See my above comment regarding the location of the ProviderDescriptors.xml (resides in both under Program Files and Program Files(x86)).
- Anonymous
- Anonymous
- Anonymous
Anonymous
May 10, 2017
Thanks, it was needed for GreenPlum (Postgres) too