70-467 · Question #166
The Sales database includes a table named Products that contains 42 columns, and two tables that record all product sales transactions. The database does not store aggregated data. The database is…
The correct answer is D. A SQL command-based OLE DB source; Aggregate and Sort transformations. Using a SQL command-based OLE DB source allows selecting and ordering only the 7 required columns at the database level, while Aggregate and Sort transformations handle aggregation and row ordering before writing to the flat file destination.
Question
The Sales database includes a table named Products that contains 42 columns, and two tables that record all product sales transactions. The database does not store aggregated data. The database is continually maintained to provide optimal indexing. The database server is sized appropriately. The Marketing department requests a delimited text file that includes seven fields containing product information from the Products table. You have the following requirements:
- Display the ProductID in the first column. Organize the remaining
columns alphabetically by column heading.
- Sort rows in ProductID order.
- Aggregate product units sold.
- Minimize the time required to return the results.
You create a SQL Server Integration Services (SSIS) package and add the appropriate connection managers and a Data Flow task. You need to develop the Data Flow task. What should you add to the Data Flow task? (More than one answer choice may achieve the goal. Select the BEST answer.)
Options
- AA table-based OLE DB source; a Script component; and a Flat File destination.
- BOnly a SQL command-based OLE DB source and a Flat File destination.
- CA table-based OLE DB source; Lookup, Aggregate, and Sort transformations;
- DA SQL command-based OLE DB source; Aggregate and Sort transformations;
How the community answered
(26 responses)- A4% (1)
- B8% (2)
- C15% (4)
- D73% (19)
Why each option
Using a SQL command-based OLE DB source allows selecting and ordering only the 7 required columns at the database level, while Aggregate and Sort transformations handle aggregation and row ordering before writing to the flat file destination.
A Script component adds unnecessary custom code complexity since all required operations - column selection, aggregation, and sorting - can be accomplished with built-in SSIS components and a SQL command.
A SQL command-based OLE DB source alone cannot perform the aggregation of product units sold from the transaction tables; a dedicated Aggregate transformation in the data flow is required.
A table-based OLE DB source retrieves all 42 columns from the Products table into the data flow, wasting memory and processing resources compared to a SQL command that selects only the 7 required columns, and a Lookup transformation is unnecessary for this task.
A SQL command-based OLE DB source lets you write a SELECT statement that retrieves only the 7 needed columns in the correct order, reducing data volume flowing through the pipeline; the Aggregate transformation computes total units sold from the transaction tables; and the Sort transformation orders rows by ProductID - satisfying all requirements with the minimal set of components needed for best performance.
Concept tested: SSIS data flow component selection for filtered ETL output
Source: https://learn.microsoft.com/en-us/sql/integration-services/data-flow/ole-db-source
Topics
Community Discussion
No community discussion yet for this question.