Decimal datatype conversion from DB2 to SSIS throught Microsoft OLE DB Provider for DB2

Hi All,

Here is a description of the issue I'm facing about decimal datatype conversion from DB2 to SSIS throught Microsoft OLE DB Provider for DB2.

I first discovered it when I tried to use a LookUp trans. and selected a decimal typed field. I was unable to validate it and clicking the OK button threw the error copied below:

TITLE: Microsoft Visual Studio

Error at Data Flow Task [DTS.Pipeline]: The "output column "MFIXFRA" (317)" has a value set for length, precision, scale, or code page that is a value other than zero, but the data type requires the value to be zero.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204019 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

Some workarounds on this issue led me to two other strange behaviours:

- If I create a dataflow task to perform a raw copy of a DB2 table to a brand new table in SQLServer destination (by clicking "new" instead of choosing an existing destination table), generated "create table" script for decimal fields is quite different from source table. For example, a source field declared Dec(15,2) is translated as Dec(29,5), and so on.

- Quite same behaviour if I try to derive a column using as derived column trans. If the source column is a decimal, it's scale and precision are interpreted ramdomly.

Any idea welcome

Andr

Can any expert or Microsoft SSIS team members please respond to this ?

We are looking for some help...

|||Are you seeing this problem with the provider from the SQL Server 2005 Feature Pack? That's the version of the provider with the most testing done with SSIS. You may be able to work around this by changing the decimal column into another type better understood by SSIS. For example, converting the decimal column to another type in your SQL command or if you are using the Import / Export Wizard, you can try modifying the datatype mapping file to get the decimal type to be mapped to another type.|||

Thanks Ted for your input,

Do you have hint if such a thing could come from the OLE DB provider settings.

As we are located in continental Europe, our setting are :

EBCDIC 500 International (for DB2 UDB)

CODEPAGE 1252

Does it ring a bell for you or anyone else ?

Nicolas.

|||Please everybody, we are still looking for some help...