Freigeben über

SSIS package fails with error : The step did not generate any output. The return value was unknown

  • Artikel

One of my customers recently ran into a very strange issue. We had many SSIS Packages that were running as a SQL Server Job.

But, off late we saw all the Jobs that were running a SSIS Package failed. All other SQL Jobs were executing without any issues. All those SSIS Jobs were failing with the Same Error message.

Message

Executed as user: <Account>. The step did not generate any output. The return value was unknown. The process exit code was -1073741502. The step failed.

It was really a generic error message with no information with it. Everything ran fine if we ran it from BIDS or Command Prompt. The issue arises only when we ran it as a Job.

We created a new package with a simple execute SQL task and even that failed as a Job. So, we enabled the SSIS Logging for the Package. Unfortunately, it wasn’t even creating the Log file. So thought that it was not even reaching the point of Execution and it was failing even before that.

We ran a Process Monitor to check if that could give us any lead. But Even Procmon didn’t have any information on it. We checked the Event Viewer Logs. But no Error messages in that as well.

Suddenly, I saw an Informational Message being logged in the Event Viewer.

Application Popup: DTExec.exe- Application Error: The application was unable to start correctly(0xc0000142). Click ok to close the application.

As Expected it was failing at the start stage.

So finally after some research, found that this issue could occur if the Desktop Heap goes out of Memory. Every Desktop has a single Desktop heap associated with it. The desktop heap stores certain user interface objects, such as windows, menus, and hooks. When an application requires a user interface object, functions within user32.dll are called to allocate those objects.  If an application does not depend on user32.dll, it does not consume desktop heap. So if the Desktop heap is Out of Memory, then Process won’t start.

This behavior is kind of explained in https://support.microsoft.com/kb/824422/en-us

The Resolution is to basically increase the Desktop Heap Size.

HOW TO CONGIGURE DESKTOP HEAP

Backup the registry refer kb (322756 How to back up and restore the registry in Windows )

1. At a command prompt, type REGEDT32.EXE to start Registry Editor.

2. In Registry Editor, locate the following registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems

3. In the right pane of Registry Editor, click Windows.

4. On the Edit menu, click Modify.

5. In the Edit String dialog box, locate the SharedSection parameter string in the Value data box, and then specify a larger value for the SharedSection parameter.

Note The SharedSection parameter specifies the system and desktop by using the following format, where <xxxx> defines the maximum size of the system-wide heap (in kilobytes), <yyyy> defines the size of the per desktop heap, and <zzzz> is the size of the desktop heap for each desktop that is associated with a non-interactive Windows station:

SharedSection= <xxxx>, <yyyy> , <zzzz>

Change the following value from:

SharedSection=1024, 3072,768

TO

SharedSection=1024, 3072, 1536 (We are making this as double.)

6. Restart the machine.

And Yaaayyy! It worked!!!

Happy Learning!! J

Author : Mahaja(MSFT), Suport engineer, Microsoft

Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead, Microsoft

Comments

  • Anonymous
    December 10, 2014
    The comment has been removed

  • Anonymous
    January 13, 2015
    Hi Mahaja, I'm facing the same error, and I have increased the shared section as mentioned above but still the job is failing with the below error. The step did not generate any output.  The return value was unknown.  The process exit code was -1073741819.  The step failed. when we try editing the package path on job and give ok. SSMS closes but itself and reopens. can you please suggest some solution.

  • Anonymous
    May 09, 2015
    Nice

  • Anonymous
    May 11, 2015
    nice

  • Anonymous
    March 08, 2016
    Thank you!! Worked like a charm in the first go and fixed close to 10/ 15 Packages I had. But a question... I did have some other Packages which were on the same server... and they were not failing... the only difference between (yeah functionally they were different), but one set was working under a Proxy Account and were failing with this error... while the Other set running under SQL Agent were not failing.The lot under the Proxy account was failing with the above mentioned error and once I applied the fix as you mentioned on the VM these also started working.. would appreciate if you could help me understand why this difference in behavior.Many Thanks for your efforts for putting this blog! Thanks,

  • Anonymous
    May 08, 2017
    Thanks. worked perfectly. Good blog.