Migrating data from operational systems typically involves copying the data to an intermediate database before it is finally copied to the data warehouse. Copying data to an intermediate database is necessary if data should be scrubbed.
Copying data should occur ideally during a period of low activity on the operational system. Otherwise, system performance may degrade, affecting any users. Additionally, if the data warehouse is composed of data from multiple interrelated operational systems, then it is important to ensure that data migration occurs when the systems are synchronized. If the operational systems are not synchronized, the data in the warehouse can produce unexpected results when queried.
The DTS Import and DTS Export wizards can be used to create a series of tasks that copy data from many heterogeneous operational systems to an intermediate database running on Microsoft® SQL Server™. Alternatively, you can use a Microsoft ActiveX® script with DTS to scrub the data, and then copy it directly to the SQL Server data warehouse, avoiding the need for an intermediate database.
| Data Transformation Services Import and Export Wizards |