I have several DTS packages scheduled as jobs in SQL Server Agent. All the
DTS packages execute flawlessly when run manually. When run through the job
subsystem, the DTS packages execute, but packages in which the final step is
to FTP the results to another server will hang on that final step. They don't
fail, they just hang.

I have configured SQLServerAgent to use a domain account (the same account
as SQL Server) and also have a CmdExec proxy account established (another
domain account). I read through article 269074 and everything appears to be
configured correctly. I also read through 283811 to make sure that the
service account had all the correct permissions. I logged on to the server
using the SQL Server Agent account and was able to run the FTP commands given
in the DTS package, so I know that the account has permission to access and
read the files. I have even added the service account as a Domain Admin just
to make sure it isn't a permission issue, but the FTP step still hangs.

Does anyone have any suggestions? Here is our configuration:

Windows Server 2003 Enterprise, PAE enabled
SQL Server 2000 Enterprise (Active-Passive Cluster)
HP Netserver 6000
Intel Xeon x 6
8GB RAM
Fiber-connected SAN for storage

It's not waiting for user input is it?

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Eric Laney" <Eric Laney (AT) discussions (DOT) microsoft.com> wrote

Allan,
Thanks for your response.

No. The entire FTP session is scripted and run as a CMD batch file. I
tested for that by running the batch file interactively, and the whole thing
ran without asking for any input and lasted less than five seconds.

Unless it somehow asks for input when run as a job that it doesn't ask for
when run through DTS Designer. : )

"Allan Mitchell" wrote:

"Allan Mitchell" wrote:

Allan,
Actually, that *was* the problem. The key to the puzzle was the
combination of Windows Server 2003 when running a CMD batch file through the
ActiveX Scripting step.
Windows Server 2003, apparently by default, pops up a dialog box on CMD
batch files. I don't have the exact text, but it's something like, "Running
files of this type may be hazardous. So are those pork rinds. And that
ramen noodle soup you had for lunch yesterday. Try going outside one in a
while, too. Are you sure you want to run this file, loser?"
The reason it didn't do this when I tested the command interactively on
the server is because I started the Command Prompt session manually and then
ran the CMD from there. I have now also tested this with a CMD file that I
launched by double-clicking on it in Explorer and typed into Start/Run, and
neither of those methods caused the dialog box to pop up. It didn't do this
before when running the DTS through the Designer because we're not using
Windows Server 2003 as our desktop machines and we generally don't do
anything on the server that we can do somewhere else. The epiphany came when
we were logged on to the server, working on the scheduled job, and went to
run the package through the DTS Designer while still on the server. Up
popped the dialog. We have since reworked the ActiveX Script step in the DTS
packages to not use any CMD files, but to instead code the steps into the
ActiveX Script step. The step now executes properly.
Now I have to figure out how to remove this red mark on my forehead from
banging it into the server rack.

Thanks for your help,
Eric

Thanks for the detailed explanation. Hopefully others suffering this same
problem will find it on Google Groups.



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Eric Laney" <EricLaney (AT) discussions (DOT) microsoft.com> wrote

One last update: we found that the dialog box inconsistency (popping up in
the ActiveX Script task but not when using Windows Explorer) is because, for
some odd reason, the ActiveX Script task fetched the CMD file being executed
using Internet Explorer. (All of these CMD batch files are stored on a NAS
server to make them centrally available to all nodes in the cluster.) This
behavior caused another error to pop up that caused the DTS package to fail
(which is better than before, where it didn't do anything).
Once we traced things back to Internet Explorer, we were able to
permanently solve the problem by logging on to the server under the service
account and modifying the security settings in IE to more closely match those
found in Windows 2000. The DTS packages now work flawlessly using our
original methodology.