I have a database sitting on SQL Server 2000. Within the database, I have a list of specific customers and their 'keys'. What I am wanting to do is get additional information (Data) from an Oracle Database, and am wanting to pass in the 'keys' from SQL Server to Oracle to retrieve the data

For example, here would be the example SQL passed to Oracle

SELECT CUST_KEY, CUST_NBR, CUST NAM
FROM CUST_TABL
WHERE CUST_KEY IN (SELECT CUST_KEY FROM MASTR_CUST_LIST WHERE CUST_ACTIVE = 'YES'

The SQL Statement in the Sub-Query is from SQL Server, but the rest gets passed to Oracle

I have read articles like http://www.sqldts.com/default.aspx?205,1 but unfortunately does not provide me with a clear direction of how to implement this in a SQL Server DTS Package

Any suggestions/help would be greatly appreciated

Regards

Diablo

One very quick way would be to use a linked server of Oracle to SQL Server.
Use OPENQUERY to do your SELECT from Oracle.

Another way would be to pump the "Keys" table to Oracle and then do the join
there retrieving the data to wherever you want.


--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Diablo" <anonymous (AT) discussions (DOT) microsoft.com> wrote

get additional information (Data) from an Oracle Database, and am wanting to
pass in the 'keys' from SQL Server to Oracle to retrieve the data.
this in a SQL Server DTS Package.

OK Ideally you would like all the data in one place so it makes Querying
easy. Linked Servers give you this but they can be clunky (IMHO). If you
pump over the Keys from your SQL Server table to a table in Oracle (working
table just for this purpose) you can then do the Query all on the Oracle
side and use that to gain the data. You can then use this Query as the
source to your DataPump task.



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Diablo" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Allan, the problem I have is that the Oracle Database is our Data Warehouse (Data Mart), and having a table added would not be justifiable. For this reason, I am storing the customers of interest in a SQL Server table (about 120 customers, and growing), whereas the Data Warehouse has over 600,000 customers in total. Managing the list in SQL Server has already been established, but retrieving the detail data for these 120 customers is where my problem exists.

Is there not an easy way of 'SELECTing' the customers (Keys) from SQL Server, and pass those Customer Keys to Oracle within the DTS Package?

Thanks for your time.

Use a linked server if creating a new table on the warehouse server is not
an option.

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Diablo" <anonymous (AT) discussions (DOT) microsoft.com> wrote

For this reason, I am storing the customers of interest in a SQL Server
table (about 120 customers, and growing), whereas the Data Warehouse has
over 600,000 customers in total. Managing the list in SQL Server has already
been established, but retrieving the detail data for these 120 customers is
where my problem exists.