DTS - Lookups 1

[Nambiti]

Hi - I need some help using lookups in DTS(SQL 2000). I have 2 tables. Source is Oracle DB with a table called Titles and Destination is SQL Server 2000 with table called Titles with no records. I have a excel spreadsheet called SBNList with one filed called SBN that has 250 SBN numbers. I need to lookup the 500,000+ SBN numbers in the Source table and only return data that matches the spreadsheet SBN numbers.

 

[dbomrrsm]

I would use a DTS Excel to SQL Server transform data task to import the Excel data into a SQL Server table then on completion of this do an Oracle to SQL Server transform data task and write a query that only imports the data from oracle table where ID in that table - something like

SELECT *
FROM ORA_TABLE
WHERE SBN_NUMBER IN (SELECT SBN NUMBER FROM SQL_S_TABLE)

[blue]DBomrrsm[/blue]

 

[Nambiti]

This works fine if the table exists in the same connection. The problem is that I can't import the temporary table into Oracle. My query selects data from an Oracle table.

 

[dbomrrsm]

import the excel data into SQL Server then reference it in the query pulling data from oracle.

what the above code is saying is to pull the data from oracle where the oracle data has a sbn number in a table in SQL Server that you have created from importing the excel data.

import the excel data into a table called SQL_S_TABLE.

then do:

SELECT blah
from oracletable_title
where oracletable_title.sbn IN
(Select sbn from SQL_S_TABLE)

[blue]DBomrrsm[/blue]

 

[yksvaan]

dbomrrsm, how would you do that when they are in different connection ?

The suggestion to load the excel to sql table is great.


One way to have single query is to use oledb with OPENDATASOURCE function.


But, if you are interested in the Data Driven Query, I recommend SQL Books Online (Sql documentation that came with installation, and/or from ms site) following chapters

Building a Data Driven Query
Data Driven Query Example: Changing Customer Accounts
Data Flow in a Data Driven Query Task

You're going to have three connections. "Binding" refers to your destination,this is a bit confusing. IYC, you just need the insert return code and "not to do anything" rc, which is DTSTransformStat_SkipRow

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[dbomrrsm]

one connection from excel to sql server brings in the excel data into a sql server table - then on completion of this task run the oracle to sql server connection which can then use the newly populated table (from excel) that is now in sql server.

[blue]DBomrrsm[/blue]

 

[Nambiti]

All of this works when the main table and the excel imported table are using the same connection. When trying to reference the new SQL table from the Oracle connection it can't find that table as it's not part of the Oracle connection.

 

[dbomrrsm]

once the new table is in SQL Server (from excel) you should be able to double click the transform task between the oracle and sql server conncetion and reference this new table in the define query pane.

You wont see this table in oracle as its in sql server but it can be used as a reference to only bring in rows from oracle where they have an id the same as the referenced table in sql server.

[blue]DBomrrsm[/blue]

 

[Nambiti]

I get an ORAOLEDB error saying that it can't find that SQL table.

 

[dbomrrsm]

do you have access to write to the oracle DB - in this case put the sql new table or the excel data into the oracle db so you can refernce it - otherwise you will need to bring all the oracle table in and use the new sql server table to then filter the records you need - sorry about leading you down a blind alley !

[blue]DBomrrsm[/blue]

 

[Nambiti]

Thought as much. No I can't write to the Oracle DB. Your tips were still helpful as it works in SQL using the same connection.

Hopefully I can try and do this using lookups and some VB script. I don't want to have to import all the data as this would take too long.

 

[dbomrrsm]

good luck - post back if you find a VB soulution - I would be interested in that.

[blue]DBomrrsm[/blue]