Very Computer

Hi,

I have a DTS package which transfers data from an Oracle database to an Ms
SQL 2000 database.

It works like a charm.

However, once a week on a "non-standard" time the Oracle database is
updated, and I need to run the DTS package.

How can I make this event driven?

I can have a table created which will put an entry in the Oracle database
such as data week01 updated.

Can I then base the execution of DTS based on the existance of an entry in a
table on Oracle?

I then need my DTS package to run an SQL query which is the following:

select SALES_WEEK_ID "Sales Week",

LOC_ID "Location",

.......

from loc_item_week

where sales_week_id in('200140')

order by sales_week_id,

loc_id,

THis also works like a charm, but how can I make the sales_week_id based
upon a table in SQL, I can then build a table in SQL, if dat is 01012002
then sales_week_id="200201'

Or any better way to make the sales_week_id dynamical.

Is this possible in DTS?

Thanks in advance,

Eduard de Vries

Hi,

I actually realise that it is a bit of a big question.
I am looking for a direction, so I know where to start searching, should I
use global variables for this?
Or where should I start?

Thanks in advance,

Eduard

Quote:>Hi,

>I have a DTS package which transfers data from an Oracle database to an Ms
>SQL 2000 database.

>It works like a charm.

>However, once a week on a "non-standard" time the Oracle database is
>updated, and I need to run the DTS package.

>How can I make this event driven?

The best method would be to use what ever the oracle update process
initiator is to call the DTS package once the oracle load has completed.
The DTS package could run on the Oracle server instead of the SQL
server, if this makes it easier (assuming Oracle is running on Windows).

Some alternatives could include use of a trigger on the Oracle system to
call and external program that directly executes or can execute a SQL
server command such as sp_start_job.

Failing that some kind of regularly scheduled job on the SQL Server
using a linked server to query Oracle and detect the changes. Presumably
there must be some predictability in when the Oracle load comes in, to
enable you set the schedule as appropriate.

Quote:

>Can I then base the execution of DTS based on the existance of an entry in a
>table on Oracle?

I think this is what I'm suggesting with the linked server above.

<snip>

Quote:

>THis also works like a charm, but how can I make the sales_week_id based
>upon a table in SQL, I can then build a table in SQL, if dat is 01012002
>then sales_week_id="200201'

If the data can be derived using the current date why can't you just use
the normal (T-)SQL date functions such as CURRENT_TIMESTAMP and CONVERT.

You use an ActiveX Script Task to derive the date in the correct format
from what ever source you require, store it in a global variable, and
then pass this into your Execute SQL Task or DataPump Task using the
Parameters functionality in SQL 2000.

As you acknowledged this is a bit of a wide topic which I always find
difficult to answer without being able to understand the full scope and
environment. Targeted questions are always much easier. Hope this is of
some help.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com