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 table 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.
