Options for Executing DTS

[Jimmy211]

I’m getting a little frustrated with DTS. After 4 days of trying to solve what I think should be a simple problem, I’m ready to show my ignorance and see if someone here can help me.

The problem:
I have 2 servers, MS SQL Server and DB2. I’ve set up a DTS package that will update tables in my SQL database from DB2. I have another package that writes data from SQL to DB2. I’ve built an MS Access front end project attached to the SQL server and I want it to execute the DTS package when a button is pressed. I’m currently using the Evaluation version of SQL Server.

I’ve discovered my options for firing off a DTS package are the following:
1) Use the VB object model.
2) Use the dtsrun.exe utility, either from a stored procedure or a command prompt.
3) Set up a SQL agent job to run the DTS package on a schedule.

What I would really like to do is get the package to run on the server with minimal installation on the client workstations.

I’d rather not have to install the VB object model on every workstation, which is my big problem with using option #1.
(I did try this option using the article on the sqldts.com website about redistributing DTS with a package found at

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

I couldn’t get the regsvr32.exe to register the dll’s for me on a win2000 box. I kept getting the error “The specified module could not be found.” At any rate, this seems like I shouldn’t have to have that much overhead.)

The problem I’ve found with the dtsrun.exe utility is that using it from a command prompt requires it to be installed on the client, same as the VB object model. I tried setting up a .bat file on the server which would be executed from the client and run the dtsrun utility, with the same problem – it tried to run on the client.

Running the dtsrun utility from a stored procedure works pretty well… if the user has server administrator rights. For obvious reasons I’d rather my users not have those. I tried using the EXEC master..xp_cmdshell 'dtsrun /S /E /N' command in a stored procedure. It worked pretty well.

I tried setting up an SQL server job to run the DTS package, but the most frequently it could be run is every 1 minute. I’d like it to be updated on demand, not according to a schedule.
(I think it was on the sqldts.com website I read about a trick you could use that has the job look to a table, and if it finds a ‘yes’ value in a column, it executes the update. The client application updates that column to the ‘yes’ value when it wants an update run. I’ts a clever approach, and would work if the job could be setup to look every few seconds, instead of a minute.)


So does anyone know of a better approach? I’d like all the software required to run the DTS program be on the server, with minimal client footprint. Is there a way to run a program so it executes on the server from the client? I feel dumb asking that question because it seems like a fundamental tenet of client\server computing. Maybe Microsoft will make this easier in Yukon, but I can’t hold my breath that long.

Thanks in advance for any help.

Jimmy211

 

[rasETL]

I have routinely set up DTS Package runs and BCP runs by creating a .bat file with the necessary commands. You might want to consider that?????

 

[sueb859]

I have tried using the EXEC master..xp_cmdshell 'dtsrun /S /E /N' command in a stored procedure but permission is denied due to not being a sysadmin. Is there a way around this?

 

[Jimmy211]

Rasanders,

I did try to use a .bat file with the following command:

dtsrun /S ACCTSERVER /N AllocUpdateData /E

Is there a better way? When I run this on a machine without dtsrun installed it fails, as though its running on the client computer and not the server.

Also… Sueb859 –
I had the same question about the master..xp_cmdshell command. There is a topic in the Books Online about xp_sqlagent_proxy_account, which is some kind of proxy agent account. I tried to get the instructions for it to work but couldn’t. Perhaps someone with experience with it could elaborate.

Thanks for the posts!
Jimmy211

 

[MDXer]

I have no answers for you but I would recommend not using xp_cmdshell if at all possible. Opening up permissions on this can be a huge security risk. As it would allow someone to execute any command to the machine through SQL.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[rasETL]

I check a couple of my DTS .bat files, and I use...

start dtsrun.exe

rather than just dtsrun

Try that and see if it helps.

 

[Jimmy211]

Rasanders,

I tried the "start dtsrun.exe" option with the same effect. The computer without dtsrun installed gives the following error message:

"Cannot find the file \'dtsrun.exe' (or one of its components.) Make sure the path and filename are correct and that all required libraries are available."

That's what makes me think the command is executing on the client computer even though the .bat file is on the server. Any ideas?

Thanks,
Jimmy211

 

[MDXer]

the dts runs from whatever machine is calling it. If it is being invoked on the client even though the .bat is on the server it will use the client system. What happens when you call the .bat file directly on the server?

if you are un willing to deploy the dts runtime files to the clients then you may want to look at the rcmd service which has to be installed on both client and server and is a pain to run. Your better off making an install for the dts runtime and deploying it to the client machines.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Jimmy211]

What's a good way to make the dts runtime for deployment? Can it be done using VB6?

Thanks,
Jimmy211

 

[Jimmy211]

I had a better way to do this pointed out to me in another thread. An option that seems to work pretty well is to use a 2 step process.

1) Use a stored procedure to start a Job using the following sql:
EXEC msdb..sp_start_job @job_name = 'MyJobName'

2) The job starts the DTS package on the server using a vbscript and the dtsrun command:
Set oShell = CreateObject ("WSCript.shell")
oShell.run "dtsrun /S MYSERVER /N MyDTSpkg /E",1,true
set oShell = nothing

Then all you have to do is run the stored procedure from your app and off you go! Took me a dang week to figure this out. Thanks especially to JayKush in thread961-801017 for clueing me into this.

Jimmy211

 

[sueb859]

Jimmy211,
So this is an ActiveX script that is scheduled as a job that then runs a DTS package? Is that correct? Don't you still need to be a sysadmin, or have a proxy account set up to run this?

 

[Jimmy211]

Sorry Sueb859, I guess I spoke too soon. Yes I'm having problems with executing under this scenario as well. If I try to run the stored procedure as a user without Sysadmin privileges, the Job tells me the DTS package doesn't exist. I've been trying to get the proxy account to work without much luck.

Have you gotten the proxy server account to work? If so, did you assign it to a real user, or did you set one up specifically for this purpose in SQL Server?

Thanks,
Jimmy211

 

[sueb859]

Jimmy211,
I finally did get it to work by setting up a proxy account on the server. Right click on SQL Server Agent and go to properties. Go to the tab Job System. At the bottom you need to uncheck only sysadmin can execute CmdExec and hit button Reset Proxy Account. This was set up to an account used for internet access. This had to be set in order for it to work.

Then I set up a job, but the job must be owned by the person who will be calling it. So I guess if you needed multiple nonSysAdmins to call it they would each need their own job.

I then have a stored procedure with the person can call with systax similar to:
exec msdb..sp_start_job @job_name = @ProcToRun

See thread183-762193 for further info.

I did finally get this to work.