I have a requirement to delete some rows in an Oracle table (Oracle 9i)
based on a look-up from a sql table (SQL Server 2000). The following
query is the one I would like to use, utilising linked servers.

delete from ORACLEDS..MYSCHEMA.CUSTOMER
where CUST_ID in
(select cust_id from SQLCustTable)

Unfortunately we cannot use linked servers as the Oracle tables contain
NVARCHAR2 columns which SQL does not understand with any Oracle drivers
(ODBS, OLE DB etc..), although please correct me if I am wrong on this.
We do not have the ability to change the data-types in the Oracle
table.

I have been trying a few different solutions to no avail. So is it
possible to do it using any of the following:

1. Two seperate SQL Tasks. The first querying the SQL data source and
putting the IDs into a global temporary table. The second task using
the Oracle data-source but somehow being able to look at the SQL
temporary table

2. Similar to 1 but putting the IDs into a Global variable rather than
a temp table. Then using an ActiveX script to generate the SQL of the
second task dynamically by looping through the global variable. I have
a feeling this is possible but I do not like the solution so I have not
tried it yet!!

3. A Data Driven task, where the lookup returns multiple rows. I see
lots of examples on how to write activeX scripts to handle a lookup
with multiple columns, but none with multiple rows.

4. Any other method!!


Thanks in advance for your time.

Regards,
Patrick.

Sure

You can have an ExecuteSQL task that grabs the values you need into a GV
rowset. You then use a loop construct and build the DELETE statement.
You pass in to another ExecuteSQL task this time against the Oracle
connection the statement and that should be that.

Here are articles that will help


How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)


Allan


"patrick" <pgorry (AT) gmail (DOT) com> wrote

Thanks Allan,

yes that solution works fine - I have it implemented now. I just
thought it seemed over complicated for what I wanted, but I guess in
the absence of linked servers its the best option.

Thanks for your reply
Patrick.