Import ODBC (DBIntegrator) data into SQL Server 2014 Database

Question

Post reply

Import ODBC (DBIntegrator) data into SQL Server 2014 Database

David Kyle

SSChasing Mays

Points: 617
More actions
May 7, 2015 at 7:32 am

#296712

I am trying to copy data from a DBIntegrator ODBC source into SQL Server using the Import Wizard.
When I select a table on the Source list and click on <Edit Mappings...> , I get an error message saying column attribute "ORDINAL_POSITION" is not valid and asking me to check ProviderDescriptors.xml.
I have tried changing the value of OrdinalPositionColumnName to "ORDINAL", "ORDINALPOSITION", "POSITION", "ID" and "INDEX" to no avail.
Does anyone know how I can find the correct value for the column ordinal position in my data source?
I have tried running Windbg and attaching it to the wizard, but there is nothing in the locals window. I don't know enough about Windbg to get the information I need.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

g.britton SSChampion Points: 13863 More actions · Answer 1

Can you post the segment of XML that covers that field?

Gerald Britton, Pluralsight courses

David Kyle SSChasing Mays Points: 617 More actions · Answer 2

Hi Gerald,

The default ProviderDescriptors.xml for ODBC is:

<dtm:ProviderDescriptorSourceType="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 = "COLUMN_SIZE"

NumericPrecisionColumnName = "COLUMN_SIZE"

NumericScaleColumnName = "DECIMAL_DIGITS"

NullableColumnName="NULLABLE"

NumberOfColumnRestrictions="4"

/>

<dtm:Literals

PrefixQualifier="""

SuffixQualifier="""

CatalogSeparator="."

SchemaSeparator="."

/>

</dtm:ProviderDescriptor>

The original error message complained about ORDINAL_POSITION, COLUMN_SIZE (twice) and DECIMAL_DIGITS. I have changed the following and it is only complaining about ORDINAL_POSITION (or whatever column name I have entered for OrdinalPositionColumnName):

MaximumLengthColumnName = "LENGTH"

NumericPrecisionColumnName = "PRECISION"

NumericScaleColumnName = "SCALE"

I believe that, if I can see the details provided by the ODBC driver, I could identify the column position column name.

amber22 Valued Member Points: 55 More actions · Answer 3

Did you ever get this figured out? I am running into this same problem, where it doesn't allow me to continue because it errors out on Ordinal_Position.

David Kyle SSChasing Mays Points: 617 More actions · Answer 4

I never resolved it. I worked around it.

Instead of using data access of 'Table or View' in the data source, I used 'SQL command' with the command = 'select * from <table>'.

Thirumalai CB Valued Member Points: 58 More actions · Answer 5

I had the same issue. The ProviderDescriptors.xml reside in both under Program Files and Program Files x86. The program was picking up the file from x86. After changing this it worked. Just posting it for someone who is searching this.