SQL Server Integration Services (SSIS) Package Failing with no error

Question

I have some SSIS packages that are executed as steps in a Server Agent Job. The Steps often fail with no error and I have had to add retries. Now the jobs complete but I get the warning exclamation mark telling me there has been a failure and its these steps that nearly always require a retry.

When looking at the failed step in the Job History I can see the package execution log and then it just randomly cuts off and the step beings a retry. There is no consistency with where it stops and I know there isn't a problem with the package as it eventually succeeds.

Does this sound like a network related issue? Is there anyway to find out what is causing these failures to happen?

Answer 1

Working backwards from the job, have you been able to run the package straight from the server? In older versions of SQL Server it might be in the Integration Services server, that you can connect to through Management Studio, or in newer versions of SQL Server it could be in the Integration Services Catalog on the database server. If you can run the package directly through there, then I would take a look the job credentials, or any configuration settings that are being set in the job. If the package fails when running it directly, then it may be the package itself that's the problem, which then I would go back to running and troubleshooting it from Visual Studio\BIDS to see where the problem is occurring.

On a more unusual path, if the package is doing a lot over an unsteady network, then you might be getting network related problems, or timeouts that aren't being reported as such to SQL. This might need you to get creative in staging data or breaking up the tasks into smaller packages and then calling those packages as additional steps.