Creating a DTS Package to Run Forced Replication

If you need to synchronize a small number of tables, you might be able to do the job by running in Query Analyzer a simple batch that contains for each table one UPDATE command that replaces, but doesn’t change, the table values on the Publisher server and kicks off the data synchronization process. But if you need to synchronize a large number of tables (more than 10), it is more efficient to create a DTS package that runs the forced replication process. Figure A shows the DTS dialog box where you can create the package.

For example, say the pubs database has several lookup tables and one large table, Table1, that I can partition by time. I need to synchronize all data in the lookup tables and new data entered in Table1 after 06/10/2004. First, I create the pubs.UDL file, which stores the connection to the pubs database. (The SQL Server 2000 Books Online—BOL—section “Data Link Connection” explains how to create universal data link (.udl) files.) Then, I prepared the INI file that Listing A shows and created the five Package Global Variables that Figure A shows. The Dynamic Properties Task reads the INI file and assigns appropriate values to all the global variables. The Semaphor ActiveX Script task in Listing B determines whether you need to run the DTS package for all non-partitioned tables, only for the date-dependent Table1, or for both cases.

The Lookups task in Figure A’s DTS package executes the sp_ForceRepl_Lookup stored procedure that Listing 6 in the main article shows. Figure B shows the Table1 task that executes stored procedure sp_ForceRepl with the four parameters that Listing 5 in the main article shows. In your environment, you can specify as many tasks as necessary by using the technique that Figure B shows. Listing B’s Semaphor task will determine which tasks should be executed. You just need to create a new variable called key in the INI file’s [Run_Options] section and add the five lines of the IF...ELSE…END code to the script in Listing B.

Finally, you need to know how to determine the step name that you’re referencing in the Semaphor ActiveX Script task. The step name appears in the Workflow Properties screen that Figure C shows. To get to this screen, highlight the task, right-click it, and select Workflow, Workflow Properties. The Options tab displays the step name, which is highlighted in Figure C.