ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-767 All Questions

View all questions & answers for the 70-767 exam
Go to Exam

Exam 70-767 topic 1 question 32 discussion

Actual exam question from Microsoft's 70-767
Question #: 32
Topic #: 1
[All 70-767 Questions]

HOTSPOT -
You create a Microsoft SQL Server Integration Services (SSIS) package as shown in the SSIS Package exhibit. (Click the Exhibit button.)

The package uses data from the Products table and the Prices table. Properties of the Prices source are shown in the OLE DB Source Editor exhibit (Click the
Exhibit Button.) and the Advanced Editor for Prices exhibit (Click the Exhibit button.)


You join the Products and Prices tables by using the ReferenceNr column.
You need to resolve the error with the package.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer Hide Answer
Suggested Answer:
There are two important sort properties that must be set for the source or upstream transformation that supplies data to the Merge and Merge Join transformations:
The Merge Join Transformation requires sorted data for its inputs.
✑ The IsSorted property of the output that indicates whether the data has been sorted. This property must be set to True.
✑ The SortKeyPosition property of output columns that indicates whether a column is sorted, the column's sort order, and the sequence in which multiple columns are sorted. This property must be set for each column of sorted data.
If you do not use a Sort transformation to sort the data, you must set these sort properties manually on the source or the upstream transformation.
References: https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/sort-data-for-the-merge-and-merge-join-transformations
by Slava_bcd81 at Oct. 7, 2020, 7:22 a.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
DudeHere
Highly Voted 4 years, 6 months ago
YES - In the source, an ORDER BY clause is used in the statement that is used to load the data. YES - The Source is sorted by RefernceNr therefore need to set the IsSorted and SortKeyPosition NO - Obvious NO - A Lookup can only accept 1 input https://stackoverflow.com/questions/6735733/what-are-the-differences-between-merge-join-and-lookup-transformations-in-ssis The IsSorted property of the output that indicates whether the data has been sorted. This property must be set to True. The SortKeyPosition property of output columns that indicates whether a column is sorted, the column's sort order, and the sequence in which multiple columns are sorted. This property must be set for each column of sorted data. https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/sort-data-for-the-merge-and-merge-join-transformations?view=sql-server-ver15
upvoted 6 times
...
Slava_bcd81
Most Recent 4 years, 7 months ago
the last is Yes
upvoted 2 times
Slava_bcd81
4 years, 7 months ago
the second is No, SortKeyPosition and IsSorted just indicate if the column is sorted and the sort order, they do not provide the sorting
upvoted 1 times
Hoglet
4 years, 4 months ago
Yes and No to your comment Yes because we don't know anything about the Product stream No because we know the Prices stream is sorted, we just need to tell SSIS
upvoted 1 times
...
...
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago