Hi All,
I would appreciate it if I could get some opinions on my proposed solution.
On a nightly basis I have to execute a batch job that would does the
following

1) Execute a Stored Procedure to return a recordset of IDs from a SQL2K DB
(Can be up to 750,000m rows) and insert these ids into a Oracle database.
2) Then call a oracle procedure that will validate these ids against its
tables, return any ids and exceptions message in a ref cursors where the ids
do not exist. This can be 100k + rows.
3) Insert these exceptions into a exception table in our SQL2K DB.
4) Execute another oracle procedure that transfroms the data into another
oracle table (a driver table) and return any exceptions.
5) Capture these exceptions and insert in a SQL DB.
6) Execute 15 different select statements against 15 different tables
filtering by the driver table created in step 4. Some of these select
statements can return upto 3m rows.

My solution is to create a DTS package and schedule it to run nightly. I am
using transfrom data task for step 1, (although I have read it might be
quicker to use linkedservers and write a distributed query to insert the ids
into the oracle table.) The only way I can think of running the Oracle
Procedure is by writing a ActiveX script using ADO to execute the Oracle
Procedure and returns the exception Ref Cursor to a recordset. I then loop
throught the recordset and insert the exceptions one at a time into our SQL
DB. This could potenially be very slow. Do I have any other options ?

When this ActiveX script completes I call another ActiveX script for step 4
to build the driver table. I am thinking of combining the two activeX
scripts into 1.
Finally I have set up 15 Transfrom Data tasks for each table I need to get
data from. The source is a SQL statement against the oracle tables filtered
by the driver table created in step 4.

So I really am just interested in peoples opinions on this. Is there a
better solution out there as I feel performance is going to be a problem
with potentiallly 10m+ rows going back and forward nightly. Any opinions
would be welcome. I hope the above makes sence.

Cheers
Adrian

Thanks !


"Ado" <Adrian_donnelly (AT) hotmail (DOT) com> wrote