Search code examples
ssisopenedgeprogress-db

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

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?

Solution

  • 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.