70-463 · Question #51
To ease the debugging of packages, you standardize the SQL Server Integration Services (SSIS) package logging methodology. The methodology has the following requirements: - Centralized logging in…
The correct answer is I. Run the package by using the dtexec/dumperror/conn command. Using dtexec with /dumperror and /conn routes package execution and error diagnostics through a SQL Server connection, leveraging SSISDB's built-in centralized logging, T-SQL-accessible catalog views, configurable log levels, and automatic log purge - all without custom…
Question
To ease the debugging of packages, you standardize the SQL Server Integration Services (SSIS) package logging methodology. The methodology has the following requirements:
- Centralized logging in SQL Server
- Simple deployment
- Availability of log information through reports or T-SQL
- Automatic purge of older log entries
- Configurable log details
You need to configure a logging methodology that meets the requirements while minimizing the amount of deployment and development effort. What should you do?
Options
- ADeploy the package by using an msi file.
- BUse the gacutil command.
- CCreate an OnError event handler.
- DCreate a reusable custom logging component.
- EUse the dtutil/copy command.
- FUse the Project Deployment Wizard.
- GRun the package by using the dtexec/rep/conn command.
- HAdd a data tap on the output of a component in the package data flow.
- IRun the package by using the dtexec/dumperror/conn command.
- JRun the package by using the dtexecui.exe utility and the SQL Log provider.
How the community answered
(16 responses)- D6% (1)
- E6% (1)
- F6% (1)
- I81% (13)
Why each option
Using dtexec with /dumperror and /conn routes package execution and error diagnostics through a SQL Server connection, leveraging SSISDB's built-in centralized logging, T-SQL-accessible catalog views, configurable log levels, and automatic log purge - all without custom development.
Deploying via an MSI addresses package distribution but does not configure a centralized logging methodology with automatic purge.
gacutil registers GAC assemblies and has no role in configuring package logging methodology.
An OnError event handler reacts to individual runtime errors but does not establish centralized, automatically purged, T-SQL-accessible logging.
A reusable custom logging component requires substantial development work, violating the requirement to minimize development effort.
dtutil /copy transfers package files and does not configure or enable any logging behavior.
The Project Deployment Wizard deploys the project to SSISDB but does not by itself configure the runtime logging parameters and error dump behavior described.
dtexec /rep /conn reports execution status through a connection but does not provide the full diagnostic capture, configurable detail, and automatic purge combination required.
A data tap captures in-flight data flow data temporarily for debugging and does not provide persistent centralized logging with automatic purge.
The dtexec /dumperror switch captures diagnostic information on errors and the /conn parameter connects execution to a SQL Server instance hosting SSISDB, combining to deliver centralized SQL Server-based logging accessible via catalog views and reports, configurable detail levels through SSISDB logging settings, and automatic purge of old entries via the SSIS Server Maintenance Job - satisfying all requirements with minimal deployment and development effort.
Running via dtexecui.exe with the SQL Log provider logs to SQL Server but requires manual per-execution configuration and does not automatically purge old entries.
Concept tested: SSIS package logging via dtexec with SQL Server connection and error dumps
Source: https://learn.microsoft.com/en-us/sql/integration-services/packages/dtexec-utility
Topics
Community Discussion
No community discussion yet for this question.