Hi,

I have a couples of DTS jobs set up for data import from Oracle to SQL
server. They all appear to run fine when triggered manually. However,
when they are scheduled to run routinely in SQL server job agent, these
jobs may OCCASIONALLY got hanged. Some jobs which normally finish within
hours (or even less than ten minutes) will hold on for more than 24
hours. They don't finish or fail, just hang there. However, when the DTS
jobs are run manually (inside the DTS designer), this scenario never
appear. Any idea please?

Thanks a lot.

have a nice day
walty

Dear Walty,

Thank you for posting here.

Based on my research, this behavior occurs because the package is using
providers, drivers, or DLLs that are apartment threaded or that are not
thread-safe. In such situation, a DTS package may raise exceptions or stop
responding (hang) when you run it as a SQL Server Agent scheduled job.

As a resolution or workaround, please click the "Execute on main package
thread" option on the Workflow Property tab 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.

Below is the related KB article for this behavior:

PRB: A DTS Package Raises Exceptions or Stops Responding When You Run It as
a Scheduled Job
http://support.microsoft.com/?id=318819

If anything is unclear in my post, please don't hesitate to let me know.

Have a nice day!

Best regards,

Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support

Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

Dear Walty,

Thank you for posting here.

Based on my research, this behavior occurs because the package is using
providers, drivers, or DLLs that are apartment threaded or that are not
thread-safe. In such situation, a DTS package may raise exceptions or stop
responding (hang) when you run it as a SQL Server Agent scheduled job.

As a resolution or workaround, please click the "Execute on main package
thread" option on the Workflow Property tab 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.

Below is the related KB article for this behavior:

PRB: A DTS Package Raises Exceptions or Stops Responding When You Run It as
a Scheduled Job
http://support.microsoft.com/?id=318819

If anything is unclear in my post, please don't hesitate to let me know.

Have a nice day!

Best regards,

Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support

Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

Dear Walty,

Thank you for posting here.

Based on my research, this behavior occurs because the package is using
providers, drivers, or DLLs that are apartment threaded or that are not
thread-safe. In such situation, a DTS package may raise exceptions or stop
responding (hang) when you run it as a SQL Server Agent scheduled job.

As a resolution or workaround, please click the "Execute on main package
thread" option on the Workflow Property tab 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.

Below is the related KB article for this behavior:

PRB: A DTS Package Raises Exceptions or Stops Responding When You Run It as
a Scheduled Job
http://support.microsoft.com/?id=318819

If anything is unclear in my post, please don't hesitate to let me know.

Have a nice day!

Best regards,

Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support

Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

Dear Walty,

Thank you for posting here.

Based on my research, this behavior occurs because the package is using
providers, drivers, or DLLs that are apartment threaded or that are not
thread-safe. In such situation, a DTS package may raise exceptions or stop
responding (hang) when you run it as a SQL Server Agent scheduled job.

As a resolution or workaround, please click the "Execute on main package
thread" option on the Workflow Property tab 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.

Below is the related KB article for this behavior:

PRB: A DTS Package Raises Exceptions or Stops Responding When You Run It as
a Scheduled Job
http://support.microsoft.com/?id=318819

If anything is unclear in my post, please don't hesitate to let me know.

Have a nice day!

Best regards,

Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support

Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

Dear Walty,

Thank you for posting here.

Based on my research, this behavior occurs because the package is using
providers, drivers, or DLLs that are apartment threaded or that are not
thread-safe. In such situation, a DTS package may raise exceptions or stop
responding (hang) when you run it as a SQL Server Agent scheduled job.

As a resolution or workaround, please click the "Execute on main package
thread" option on the Workflow Property tab 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.

Below is the related KB article for this behavior:

PRB: A DTS Package Raises Exceptions or Stops Responding When You Run It as
a Scheduled Job
http://support.microsoft.com/?id=318819

If anything is unclear in my post, please don't hesitate to let me know.

Have a nice day!

Best regards,

Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support

Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

Dear Walty,

Thank you for posting here.

Based on my research, this behavior occurs because the package is using
providers, drivers, or DLLs that are apartment threaded or that are not
thread-safe. In such situation, a DTS package may raise exceptions or stop
responding (hang) when you run it as a SQL Server Agent scheduled job.

As a resolution or workaround, please click the "Execute on main package
thread" option on the Workflow Property tab 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.

Below is the related KB article for this behavior:

PRB: A DTS Package Raises Exceptions or Stops Responding When You Run It as
a Scheduled Job
http://support.microsoft.com/?id=318819

If anything is unclear in my post, please don't hesitate to let me know.

Have a nice day!

Best regards,

Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support

Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

Dear Walty,

Thank you for posting here.

Based on my research, this behavior occurs because the package is using
providers, drivers, or DLLs that are apartment threaded or that are not
thread-safe. In such situation, a DTS package may raise exceptions or stop
responding (hang) when you run it as a SQL Server Agent scheduled job.

As a resolution or workaround, please click the "Execute on main package
thread" option on the Workflow Property tab 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.

Below is the related KB article for this behavior:

PRB: A DTS Package Raises Exceptions or Stops Responding When You Run It as
a Scheduled Job
http://support.microsoft.com/?id=318819

If anything is unclear in my post, please don't hesitate to let me know.

Have a nice day!

Best regards,

Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support

Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

Dear Walty,

Thank you for posting here.

Based on my research, this behavior occurs because the package is using
providers, drivers, or DLLs that are apartment threaded or that are not
thread-safe. In such situation, a DTS package may raise exceptions or stop
responding (hang) when you run it as a SQL Server Agent scheduled job.

As a resolution or workaround, please click the "Execute on main package
thread" option on the Workflow Property tab 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.

Below is the related KB article for this behavior:

PRB: A DTS Package Raises Exceptions or Stops Responding When You Run It as
a Scheduled Job
http://support.microsoft.com/?id=318819

If anything is unclear in my post, please don't hesitate to let me know.

Have a nice day!

Best regards,

Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support

Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

Dear Walty,

How's everything going?

I'm wondering if the suggestion has helped or if you have any further
questions. Please feel free to respond to the newsgroups if you need any
additional help.

Have a nice day!

Best regards,

Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support

Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.



--------------------