How do I run multiple DTS jobs?

[bigfoot]

As a forinstance; if I create 4 file transfers from my AS/400 to SQL Server, because sometimes I want to run them seperately, and sometimes I want to run them together.

What do I use to "package" them together so I can call them as a single job.

Any takers??

 

[wilk]

you could create a job using sqlserver agent, if you do this make sure the sqlserver is not login on as local system.

Within the job have 4 steps one for each job. The steps would look like this

master..xp_cmdshell 'dtsrun /S servername /E /N dtsname'

that should sort it out then you could either add a schedule or just execute the job as and when

 

[MissTipps]

This is certainly how I'd handle it.

However, a word of advice. I have many DTS packages running import and export to and from AS/400s. I tend to use the following OS command within the job step to kick off a DTS package:

C:\Program Files\Microsoft SQL Server\80\Tools\BINN\DTSRun.exe /<server name>/N&quot;<package name>&quot; /E

rather than xp_cmdshell. The reason for this is that AS/400 connections can fail for many reasons and if this should happen your job will hang indefinitely. As you can't kill an extended stored procedure, the only way to get rid of it is a reboot.

Just a tip to save you the hassle I've had in the past.

Cheers

Clare

 

[SQLSister]

Create separate DTS packages for each process you want to test and run separately. Then you can put them all together in a different package using the execute package task. Then schedule and run the one package to do all four processes or run a single package by itself.

 

[bigfoot]

Thanks. And MissTipps, I was thinking of subrmtcmd (Submit Remote Command) from the AS/400. This runs a batch file on my win2000 system that has in it:
C:\PROGRA~1\MICROS~3\80\TOOLS\BINN\DTSRun /S &quot;ARETT2&quot; /U &quot;myuser&quot; /P &quot;mypassword&quot; /N &quot;ExportOHSELOG&quot; /G &quot;{6798D053-EF19-407B-861A-2BDAB03B8622}&quot; /W &quot;0&quot;

DTSRUN Barfed when I used spaces in the path, i.e. 'Program Files'

That runs the job fine. But then, I wanted to make a few jobs and then be able to include them in a kind of a master job that my AS/400 will call.

When I right-click on a SQL file transfer DTS job in and choose schedule it makes a &quot;job&quot; in my jobs under SQL Server Agent.

How do I then add to this job the other steps, and then run this job under my batch file.
Or am I in the wrong spot. I don't want to schedule my SQL job, I want to save it and call it from my AS/400.

Sorry, I have been away from SQL since 6.x. 2000 is real new, and has tons of cool stuff.

And the online help is not real good at teaching.