sql server dts: Hi there,
I have created a DTS package that works fine when you execute it, but
when you schedule it, it will always fail.

The package connects a local SQL server to a remote web server that
also has SQL 2000 running on it. Its running in Windows only security
mode.

The package deletes the entries from 2 tables and then repopulates
them with the current data.

In order to work out what was happening I decided to log each step in
the package, so I have inserted some sql tasks between each step that
updates a table on the remote web server. I have setup workflow
between each step and the logging action. When the package is run the
steps are all logged and the package is executed perfectly, yet still
when it is scheduled as a job it fails. Even if I try to force the job
to start it takes while but fails, nothing is inserted in my log table
on the web server.

Does anyone have any ideas as to why this is happening?

I hate replying to my own posts, but it may help someone :)

I have setup my online SQL 2000 server to use a different port for
clients to connect to. In order to get EM to connect from my work
station I had to create an ODBC link and tell it not to use the
standard port, what I didn't realise was the way the packages were
running were from the local server, which isn't on my work station.
Once I had setup an ODBC with the correct port number everything
started to work correctly.

So, if you are using a different port on your remote server, always
make sure each machine that needs to connect to the server has an ODBC
setup. If there is another way I should be doing this, please let me
know, I am still learning!

Cheers,

Steve