SQL Agent job step "Dump on errors" produces no output

This caught me off guard the first time too. The most common reason no dump file is created is that the target folder either does not exist or the SQL Server Agent service account does not have write access to it. Check that first before anything else.

The default dump location is:

C:\Program Files\Microsoft SQL Server\<instance>\Shared\ErrorDumps

Grant the Agent service account full write access to that folder. If the folder does not exist create it manually and then set the permissions.

You can also confirm where SQL Server is actually configured to write dumps:

sql

EXEC xp_instance_regread 
 N'HKEY_LOCAL_MACHINE',
 N'SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\CPE',
 N'SqlDumpDir';

The other thing worth knowing is that Dump on errors only fires on managed SSIS errors. If the package is terminating unexpectedly due to an out-of-memory condition, a process crash, or an external dependency failure, the dump mechanism is bypassed entirely and nothing gets written. Check the Windows Event Log at the exact time of failure. That will often tell you more than the SSIS logs in these cases.

Also make sure the target volume has available disk space. A full disk causes the dump to silently fail with no notification.

Since the SSIS internal dump mechanism is bypassed, you may try following solutions:

1. Check Windows Event Viewer

Because the crash happened at the operating system level, Windows always logs it.

• Open Event Viewer.
• Expand Windows Logs then Application.
• Look for an Error level event sourced from Application Error or .NET Runtime occurring at the exact second the job failed.
• The error message will usually identify the exact faulting module.

2. Configure Windows Error Reporting (LocalDumps)

You can force the Windows operating system itself to catch the process crash and generate a dump file, circumventing SSIS entirely.

• Open the Registry Editor (regedit) on the SQL Server machine.
• Navigate to: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Windows Error Reporting\LocalDumps
• Create a new key named ISServerExec.exe.
• Inside that key, create a Expandable String Value (REG_EXPAND_SZ) named DumpFolder and set its value to a folder path (e.g., C:\CrashDumps).
• Create a DWORD (32-bit) Value named DumpType and set it to 2 (this forces a Full Dump).
• Run the failing job again. Windows will intercept the unhandled crash and drop a full .dmp file into that folder.

3. Review the SSISDB Catalog Reports

If the package ran for a short period before dying, some clues might have been flushed to the SSIS catalog database before the termination. Right-click your package in SQL Server Management Studio (SSMS), go to Reports then Standard Reports then All Executions, and click on All Messages for the failed run.