Lookup Transformation: How can I join tables in different databases

I want to join tables that reside in different databases (same instance). The Lookup object only lets me select from one data source. Is there anyway to lookup using more than one data source? I can write a SQL query to lookup across databases.

Is this a feature that is being added to future releases?

I appreciate your help

-Marcus
Are these SQL Server databases? If so you can create a view in one database that selects data from another - thus making it appear as though the data is all in the same DB.

Voila!

-Jamie|||lol... didn't think of that one...

Thanks :)|||Still onthe lookup subject, I have noticed that if I write a simpley query in the box "User results of an SQL query" say select * from DB1.dbo.tablea, DB2.dbo.tableb, I can then click the "Build Query" button and hey presto both tables are then available for me to work with even thought they are from different databases. I do notice that the top left corner of the table boxes have an arrow.

However if I go straight to "Build Query", add my first table by right clicking and selecting "add table" there is no arrow in the top left corner of the table box. I can then modify the sql statment manually to include the table from the other database. This table then appears in the top window with an arrow in the top left corner of it's box.

Is this a bug? should there actually be an optin to add a table from an alternative database and it's missing? As detailed above I can manually add the tables and the tool recognisines them.

Has anyone else seen this? Has this been fixed in later builds? I'm using Junes.

Thanks|||

What you have observed is not a bug.

We do supply an option to add tables. But we only list tables in the current database context. Tables in other databases have to be added manually. This is the behavior in June CTP and are not changed since then.

|||

Do you know if this will be changed in future CTP's?

Thanks

|||

I do not think so.

But please feel free to open a DCR via BetaPlace.

|||What about MS Access using DAO? In DAO you use something like this.

CDaoRecordset rset(&db);
rset.Open(dbOpenSnapshot, SQLquery, dbReadOnly);

This does not allow for binding to more than one database at the time. Therefore, how can you do a join query between two tables residing in different databases?

Thank you.

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