DTS - Strange Behavior

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@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> 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@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> 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.
>|||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@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> 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.
>