SQL SSIS import error: PROGRESS openedge wire protocol column in table has value exeeding its max length or precision

Question

I'm importing data from a progress database.

I am getting the following error:

Progress openedge wire protocol column in table has value exeeding its max length or precision

Is there a way to specify a specific length of the select column's data in the select statement?

For example: SELECT SUBSTRING(EMAIL,15) FROM SQL92.PROGRESSTABLE

SUBSTRING does give me the substring of a valid field value, but still fails with the above error when the dataset hits the "dirty" row.

I don't have access to the Progress database, so I can't run the progress DBTool to fix data.

The same kind of question was asked here, but the solution never posted. Can I make an IDataReader ignore column length definitions?

Answer 1

The answer is here:

ODBC Error "Column x in table y has value exceeding its max length or precision"

Use this curly brace syntax to run a native RDBMS (Progress) function and fix the data before it hits ODBC:

SELECT  { fn CONVERT(SUBSTRING( EMAIL,1,15) , SQL_VARCHAR) } 
FROM SQL92.PROGRESSTABLE

I can't believe people choose to use a database that so easily allows corrupt data.

As some background, you will likely encounter two kinds of errors if using SSIS against Progress:

The data type of "output column "xyz" (n)" does not match the data type "System.Decimal" of the source column "xyz"

(Could be any data type)

I guess this means that the data type has been automatically changed behind the scenes by Progress. It differs to the one saved in SSIS which of course it doesn't like.

The short term solution is to open the package and refresh metadata by double clicking the source

The other error is:

Column xxx in table yyy has value exceeding its max length or precision.

Which means for example there is data that is 371 chars long in the database but the data dicitionary says its 324 chars long.

The long term solution to both of these is to wrap everything in a similar construct to above - cast it before it gets to the ODBC driver to get a consistent data type. It will of course truncate but that's probably better than it failing.

Answer 2

Data length for SQL data retrieved from an ODBC connection is determined by "Width" property of the Progress database fields. This can be accessed through "Progress Data Dictionary" by selecting the table that you want, then choosing in menu bar "Options", "Adjust Field Width". In CHAR fields normally the "Width" property is defined with a value that correspond to twice "Format" width.

For example : a field FOO, type CHAR, format "x(20)" by default has a 40 SQL Width value, twice as original format size.

Despite you can write data with length greater than 20 chars in a CHAR field formatted as "x(20)" as long as Progress don't care on what amount of data stored in his fields and "Format" phrase is only for display purposes (within his data type size limits, of course), it limits data length for SQL connections with a hard limit, such as Oracle does. In other words, for instance, as long as you can't write N + n chars in a Oracle database field defined as N length you can't retrieve data from a Progress database through an ODBC connection if they fields are defined as format "x(N)" and data stored on it exceeds, tipically, 2N.

If you don't have access to Progress database you may contact the person who's responsible for data stored on the Progress database and ask him to resize the fields on database schema to match the full amount of data stored. The programs whose is storing data on database may be discordant with database field size. Otherwise you won't be able to retrieve data from tables that have fields which stores more than "Width" chars.