PRB: A DTS Package Raises Exceptions or Stops Responding When You Run It as a Scheduled Job

A Data Transformation Services (DTS) package may raise exceptions or stop responding (hang) when you run it as a SQL Server Agent scheduled job if the package is using third-party drivers or Dynamic Link Libraries (DLLs) that do not support free threading.

You can use DLLs in a custom task, an ActiveX Script task, or a Transform Data task. The packages may run successfully from Enterprise Manager or they may not experience errors upon each scheduled run; however, if the package never completes, and dtsrun is still running in Task Manager, the package has stopped responding. You may also see this behavior when you run the dtsrun command prompt utility.

This behavior occurs because the package is using providers, drivers, or DLLs that are apartment threaded or that are not thread-safe.

To work around this behavior, on the Workflow Property tab, click the Execute on main package thread option for the Transform Data task, or ActiveX Script task step that uses apartment-threaded third-party drivers or DLLs. Turn on DTS package logging to determine at which step the failure occurs. If the package log indicates that all steps have succeeded, turn on Execute on main package thread for each Transform Data task or ActiveX Script task that uses apartment-threaded drivers, providers, or DLLs.

DTS uses the free-threading model to allow the steps to execute in parallel on different threads. If you try to use parallel execution on a provider that does not support it, exceptions may occur. A data provider that is used with DTS Designer that does not support parallel execution is the OLE DB Provider for Microsoft Jet, which accesses data from Paradox, dBASE, Microsoft Excel, and HTML source files.

For more information about supported drivers see:

• SQL Server 2000 Books Online:
  "DTS Driver Support for Heterogeneous Data Types"
  
  "Workflow Properties (Options Tab)"
• SQL Server 7.0 Books Online:
  "Driver Support for Heterogeneous Data Sources"

An ActiveX Script task, custom task, or Transform Data task that uses a Microsoft Visual Basic DLL may also cause a package to raise an exception or to stop responding when you run the package as a scheduled job. The Visual Basic DLL is apartment threaded while DTS is free threaded. Additionally, this information applies to apartment-threaded DLLs that are written in other languages. For more information, see:

• SQL Server 2000 Books Online:
  "DTS Example: Running Concurrent Operations in Visual Basic"
  
  "DTS Package Events in Visual Basic"
• SQL Server 7.0 Books Online:
  "Handling Connection Point Events"

For additional information about custom tasks, click the article numbers below to view the articles in the Microsoft Knowledge Base: