469,625 Members | 1,094 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

Run DTS in safe way.

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)
Jul 23 '05 #1
2 2752

"Piotr Lipski" <pi**********@nie.poczta.spamuj.onet.bo.pl.w.ryj > wrote in
message news:d4**********@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
Jul 23 '05 #2
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

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

42 posts views Thread by Irmen de Jong | last post: by
9 posts views Thread by Jody Gelowitz | last post: by
1 post views Thread by jecheney | last post: by
4 posts views Thread by George2 | last post: by
3 posts views Thread by =?Utf-8?B?anBhdHJjaWs=?= | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.