Re: Delete from Oracle table based on IDs in a SQL table

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@xxxxxxxxx> wrote in message news:1131751053.469790.214100@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:

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.


.

Relevant Pages

  • Re: Current System as Global Variable
    ... such as ODBC versus OLE-DB or the MS versus Oracle flavour. ... Global Variables and SQL statements in DTS ... The OLE DB selected was Microsoft OLE DB provider for SQL Server> and have tried the same query as before in the SQL Exec tas kand it reports> the same error u discussed. ...
    (microsoft.public.sqlserver.dts)
  • Re: for the love of all things holy (and appending a parameter to an Oracle query)
    ... Now use this in the SourceSQLStatement of the data pump task that takes the ... You can use (if Oracle supports) the? ... Global Variables and SQL statements in DTS ...
    (microsoft.public.sqlserver.dts)
  • Re: Limit of 1050 columns for ANSI joins
    ... comprehensive than Oracle SQL. ... I'm trying not to have any auto-generated SQL on my system, ... Index your schema properly and think about the proper design. ...
    (comp.databases.oracle.server)
  • Re: I cant find a SETUP.EXE in the SQL Plus Client ??
    ... someone else's server for testing some SELECT statements I'm ... free client because I don't own the Oracle license. ... SQL statements. ... There is no documentation with that download. ...
    (comp.databases.oracle.tools)
  • Re: How to convince my customer to use SQL2005 instaed of Oracle10g
    ... the old Sql versus Oracle debate. ... If I were you I would focus one SQL Server 2005 clearest advantages ... You mentioned Availability, so I assume your looking into HA (high ...
    (microsoft.public.sqlserver)