Hi,

I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:

Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
not exist or access is denied.

After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).

The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.

If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.

Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.

Thanks

Jerry

PS - The double post is in leu of time constraints.

Also,

This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.


"Jerry Spivey" <jspivey (AT) vestas-awt (DOT) com> wrote

Also,

This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.

"Jerry Spivey" <jspivey (AT) vestas-awt (DOT) com> wrote

Got it. For future views of this post:

The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.

Jerry


"Jerry Spivey" <jspivey (AT) vestas-awt (DOT) com> wrote