JOBS and DTS's

[sefafo]

Hello..

I want to schedule a Jobs Step to run a DTS. I am wornking in Visual Basic 6.0. The DTS is generate from my applications, but I need to schedule it, could you help me please.

 

[LogicalmanUS]

sefafo,

I cannot tell you how to schedule from VB6, but I would advise that you request your DBA to schedule any jobs, as he/she would need to ensure all permissions, and the DTS package integrity are correct.

Above all else, it's courtesy for the DBA to OK all jobs running on their server.

Logicalman

 

[sefafo]

The application generates a DTS, so I need to be able to schedule the step from it. Actually , the end user is who choose tha date and time to execute...

 

[LogicalmanUS]

sefafo,

I'm sorry, I can't fully answer your question, except that scheduling DTS packages to run are under the auspices of the SQL Server Agent. I'm not sure if you have access to that agent, if you do, then that would be the way to go.

Logicalman

 

[sefafo]

It's ok LogicalmanUs, I'll keep looking for, If I got it, I will let you know!!!

Thanks a Lot!!!

 

[dky1e]

You must run "dtsrun" command prompt utility on the server.

In order to schedule a job you must have access to msdb database and be able to run sp_add_job, and sp_add_jobstep and sp_add_jobschedule. Take a look at these stored procedures in SQL Server Books Online.

You will need to schedule a job that will run a command prompt script(@subsystem ='CMDEXEC').

Talk to your DBA first. I am sure they can be of much help.

 

[LogicalmanUS]

sefafo,

dky1e's reply clicked something in the back of my mind.

If you can create the DTS package locally, then you should have the dtsrun utility loaded locally as well.
If so, then you can run the DTS package using the dtsutility from a command line.
Therefore, taking that a step further, you can create a batch file to kick off the dtsutility and then use the widnows scheduler to run the batch file.

All this is theory, I have not even attempted the latter two stages, but, as they say, theory can work.

Please keep me advised, I'll be very interested.

Logicalman

 

[topher0303]

It sounds as if you are dynamically building your DTS in your VB app each time it is run as opposed to creating it one time in Enterprise Mgr, saving it, and scheduling it.

If this is the case, you can use windows scheduler to kick off your VB app. If the user doesn't access the scheduler directly, you could schedule it from an app using some winapi calls.

If you use Norton Anti-Virus, you will find that when you schedule scans and definition updates, it creates an entry in your windows scheduler in much the same way.

 

[sefafo]

Good Morning..

I have checked sp_add_jobstep stored procedure, my question is: that store procedure has a parameter called
@command ,the command(s) to be executed by SQLServerAgent .

I don't know how to poblate this parameter in order to add a step that executes a DTS, when a Schedule a DTS and look in the sysjobsJob that executes a DTS do you follow me?? Sorry for my english.

when a query the sysjobsteps, the field command has the following value for a dts job schedule:

DTSRun /~Z0x6C74A21013F9E12491341A78AA33958344AC3A680E8796AAC66CB47AC37E66885EF83DE64DCA8FF58D2A8CCE2EB2E3986E4850439E2046BBD11D9CD6E7A30E1AF5F3C25B49BFD1A87EDE82FD1C6E669E8F0E8FD296BE0BBF2DB1E931CB3680CBEC5ADBF656FA47CDDD41C47C893C01F027DA1E9558EA9841B324

 

[sefafo]

Hi. I got thanks to you. I use dtsrun and send the appropietate parameters:

dtsrun /Sservidor /Uusuario /Pcontrasena /Nnombredts

now I can send this string as the value for the parameter @command of sp_add_jobstep store procedure!

Thanks for all!!!!!