DTS partial failure if scheduled or called in ASP

• earthside

  SSC Enthusiast

  Points: 139

  More actions

  October 18, 2002 at 6:21 pm

  #173560

  Environment:

  WIN2k Server, IIS 5.0

  SQL Server 2000

  Problem:

  I am developing a DTS package that queries 30 ODBC data sources and inserts the data into a table. 10 of the ODBC sources are Oracle 7 or 8 DBs and they work 100% of the time. The remaining 20 ODBC sources are INFORMIX5 DBs and they only work if I run the package manually. I need to be able to get the packages to run reliably for the INFORMIX sources in the following situations-

  1. Scheduled daily package execution

  2. On-demand package execution when called from an ASP file.

  The tasks in the package that deal with the Oracle DBs work 100% in both cases, but the INFORMIX tasks always fail. I have moved the INFORMIX tasks to the beginning of the package, and set up "on success" connections so that a single failure should cause the entire package to fail. SQL Server just skips the INFORMIX tasks and processes the Oracle tasks, then reports success.

  Anyone have any ideas?

  Thanks

  Andy

• earthside

  SSC Enthusiast

  Points: 139

  More actions

  October 21, 2002 at 8:52 am

  #439008

  Antares686 (who it turns out I know through work) provided an answer to this question via phone...

  Turns out SQL Server Agent did not have adequate security access to run the Informix tasks.

  Thanks Antares...