Batch Scheduling using SQL

[Fouts]

How can I keep a batch file active for the duration of a SQL DTS job?
The job runs for variable lengths of time but usually around 45 min. to an hour.
When I execute this batch job, it immediately returns a 0 for a return code and the DOS window closes.
I have other time sensitive jobs dependent on its completion.

I have a batch file calling ISQL using the command:
isql -U sa -P sa -d db_name -s server -i SQL_Exec.sql -o SQL_Exec_output.txt -n -b

The input script "SQL_Exec" is as follows:
USE msdb
DECLARE @RC int
EXEC @RC = sp_start_job @job_name = 'Test'

The SQL job "Test" kicks off a DTS utility using:
DTSRUN /S server /E /N package_name

 

[mrdenny]

Instead of the batch file starting the job, why not have the batch file run the DTS package using DTSRun?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Fouts]

Does this correctly keep the dos window open? If so, that is something that would work.

Is there any other way to do it using ISQL?

 

[mrdenny]

Yes it will keep the dos window open.

You could use iSQL and have it call xp_CMDShell and run DTSRun from there, but at that point you may as well just use DTSRun from the batch file it self.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Fouts]

I copied the exact DTSRUN command from the SQL Job step and pasted it into my batch file. It is giving me errors as if it doesn't like the syntax.
Is this because of a path problem? I tried running it in the C:\Program Files\Microsoft SQL Server\MSSQL\Binn but got the same results.

Here is the DTSRUN command.
DTSRUN /S Server1 /E /N Package1

What am I missing?

 

[mrdenny]

That should do the trick. Providing that the user account you are using to log into windows with has the access needed to the SQL Server. What's the exact error message that you are getting?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Fouts]

I was incorrectly calling it before as the input to my ISQL command, however...

When I run the command by itself the batch file "spins its wheels" without actually starting the DTS package.

The batch window shows the command repeating itself indefinitely.

 

[mrdenny]

What happens if you call the DTSRun from a command line, and not from the batch file.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[wcburton]

To assist in creating the command line, you can use DTSRunUI. Run that program from the command prompt with no parameters. You will get a window that will allow you to choose the server, DTS package, set options and variables, etc. You can run the DTS package from there but you can also hit the button labeled "Generate" to create the full command to use inside a batch file. (Generate is found by going into the advanced options)

 

[jymm]

I know that when I run DTS from a batch file I have to put quotes around the name since there are blanks --- like this

dtsrun /SSQL1Server /Uuser /Ppwd /N"Customers to Report"

 

[Fouts]

Thanks All for the suggestions. I will try these things soon. Our server is down now.