"Martin Brown via SQLMonster.com" wrote:

> Hi,
>
> I have to update 9 tables on my local SQL Server each night, built with the data from two remote databases (1 SQL Server and 1 FoxPro). The data in these remote databases contains duplicates.
>
> The method I have chosen is to create a DTS package on my local machine, which creates 9 temporary tables, and then drops and recreates the 9 permanent tables. I then import the data from each remote server into the temporary tables. Finally, I run a SQL DISTINCT query which inserts unique values into the permanent tables (removing the duplicates).
>
> I am new to DTS. My package consists of nine workflows (1 for each table) as follows:
>
> 1) Execute SQL Task (drop permanent table)
> 2) Execute SQL Task (create permanent table)
> 3) Execute SQL Task (drop temporary table)
> 4) Execute SQL Task (create temporary table)
> I then have two parallel 'on success' workflows leaving step 4. One goes to the remote SQL Server connection, the other goes to the remote FoxPro connection. Both of these connections then have a Transform Data Task which copies the remote data into the temporary local table. Both of these Transform Data Tasks point to the same local SQL Server connection. Finally, an 'on completion' workflow points to a final Execute SQL Task which moves the distinct data into the relevant permanent table.
>
> The above process is done 9 times (once for each table). And so my package consists of 9 rows of the above.
>
> I wonder if anyone experienced with DTS could offer any comments or advice to a beginner regarding the suitability or ways to improve the performance of my package.
>
> Grateful for any comments,
> Martin
>
> --
> Message posted via http://www.sqlmonster.com