You develop a SQL Server Integration Services (SSIS) project by using the Package
Deployment Model. A package in the project extracts data from a Windows Azure SQL
Database database.
The package is deployed to SQL Server.
The package is not producing the desired results.
You need to generate the .mdmp and .tmp debug files in order to troubleshoot the issues.
What should you do?
A.
Execute the catalog.add_data_tap stored procedure with the package execution_id.
B.
Execute the catalog.create_execution_dump stored procedure with the package
execution_id.
C.
Run the DTEXEC utility with the /DumpOnError option.
D.
Run the DTEXEC utility with the /Reporting V option.
It is C
https://technet.microsoft.com/en-us/library/ms162810(v=sql.105).aspx
/Dumperror code
(Optional) Creates the debug dump files, .mdmp and .tmp, when one or more specified events occur while the package is running. The error code argument specifies the type of event code—error, warning, or information—that will trigger the system to create the debug dump files. To specify multiple event codes, separate each error code argument by a semi-colon (;).
By default, Integration Services stores the debug dump files in the folder, :\Program Files\Microsoft SQL Server\100\Shared\ErrorDumps.
NoteNote Debug dump files may contain sensitive information. Use an access control list (ACL) to restrict access to the files, or copy the files to a folder with restricted access. For example, before you send your debug files to Microsoft support services, we recommended that you remove any sensitive or confidential information.
To apply this option to all packages that the dtexec utility runs, add a DumpOnCodes REG_SZ value to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS\Setup\DtsPath registry key. The data value in DumpOnCodes specifies the error code or codes that will trigger the system to create debug dump files. Multiple error codes must be separated by a semi-colon (;).
If you add a DumpOnCodes value to the registry key, and use the /Dump option, the system will create debug dump files that are based on both settings.
For more information about debug dump files, see Working with Debug Dump Files.
My Mistake. The answer seems to be B Execute the catalog.create_execution_dump stored procedure with the package execution_id.
According to this website you can call the stored proc inside the package to generate the debug files.
https://msdn.microsoft.com/en-us/library/hh231095.aspx
Answer is C.
The answer (C.) appeared to be correct according to this link;
https://msdn.microsoft.com/en-us/library/hh230987(v=sql.110).aspx
The project is using the Package Deployment Model, so option C is correct.
Option B uses the SSISDB catalog stored procedure (catalog.create_execution_dump), but this only useful in a Project Deployment Model scenario.