You are a database developer. You provide solutions by using SQL Server 2008 in an
enterprise environment. Your online transaction processing (OLTP) database contains a
tabled named SalesOrders. Your data warehouse contains a table named factBuyingHabits.
The factBuyingHabits table has no indexes. You need to synchronize data between the two
tables on a weekly basis. The synchronization process has the following requirements:
·New records in the SalesOrders table are inserted in the factBuyingHabits table. ·When a
record is modified in the SalesOrders table, the modification is updated in the
factBuyingHabits table. ·Records that are deleted from the SalesOrders table are also
deleted from the factBuyingHabits table. You need to design an appropriate synchronization
solution. You want to achieve this goal by using minimum amount of coding and
administrative efforts. What should you do?
A.
Design an SSIS package each for the INSERT, UPDATE, and DELETE operations.
Schedule a job to run this package.
B.
Design a single SSIS package that uses the Slowly Changing Dimension task. Schedule
a job to run this package.
C.
Write one stored procedure that contains a MERGE statement to perform the INSERT,
UPDATE, and DELETE operations. Schedule a job to run the stored procedure.
D.
Write three stored procedures each for the INSERT, UPDATE, and DELETE operations.
Schedule a job to run the stored procedures in a sequential manner.
Explanation: