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

Actual exam question from Microsoft's 70-767
Question #: 27
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 a database administrator for an e-commerce company that runs an online store. The company has the databases described in the following table.


Each day, data from the table OnlineOrder in DB2 must be exported by partition. The tables must not be locked during the process.
You need to write a Microsoft SQL Server Integration Services (SSIS) package that performs the data export.
What should you use?

  • A. Lookup transformation
  • B. Merge transformation
  • C. Merge Join transformation
  • D. MERGE statement
  • E. Union All transformation
  • F. Balanced Data Distributor transformation
  • G. Sequential container
  • H. Foreach Loop container
Show Suggested Answer Hide Answer
Suggested Answer: E 🗳️
The Union All transformation combines multiple inputs into one output. For example, the outputs from five different Flat File sources can be inputs to the Union All transformation and combined into one output.
References: https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/union-all-transformation
by Dieter at May 5, 2020, 6:39 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
CGLUE
Highly Voted 5 years ago
You need to export a table to 24 destinations and not import 24 sources into one table. Balanced Data Distributor transformation is correct!
upvoted 9 times
Dieter
4 years, 11 months ago
The only thing I am confused about is that BDD does the data distribution using no special rules to make sure that data gets to the correct partition, doesn't it? Thus, this requirement cannot be fulfilled, can it?
upvoted 1 times
...
MobeenAhadKhan
4 years, 10 months ago
After a day data from 24 hourly partitions need to be moved to a table with monthly or yearly data, which is why union or merge appear more logical?
upvoted 1 times
...
...
eceb
Highly Voted 4 years, 9 months ago
it's possible that "data from the table OnlineOrder in DB2 must be exported by partition" means that we have to export all data from the 24 partitions to one destination? in that case union all should be correct. source partition1 source partition2 ..... source partition24 |____________________|_______________________| | union all | destination
upvoted 6 times
JackLKemp
4 years, 6 months ago
No, it clearly says that there are 24 destinations. Union All is a bad answer.
upvoted 2 times
...
...
Hoglet
Most Recent 4 years, 4 months ago
The table is set to Lock Escalation of AUTO. This means that SQL Server will escalate to a PARTITION lock rather than a TABLE lock. I do think that it can START as a TABLE lock under these conditions We are to extract all the partitions, without locking the table. So we can't extract everything in query, we have to split it out by partition, there the ForEach Loop container seems correct. A UNION ALL would allow us to lock ALL the partitions at once, effectively locking the TABLE, and join the results into a single stream. So I don't think it's correct. A Balanced Data Distributor would take the data from a single stream and split it up. If that single stream is locking the table, it can't the be the answer
upvoted 2 times
...
kimalto452
4 years, 5 months ago
you have table with 24 partition, you need EXPORT ALL DATA from this 24 partition to ONE DESTINATION, -->union all is correct
upvoted 1 times
TrungMyLanTJ
4 years, 5 months ago
Data flow has 24 Destinations Correct answer is F : Balanced Data Distributor transformation
upvoted 1 times
...
Hoglet
4 years, 4 months ago
And how would you avoid locking the entire table when you do that? By doing each partition one after the other, with the FOREACH container
upvoted 2 times
...
...
DudeHere
4 years, 5 months ago
Going with ForEach. If there is an explicit transaction in place the select..union becomes part of that and the locks are held until the COMMIT or ROLLBACK. If you have an implicit transaction, by which I mean autocommit, not SET IMPLICIT_TRANSACTIONS, it spans all of the selects. Locks will be held for however long they would be held absent the UNION, governed by isolation level, query options (TABLOCKX etc.), lock escalation, trace flags .. and all the other things that affect this.
upvoted 1 times
...
bhrv
4 years, 6 months ago
How the hell Union all is correct ? I didn't even consider this answer
upvoted 3 times
...
arnoldnowak1992
4 years, 7 months ago
H. Foreach Loop container You have to execute select 24 times, one time for each partition to not fire lock escalation process.
upvoted 3 times
Slava_bcd81
4 years, 7 months ago
good reason
upvoted 1 times
...
...
othman_ee
4 years, 9 months ago
Answer which makes most sense is E
upvoted 2 times
...
Dieter
5 years ago
Sorry. I dont get it. When we want to export _one_ source by partition, the result will be multiple targets, right? Thus, why would UNION ALL be useful for this step?
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