70-463 · Question #43
You are writing a SQL Server Integration Services (SSIS) package that transfers data from a legacy system. Data integrity in the legacy system is very poor. Invalid rows are discarded by the package…
The correct answer is A. Add a data tap on the output of a component in the package data flow. A data tap in SSIS captures the data flowing along a specific path in a data flow to a CSV file at runtime without requiring any changes to the package itself, making it the lowest-effort approach.
Question
You are writing a SQL Server Integration Services (SSIS) package that transfers data from a legacy system. Data integrity in the legacy system is very poor. Invalid rows are discarded by the package but must be logged to a CSV file for auditing purposes. You need to establish the best technique to log these invalid rows while minimizing the amount of development effort. What should you do?
Options
- AAdd a data tap on the output of a component in the package data flow.
- BDeploy the package by using an msi file.
- CRun the package by using the dtexecui.exe utility and the SQL Log provider.
- Duses the dtutil/copy command.
- EDeploy the package to the Integration Services catalog by using dtutil and use SQL Server to
- FCreate an OnError event handler.
- Guses the Project Deployment Wizard.
- HUse the gacutil command.
- ICreate a reusable custom logging component.
- JRun the package by using the dtexec/rep/conn command.
How the community answered
(27 responses)- A74% (20)
- B15% (4)
- D7% (2)
- J4% (1)
Why each option
A data tap in SSIS captures the data flowing along a specific path in a data flow to a CSV file at runtime without requiring any changes to the package itself, making it the lowest-effort approach.
A data tap allows you to attach a CSV output to any data flow path in a deployed SSIS package without modifying or redeploying the package. It is configured externally, typically via the SSISDB catalog or T-SQL, and captures the exact rows passing through that path - including invalid rows being discarded - directly to a flat file. This requires no development changes to the package, minimizing development effort.
Deploying via an MSI file is a package deployment mechanism and does not provide any mechanism for capturing or logging invalid rows to a CSV.
dtexecui with the SQL Log provider logs package-level events to SQL Server, not individual invalid rows to a CSV file.
dtutil /copy copies SSIS package files between storage locations and has no capability for logging row-level data.
An OnError event handler fires on package or task errors, not on individual invalid row-level discards within a data flow transformation.
The Project Deployment Wizard deploys SSIS projects to the catalog and provides no row-level CSV logging capability.
gacutil registers .NET assemblies in the GAC for custom component deployment and has no logging functionality.
Creating a reusable custom logging component requires substantial development effort, violating the constraint to minimize development work.
dtexec /rep generates a formatted report of package execution results and does not capture row-level data to a CSV file.
Concept tested: SSIS data tap for capturing invalid rows to CSV
Source: https://learn.microsoft.com/en-us/sql/integration-services/data-flow/data-flow-path
Topics
Community Discussion
No community discussion yet for this question.