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?

Can you give a bit more info on the XML files? For example, how are the xml files loaded...are they loaded sequentially in exactly the same order every time, or can the order change? I'm just wondering if it's possible that one (or more) of the xml files is causing the error, and how to narrow it down to the exact file. — Joe L., Feb 19 '11 at 01:34
The SSIS job runs as a For loop over a directory. Each individual xml file is passed into the dataflow step. It is possible the individual XML files contain slightly different content, but...even when it fails, the XML file being processed actually does import as expected. It's only the job itself that fails. I've also attempted to run the job manually through the dev environment, and it runs fine. I've had it running for 5-6 hours (about 40000 files to import, so takes some time), and it only stopped when my VM session timed out. Clearly, this won't happen for the job. — YogoZuno, Feb 20 '11 at 22:34
You've probably already seen all these but this particular search had some people that got that error and the different ways they solved it: http://www.google.com/search?q=%22-1073741819%22+ssis&rls=com.microsoft:en-us:IE-SearchBox&ie=UTF-8&oe=UTF-8&sourceid=ie7&rlz=#hl=en&rls=com.microsoft:en-us:IE-SearchBox&q=+site:social.msdn.microsoft.com+%22-1073741819%22+ssis&sa=X&ei=-YNiTa3kJcHYgAfHi62nAg&ved=0CBwQrQIwAA&bav=on.1,or.&fp=6ec3191c2b79d4d0 — DKnight, Feb 21 '11 at 15:46

score 3 · Accepted Answer · answered Feb 23 '11 at 22:09

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;
}

There are a couple of script tasks that check the file size of an error log file, said file always exists. The Script task was always setting the task result to success, but didn't have any error handling...trying that now. — YogoZuno, Feb 23 '11 at 22:20
Cant help with that, just make sure your log is pointing somewhere you have access to...like a network share. — Jeremy Gray, Feb 23 '11 at 22:39
I can't verify until next week, but I'll pay the bounty now I guess. — YogoZuno, Feb 24 '11 at 03:51

Joe L. · Answer 2 · 2011-02-15T15:19:43.147

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

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

2 Answers2