March 28, 2011 at 10:09 am
Hi
I'm currently studying for the SQL 2008 Business Intelligence MCTS exam and I've come across a question in a practice test as follows:
You are creating an SSIS data flow and need to refresh existing data in a sql 2008 table. Select the best option:
A - use an oledb destination
B - use a sql server destination
C - use a data conversion transformation
D - use a fuzzy grouping transformation
The given answer is B - use a sql server destination - but I can't find any documentation on MSDN or elsewhere that specifies this functionality. I've set up an SSIS dataflow using a sql server destination and can't see any way of implementing this solution.
I suspect that this may be something to do with the MERGE statement, but again can't find any evidence that this is possible using SSIS and a SQL Server Destination.
I've also seen an article on codeplex that says:
"SQL Server can use the MERGE statement to perform an upsert operation efficiently, by batching rows of data instead of processing rows one at a time. However, Integration Services does not currently provide a built-in destination "
The article is dated November 2008 so I'm wondering if anything has changed since then.
Can anyone shed any light on this?
Thanks
March 28, 2011 at 1:04 pm
My opinion...you may be reading too much into the word "refresh" in the question. I have found many of the exam questions to be delightfully ambiguous. Based on the set of answers I would interpret the question as "what is the preferred Data Flow Destination component to choose when loading data into an empty SQL Server table". As the word refresh can mean many things to many different people I would substitute "load" for "refresh" making B a good choice.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2011 at 11:35 pm
opc.three is right, with "refresh" they just mean load data into the destination.
This is how you solve such questions:
answer C and D can immediately be eliminated, as they have nothing to do with saving data into a table.
A and B are both correct, they are alternatives of each other. So the real question is: which component of the two is preferred?
Since SQL 2008 is mentioned, you should pick SQL Server Destination, as it is the best choice according to Microsoft.
(although it has more restrictions than the OLE DB Destination and the performance difference is negligable when using fast load in the OLE DB)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 29, 2011 at 1:18 am
Thanks guys your comments are very helpful
Apparently there is never any reason to use SQL Server Destination in the real world. And members of the SSIS product team have said this publicly. But I suppose I have to learn what Microsoft want me to 'know' in order to pass the exam
March 29, 2011 at 1:35 am
brian.melvin (3/29/2011)
But I suppose I have to learn what Microsoft want me to 'know' in order to pass the exam
Indeed, that's how it works 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply