nerdexam
Microsoft

70-463 · Question #10

You are developing a data flow transformation to merge two data sources. One source contains product data and the other source contains data about the country in which the product was manufactured…

The correct answer is C. Set the appropriate SortKeyPosition properties on the data sources. D. Set the IsSorted property on both data sources. This question tests the prerequisite configuration required for the SSIS Merge Join transformation to function correctly - it requires SSIS to be explicitly told that its inputs are sorted.

Extract and transform data

Question

You are developing a data flow transformation to merge two data sources. One source contains product data and the other source contains data about the country in which the product was manufactured. Both data sources contain a two-character CountryCode column and both use SQL Server. Both data sources contain an ORDER BY clause to sort the data by the CountryCode column in ascending order. You use a Merge Join transformation to join the data. You need to ensure that the Merge Join transformation works correctly without additional transformations. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

Exhibit

70-463 question #10 exhibit

Options

  • AChange the ORDER BY clause on the product source to order by ProductName.
  • BChange the Merge Join transformation to a Merge transformation.
  • CSet the appropriate SortKeyPosition properties on the data sources.
  • DSet the IsSorted property on both data sources.

How the community answered

(39 responses)
  • A
    13% (5)
  • B
    8% (3)
  • C
    79% (31)

Why each option

This question tests the prerequisite configuration required for the SSIS Merge Join transformation to function correctly - it requires SSIS to be explicitly told that its inputs are sorted.

AChange the ORDER BY clause on the product source to order by ProductName.

Changing the ORDER BY to ProductName would remove the CountryCode sort that the Merge Join requires for the join key, breaking the transformation entirely.

BChange the Merge Join transformation to a Merge transformation.

The Merge transformation concatenates two sorted streams into one without performing a key-based join; it does not provide the lookup/join semantics the scenario requires.

CSet the appropriate SortKeyPosition properties on the data sources.Correct

SortKeyPosition must be set on each output column that participates in the sort to indicate the sort order and position, which tells SSIS how the data is ordered for the join.

DSet the IsSorted property on both data sources.Correct

Setting IsSorted to True on the data source output informs SSIS that the upstream data is already sorted, which is required for Merge Join to accept the input without inserting a Sort transformation.

Concept tested: SSIS Merge Join IsSorted and SortKeyPosition metadata configuration

Source: https://learn.microsoft.com/en-us/sql/integration-services/data-flow/transformations/merge-join-transformation

Topics

#Merge Join#SortKeyPosition#IsSorted property#data flow transformation

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-463 Practice