70-463 · Question #54
You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure database into a data warehouse. The source system contains redundant or inconsistent data. W
The correct answer is I. Configure the output of a component in the package data flow to use a data tap.. A data tap lets you capture data flowing through a specific data flow path at runtime and write it directly to a flat file, meeting both the omission and logging requirements with minimal development effort.
Question
You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure database into a data warehouse. The source system contains redundant or inconsistent data. When the package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a text file for further analysis. You need to establish the best technique to log these invalid rows while keeping the amount of development effort to a minimum. What should you do?
Options
- AAdd an OnError event handler to the SSIS project.
- BOpen a command prompt and execute the package by using the SQL Log provider and
- CUse an msi file to deploy the package on the server.
- DOpen a command prompt and run the gacutil command.
- ERun the dtutil command to deploy the package to the SSIS catalog and store the
- FOpen a command prompt and run the dtutil/copy command.
- GCreate a reusable custom logging component and use it in the SSIS project.
- HConfigure the SSIS solution to use the Project Deployment Model.
- IConfigure the output of a component in the package data flow to use a data tap.
- JOpen a command prompt and run the dtexec/rep/conn command.
How the community answered
(20 responses)- D15% (3)
- G5% (1)
- H5% (1)
- I75% (15)
Why each option
A data tap lets you capture data flowing through a specific data flow path at runtime and write it directly to a flat file, meeting both the omission and logging requirements with minimal development effort.
An OnError event handler responds to component-level errors but does not provide a mechanism to capture and redirect specific invalid data rows to a text file as part of the data flow.
Creating a reusable custom logging component requires significant development effort, which violates the requirement to minimize development work.
The Project Deployment Model governs how packages are deployed and managed; it does not address the data flow requirement to log invalid rows to a text file.
Configuring a data tap on the error output or a conditional split output of a data flow component allows SSIS to intercept rows at that path and write them to a CSV file without requiring additional transformations, destinations, or custom code. This is a built-in SSIS feature that requires minimal configuration and precisely matches the requirement of omitting invalid rows from the main flow while capturing them in a text file for analysis.
Concept tested: SSIS data tap for capturing invalid rows to a flat file
Source: https://learn.microsoft.com/en-us/sql/integration-services/data-flow/data-flow
Topics
Community Discussion
No community discussion yet for this question.