Hanging SSIS Packages

Been having some problems with Integration packages hanging, so thought I'd share some common bloopers to avoid in your development.

NEVER use the same table as OLE DB Source AND Destination in the same DataFlow.  When the package tries to write out to the table, it will be locked by the package trying to extract records. Seems simple enough, but I have fallen for it in the past!

NEVER do the same as above using Raw Files.  Similar reasons.  In fact don't do it for any connection/desintation. 

I never recieved a single error message, just hanging packages and a lot of frustration once I realised what I was doing.

Integration Services will also be affected by general locks on the SQL Server, so be careful how you manage access to the tables and locking in general.  For example, don't try and add to a Fact Table while a cube is processing - sounds simple unless you have a lot of schedules and haven't kept track of your timings (Only happened to me in testing BTW!).
Things to look out for:

Memory usage - large sorts can use up all available memory and stall a package.

CPU usage - can cause bottle-necks in large packages.

Bottle-necks in general - can slow a package down so much it looks 'hung'; check engine threads, Default BufferMaxRow and DefaultBufferSize for large DataFlows.  Consider using BufferTempStoragePath, which can help free-up memory.  I have used this quite successfully while processing large dimensions.  Also consider splitting the DataFlow into chunks.

I'll do a post on this in the next couple of days:-  using the Modulo Algorithm to split a dimension, so when the Slowly Changing Dimension is running, we are only using a section of the dimension at a time.

Currently I am working on a mystery hanging package.

Task 1 (DataFlow): Imports 10 million records to a table.

Task 2 (Execute Package Task): Runs a small package containing a Slowly Changing Dimension.

Task 3 (Raw File Sourced DataFlow): Transforms some data.

Always hangs on Task 3.

Moved Task 2 to Task 6; improves performance by changing the task that hangs to task 15.

Still working on this one...