Question:
I am trying to build a warehouse. The steps involved are:
- Make a snapshot replication of the OLTP onto the staging area.
- Run a DTS package that makes transformations and loads the data warehouse.
Since I want to automate the process, I want to schedule Step 1 and then, on its completion, start Step 2. Because Step 1 is in Replication module and Step 2 in DTS module of SQL Server 7, I am unable to integrate the two. How do I get this to work?
Answer:
I am not clear on what you are trying to accomplish. Replication, once it’s defined on your OLTP system, should be running in a hands-off kind of way, not requiring you to initiate it. Either your Publisher is going to push it or your subscribers are going to pull it, but in no case should you be having to manage the updates yourself. That’s up to your schedule and the log reader.
I think you need to revisit the goals of the process and iron out some duplication of tasks between the transformation services and replication.
