SSIS Package Hangs when run as job

• LEBallard

  SSC Enthusiast

  Points: 142

  More actions

  August 20, 2009 at 9:09 am

  #137338

  I realize this is the 2005 SSIS forum, but I didn't see a 2008 SSIS forum so I wasn't sure to post

  I completed an in place upgrade from SQL Server 2005 (Standard) to 2008 (Standard) a month ago. The server is Windows 2003 Enterprise. Since that time, I've had an SSIS package that imports approximately 15 million rows on a daily basis hang intermittently. The package is scheduled to run as a SQL Server job so it looks as if the package stops running but the job is still running. The data source is a Windows 2003/SQL Server 2000 machine.

  I do have the logging turned on and when the package hangs, the message the last item written into the log states...

  "Rows were provided to a data flow component as input. : : 63 : OLE DB Source Output : 46 : ... : 59 : OLE DB Destination Input : 4096"

  and the event was "OnPipelineRowsSent"

  So it looks as if the package just hung up during the import and never recovered. This is where the package always hangs (although the package doesn't always hang on the same number of rows).

  The package is set to do a fast-load and I've experimented with changing the Rows per batch and maximum insert commit sizes but those experiments have not solved the issue.

  I've had the Windows Performance logs running and when I examine those there doesn't seem to be a memory, processor, disk or I/O issue. I'm not finding errors in the Event Logs or the SQL logs either. I've tried googling this and searching various forums, but I haven't seen anything applicable.

  Any suggestions would be extremely appreciated.