I have an SSIS package that recently hung in the middle of execution. It was
in the middle of a ReadCommitted transaction that is accessing one database.
During this time, all other SQL queries to this database hang. If I stop the
SSIS package, everything works fine. This package has been working
flawlessly for a while and has not had this problem before.

I had SQL tracing turned on and can see where the hang occurred, but I don't
see any reason for the hang. I had event 25 (Lock: Deadlock) enabled for
tracing, but I did not see this in the trace. I also had some other events
enabled for tracing, mainly to show the queries.

I really don't know why this hang is occurring, since there does seem to be
any SSIS logging. Is there any SSIS server side logging or debugging that I
can turn on for this? Are there any recommended SQL tracing events that I
should enable? Does this look like a deadlock issue even though I'm using a
ReadCommitted isolation level?

Unfortunately, this happened on a production machine so I did not want to
hold anything up. If this happens again, I'm not entirely sure where to look
for the problem, SQL Server or SSIS or a combination of both.

I'd appreciate information from anybody that might have experience with this
or have an approach to take with this problem.

Hello,

I understand that you once encountered a hang issue when executing a SSIS
package when it was in a readcommitted transaction. You didn't find
deaklock events in the trace log. If I'm off-base, please let me know.

SSIS logging and log providers let you capture details about the package
execution and failures. By default, the package does not log information.
You must configure the package to log information. When you configure the
package to log information, you will see detailed information that
resembles the following.

By using the exec subsystem command approach, you add verbose console
logging switches to the SSIS command line to call the Dtexec.exe SSIS
command-line executable file. Additionally, you use the Advanced job
feature of the output file. You can also use the Include Step Output in the
history option to redirect the logging information to a file or to the SQL
Server Agent Job History.

The following is an example of a command line:

dtexec.exe /FILE
"C:\_work\SSISPackages\ProtectionLevelTest\ProtectionLevelTest\AgentTesting.
dtsx" /MAXCONCURRENT " -1
" /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT

Please see the following article for more details:

918760 An SSIS package does not run when you call the SSIS package from a
SQL Server Agent job step
http://support.microsoft.com/default.aspx?scid=kb;EN-US;918760

SQL Server 2005 - SQL Server Integration Services - Part 12 - Logging
http://www.databasejournal.com/features/mssql/article.php/3562406

Since the issue only appeared once and it also locked other SQL
connections, it might be some internal errors in SQL Server, To find out
the root cause of this issue we may need to analyze memory dumps, this work
has to be done by contacting Microsoft Product Support Services. Therefore,
we probably will not be able to resolve the issue through the newsgroups.
If the issue is urgent, I recommend that you open a Support incident with
Microsoft Product Support Services so that a dedicated Support Professional
can assist with this case. If you need any help in this regard, please let
me know.

For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp

Since you did not find any deadlock events on the server, it may also be
caused by blocking issue on the server. You may want to refer to the
following article to get the result and send it to me for reviewing:
(Please remove "online" in my displayed email addrees)

How to monitor blocking in SQL Server 2005 and in SQL Server 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509

INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/?id=224453

Please also include sql profiler trace when the issue occurs. I will check
if there is any possible blocking issue. Thank you!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.