Advise: DTS

[B]

Version: SQL Server 7.0
App: MS Access 2000

I created DTS to move records from MySQL to SQL Server (approx over 500,000+
per months dataset) for purpose of canned and adhoc reporting. The app
have a simple interface using MS Access where user will input the timeframe
of the dataset they will process.

Both servers are physically located in the same bldg from another state.
Previous to MySQL, they send us cd in text format and my app runs the DTS
using a BAT file containing the DTSRUN command. But I would like stay away
from the DTSRUN since they take awhile to load to the server, plus it runs
on background on client pc. I wanted a functionality where it will run on
the server without user supervision, and they can go about their business
and do something else. In other words, let the server do majority of the
work.

Now that MySQL came into the picture, the Network Support created a filedsn
where sqlserver sits and I use that as a reference on my DTS (using UNC,
\\myserver\file$\sample.dsn)

The problem:
1) I work from home and when creating and running this DTS's, it takes a
while to process;
2) When specifying the UNC path on DTS and saved, for some reason it
switches to the User/System DSN section;
3) I also tested from office with the same result (i connect using cable
modem)

Now the only way I can think of to solve this problem:
a) save the DTS as a file and send it over to Network Support and have them
load it up and manually change the source location OR have them modify the
DTS and change the source location
b) then create a DTS job

The person who will be running this DTS might be running it from home or
office. Also, is there a way to automate the DTS job using an SP? I wanted
to make it as easier as possible for the end user, where all they have to do
is enable the job and run only for this time and for this package.

TIA!
Bob

[John Bell]

Hi

You can schedule the DTS package to run periodically on your SQL Server.
Look at sp_add_job and associated procedures as descibed in the topic "How
to create a job (Transact-SQL)" in Books online or the topic "How to create
a job (Enterprise Manager)" to do it manually through EM.

If you don't want run a job without a schedule look at the "Running Jobs"
topic for sp_start_job.

John

"B" <no_spam@no_spam.com> wrote in message
news:41**********************@news.rcn.com...

Version: SQL Server 7.0
App: MS Access 2000

I created DTS to move records from MySQL to SQL Server (approx over 500,000+ per months dataset) for purpose of canned and adhoc reporting. The app
have a simple interface using MS Access where user will input the timeframe of the dataset they will process.

Both servers are physically located in the same bldg from another state.
Previous to MySQL, they send us cd in text format and my app runs the DTS
using a BAT file containing the DTSRUN command. But I would like stay away from the DTSRUN since they take awhile to load to the server, plus it runs
on background on client pc. I wanted a functionality where it will run on
the server without user supervision, and they can go about their business
and do something else. In other words, let the server do majority of the
work.

Now that MySQL came into the picture, the Network Support created a filedsn where sqlserver sits and I use that as a reference on my DTS (using UNC,
\\myserver\file$\sample.dsn)

The problem:
1) I work from home and when creating and running this DTS's, it takes a
while to process;
2) When specifying the UNC path on DTS and saved, for some reason it
switches to the User/System DSN section;
3) I also tested from office with the same result (i connect using cable
modem)

Now the only way I can think of to solve this problem:
a) save the DTS as a file and send it over to Network Support and have them load it up and manually change the source location OR have them modify the
DTS and change the source location
b) then create a DTS job

The person who will be running this DTS might be running it from home or
office. Also, is there a way to automate the DTS job using an SP? I wanted to make it as easier as possible for the end user, where all they have to do is enable the job and run only for this time and for this package.

TIA!
Bob