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 99 discussion

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

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are developing a Microsoft SQL Server Integration Services (SSIS) package.
You are importing data from databases at retail stores into a central data warehouse. All stores use the same database schema.
The query being executed against the retail stores is shown below:

The data source property named IsSorted is set to True. The output of the transform must be sorted.
You need to add a component to the data flow.
Which SSIS Toolbox item should you use?

  • A. CDC Control task
  • B. CDC Splitter
  • C. Union All
  • D. XML task
  • E. Fuzzy Grouping
  • F. Merge
  • G. Merge Join
Show Suggested Answer Hide Answer
Suggested Answer: C 🗳️
by Rmznd at Feb. 9, 2020, 2:01 p.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
Rmznd
Highly Voted 5 years, 2 months ago
This should be F. Merge
upvoted 16 times
Hoglet
4 years, 4 months ago
It is Merge, not Union All While Union All will accept Sorted Input, the output would not necessary be Sorted, rather the streams will be "randomly interleaved". Merge requires Sorted input and will interleave the results based on the Sort Keys
upvoted 1 times
...
...
radissoufan
Highly Voted 5 years ago
Guys...it multiple data sources its 2+ data sources.. and they are already sorted by query and their sort of property.. so union all and merge are the same.. but union all is the correct answer because it can accept more than 2 data sources .. eg. 3 store's, 4 store's tables... etc.. so union all is correct
upvoted 15 times
Lex_P
5 years ago
Output must be sorted. Union all doesn't sort output
upvoted 3 times
Anette
4 years, 8 months ago
Even Merge doesn't sort output, it requires input to be sorted and we have it by using ORDER BY. So, your argument doesn't stand. And since we must join more than one data source only Union All fits here.
upvoted 1 times
...
...
...
melvin9900
Most Recent 4 years, 7 months ago
All stores use the same database schema. - We don't know how many stores in there .If it's more than 2 then definitely Union all Which is not provided in the question. Union all doesn't sort the output even if the Source is Sorted. It just provides a random order output irrespective of input data is sorted or Not. So I am going for "MERGE"
upvoted 1 times
...
Cococo
4 years, 7 months ago
In DW you will need to know which store provided the info, so there is a Store_ID and all data from each store is sorted. Microsoft is saying that Merge and Merge Join are limited to 2 sources only, so let's say you prepared the data as two sources and now you want to sort it by what? by ID? 100 shops can have the same ID 101... so, I think there are Store_IDs as well and Union All will do the job
upvoted 1 times
...
MattB
4 years, 8 months ago
"you need to add *a* component" - answer is merge
upvoted 1 times
...
othman_ee
4 years, 9 months ago
Correct Answer F: under the explanation of Union you can find: The transformation inputs are added to the transformation output one after the other; no reordering of rows occurs. If the package requires a sorted output, you should use the Merge transformation instead of the Union All transformation. https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/union-all-transformation?view=sql-server-ver15
upvoted 6 times
...
eceb
4 years, 9 months ago
I think the question is a bit confusing... With sorted sources we can use a Merge, that will return a sorted output, but this component only accepts two sources, and the question says "all stores" so maybe there are more than 2 sources. In that case a possible option is to use more Merge components to continue merging other sources in pairs, and the output will remain sorted. If we use a Union All component we can join data from more than 2 sources but we'll need a sort component to sort the output. Depending on the point of view, I think that Union all and Merge both could be correct. The question says "which item should you use?" to be strict with this, using only one type of item, the correct answer should be merge (more than one merge components if we have more than 2 sources)
upvoted 4 times
eceb
4 years, 9 months ago
solution with union all: src1 src2 src3 ⠀|_______|________| ⠀⠀⠀⠀⠀ | ⠀⠀⠀union all ⠀⠀⠀⠀⠀ | sort ⠀⠀⠀⠀⠀| ⠀⠀⠀destination solution with merge components: src1 src2 src3 |________|⠀⠀⠀⠀| ⠀⠀⠀ |⠀⠀⠀⠀⠀⠀ | ⠀⠀merge ⠀⠀⠀⠀ | ⠀⠀⠀ | __________| ⠀⠀⠀⠀⠀⠀ | ⠀⠀⠀⠀merge ⠀⠀⠀⠀⠀⠀ | ⠀⠀⠀destination what do you think??
upvoted 3 times
arnoldnowak1992
4 years, 6 months ago
Your are writing about merge join, not merge.
upvoted 1 times
...
...
...
mohroshdy
5 years, 2 months ago
The output of the transform must be sorted so we have to use Merge
upvoted 3 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