Monday, March 12, 2012

Lookup on AS400

Hi everybody,

I'm new in SSIS and I'm trying to activate a lookup on a table reside on DB2/AS400. Iget the following message when I try to join the input column to the lookup column:

TITLE: Microsoft Visual Studio

The following columns cannot be mapped:

[STOREK_S, STOREK_S]

One or more columns do not have supported data types, or their data types do not match.


BUTTONS:

OK

I know both columns have the same format on the DB2 table.

What I'm doing wrong ?

P.S: I'm using the IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider as connection.

If I try with the Microsoft OLE DB Provider for DB2, I was unable to get the list of my table on teh AS400.

Could you help me ?

Well, are you taking your dataflow and looking up a column against a DB2 source? In that case, the incoming data type may not match the data type in DB2. They could be different. You'll have to verify the DB2 data type and compare it against the incoming column.

Also, I've used both the IBM DB2 iSeries and Microsoft OLE DB for DB2 connectors and have them both working... In the MS provider, ensure that your package collection (library) is set to the same as the default schema. The catalog entry is for the database name. This helped me, and if that doesn't resolve your issue, please provide the error that you get when you try to select a table in the drop-down box.

I'd like to help you through both of your issues and get more DB2 documentation out on this forum!

Thanks,
Phil|||

Yes I'm using a Dataflow where I got 3 box on it: A DataSource Reader, a lookup and a OLE DB Destination.

My DataSource Reader used a ODBC connection (iSserie V5R4) comming with the IBM iSerie client. An SQL extract the data and it work fine if I connect it to the OLE DB Destination.

If I try to insert a lookup between the DataSource Reader and OLE DB destination, I got problem with the lookup.

The lookup use the Microsoft OLE DB provider for DB2(let focus on this connection first). I got correctly my list of table on the AS400. I got the correct list of the available Input Columns and available Lookup Columns. But when I try to specify the join columns I got an error.

The one mention in the initial request.

At the point of view of the ETL the column STOREK_S on the available Input Columns is in format [DT_NUMERIC], length: 0, scale: 0, Precision: 8, Source Component : DataSource Reader

and the available Lookup Columns, STOREK_S is in the format [DT_DECIMAL], length: 0, scale: 0, Precision: 8, Code page: (blank).

Like I also mention at the point of view of the DB2 AS400 the colomns STOREK_L got the same format.

What could be done to solve this problem ?

|||Right before the lookup, use a derived column transformation to cast the input column, STOREK_L to DT_DECIMAL from DT_NUMERIC.

Try this as your expression: (DT_DECIMAL,8)STOREK_L

I'm a little surprised to see precision of 8 with no scale, though, on your lookup column. Doesn't make sense.|||

I also sruprised by the precision of 8 with no scale, but it's the default mapping I got.

Could I change it in any way ?

Doing what you ask for I always get error in mapping but now the field derived is in the format of [DT_DECIMAL] with length: 0, scale:8 and precision : 0, that not map with the [DT_DECIMAL] with length: 0, scale:0 and precision : 8

|||

desilets wrote:

I also sruprised by the precision of 8 with no scale, but it's the default mapping I got.

Could I change it in any way ?

Doing what you ask for I always get error in mapping but now the field derived is in the format of [DT_DECIMAL] with length: 0, scale:8 and precision : 0, that not map with the [DT_DECIMAL] with length: 0, scale:0 and precision : 8

But do you get an error?|||Sorry, I forgot to write it. But I still get the problem when I try to join the columns for lookup|||The lookup field isn't by chance a packed decimal field, is it?|||Yes it is. But the lookup is always not working.|||

Phil,

Do you know if some special software was installl on the AS400 to allow the OLE to recognize the right structure of the table ? (somebody working with me, tell me something about a software, I verify this way).

|||

desilets wrote:

Yes it is. But the lookup is always not working.

I don't think packed decimals (and perhaps zoned decimals) are supported by the Microsoft OLEDB for DB2 driver. You can try a new iSeries driver, or you can try the "DB2 Connect" software also from IBM.

http://www-128.ibm.com/developerworks/forums/dw_thread.jsp?forum=292&thread=135790&cat=5

No comments:

Post a Comment