Methods to run a DTS from a client machine (on demand) 1

[Edimator]

Hi, i have a DTS that process a file generated from another system. That file have critical data every minute. The DTS package transform the data file and insert in a SQL table with only data from the actual day.

Actually the DTS works fine scheduled every hour, but some clients need data more fresh than that. I think the solution is put a code in the client front-end that execute the DTS in the server.

I try the DTSRUN but i don't have any information that how i can distributed that program.

Even i try with the code to run a DTS Package from a ASP page (using the LoadFromSQLServer method) and work great but asynchronously, and the report run before the new data insertion are available.

Any ideas that how i can perform that. I really apreciate any kind of help.

p.d.: Sorry about my english

 

[foxdev]

How about the users invoking a Stored Procedure that runs the DTS job? That way, DTSRUN.EXE isn't actually running on the client machine, but on the server. Robert Bradley
Support operation moo!
Visit the OpCow page

http://www.agrainofhope.org/opcow.shtml

to help in this cause

 

[Edimator]

That's correct. the DTSRUN only run on the server machine no on the client, sorry if i wasn't clear.

 

[tlbroadbent]

Here is a method that we've employed.[ul][li]Create a job to run the DTS package.
[li]Set the job to start on an Alert.
[li]Give the user a way to execute RAISERROR. The RAISERROR execution triggers the Alert and the Job starts.[/ul]The advantage is that the Job can run under control of SQL Agent and can safely be given more priveleges than you would give another user. The user doesn't require access to the DTS package, network shares, or database priveleges that you would not normally provide.

Executing RAISERROR can be easily incorporated into an application. The SQL command can be as simple as

RAISERROR(50001,10,1)

Hope this is helpful. I'll be glad to provide more info if needed. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[foxdev]

That's correct. the DTSRUN only run on the server machine no on the client, sorry if i wasn't clear.

Correct X 2...I'm not sure where we're miscommunicating - the advantage of having the stored procedure kickoff the DTS job is because the DTSRUN.EXE is on the server, and doesn't need to run on the client.

I'll give Terry 1000 "Whose Line is it Anyway" points for creativity. Robert Bradley
Support operation moo!
Visit the OpCow page

http://www.agrainofhope.org/opcow.shtml

to help in this cause