Cause of Intermittent SSIS Job failure with exit code -1073741819?

Question

I am relatively new to SSIS. In the environment I am working in, I have access to a Dev database only. I create and test packages, then send these to a DBA to execute on our UAT environment. One of these packages, which is importing XML files, is intermittently failing when run as an SQL Server Agent job. This is a problem, as the end solution is for a high-reliability system. The test load is approximately 46000 files, and the import is variously failing after 1000-2000 files imported.

The very informative error message is shown below. I've done a little research, and the only information I can locate on this error (which is sketchy at best) talks about concurrency problems. So, I ratcheted back the maximum executions to 1...still the same issue.

Executed as user: \. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:19:57 PM. The return value was unknown. The process exit code was -1073741819. The step failed.

Can anyone suggest a cause or solution? Or even a method for obtaining more diagnostic information?

Answer 1

Configure the package to capture log-enabled events at runtime.
Click on the SSIS menu -> Logging I like to use the log provider for SQL Server, this will create a system stored proc called 'sp_ssis_addlogentry' and a table named 'sysssislog'. This should at very least give you the component within the package that is failing.

If I had to guess, there is an exception in a ScriptTask that is unhandled and the task is not setting DTS.TaskResult.

DTS.TaskResult = (int)ScriptResults.Failure

Try wrapping the ScriptTask code in something like this (but remove catch(Exception Ex) when you figure out what the problem is)

try
{
    bool fireAgain = false;
    // Some Code
    Dts.Events.FireInformation(0, "", "Some logable info: I Love Puppies", String.Empty, 0, ref fireAgain);
    Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception Ex)
{
    Dts.Events.FireError(1, "", "FAILURE: " + Ex.Message, "", 0);  // this will show up in the sysssislog table
    Dts.TaskResult = (int)ScriptResults.Failure;
}

Answer 2

Is your flow totally sequential, or do you have some parallel tasks going on? If there are parallel tasks then it may be a problem with the timing of the two flows (ie. one may be accessing data that the other flow is using...or maybe something SSIS can't cope with).

You can try setting the package's MaxConcurrentExecutables to 1 (ie. force only 1 thread to be used when the package runs). You can see/set this property in the designer when you open the package and are viewing the Control Flow window. If you set this property to 1, it should help you determine if this is the type of problem that you're hitting. If it solves your problem, then you can either leave it with that setting, or play around with your package to try to eliminate the parallelism that is causing the original problem.

Here's a link to the MaxConcurrentExecutables flag: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.maxconcurrentexecutables(v=SQL.90).aspx