SEO

I have two servers: progress as transational server and mssql as warehouse
server.

I did DTS that "pumps" data from progress to mssql (via ODBC). Copying the
data has to be done once a day, but sometimes there is a need to do it on
user's demand.

I'd like to ensure that I properly understood the method of running DTS I've
just found using Google.

As I can see, there are at least two methods (except of scheduling):

- using dtsrun - which requires user running the DTS to have admin
privileges to use xp_smdshell

- using sp_start_job - which requires creating a job prior to running the
DTS.

I think of using the second one as it seems to be more secure - am I right?
Are there any hidden traps? What else should I do?

--
PL
(remove "nie.spamuj.bo.w.ryj" from my email address)

"Piotr Lipski" <piotr.lipski@nie.poczta.spamuj.onet.bo.pl.w.ryj > wrote in
message news:d42s9j$f6n$1@news.onet.pl...
>I have two servers: progress as transational server and mssql as warehouse
> server.
>
> I did DTS that "pumps" data from progress to mssql (via ODBC). Copying the
> data has to be done once a day, but sometimes there is a need to do it on
> user's demand.
>
> I'd like to ensure that I properly understood the method of running DTS
> I've
> just found using Google.
>
> As I can see, there are at least two methods (except of scheduling):
>
> - using dtsrun - which requires user running the DTS to have admin
> privileges to use xp_smdshell
>
> - using sp_start_job - which requires creating a job prior to running the
> DTS.
>
> I think of using the second one as it seems to be more secure - am I
> right?
> Are there any hidden traps? What else should I do?
>
> --
> PL
> (remove "nie.spamuj.bo.w.ryj" from my email address)

There are a number of ways to run a package:

http://www.sqldts.com/default.aspx?104

To run xp_cmdshell, you don't need sysadmin privileges if you configure a
proxy account - see xp_sqlagent_proxy_account in Books Online - although
even using a proxy, it may still be too risky in some environments.
sp_start_job is also a problem, because you can only start a job that you
own yourself (unless you're a sysadmin, of course).

One solution is that a user INSERTs into a queue/request table to indicate
that they want to run the package. A scheduled job can then run every few
seconds/minutes/hours, and if the row is in the table (or the flag column is
set etc), then it runs the package. This avoids giving any special
permissions to users.

Simon

I would definatly reccomend using the SQL server agent and SP_Start_Job
as you get all the logging features for the agent - you get protection
against running the same DTS package 2 times at once. You can get
e-mail notification when the job has finished or failed or whatever.

I also like the idea of having a requests table - i may consider
however create a trigger on the table that executes the sp_start_job
stored procedure instead of creating a polling process. It all depends
on how much complexity you can afford and how much tracking / logging
you require.

One of my customers has many DTS packages used for loading data in to a
data warehouse. When a busniess manager receives some new data from a
data provider they simply place the data on a shared drive and run the
DTS package. I created a simple ASP.NET web application that lists the
DTS packages available and shows the status of each as in the
enterprise manager..

in 2 days i had a fully functional system with simple access
permissions and admin screens. If you want to a similar thing look for
these stored procedures.

msdb.dbo.sp_help_job
msdb.dbo.sp_start_job
msdb.dbo.sp_stop_job