Performance Tuning Tips for SQL Server DTS

By default, when you use the Transform Data task in SQL Server 2000 DTS, each column to be transformed from one data source to another is assigned its own COM object. Because of this, the transformation process can be slowed as the COM objects are opened and closed. A more efficient, faster method would be to use only one COM object, not multiple COM objects. Fortunately, this is relatively easy to do.

To modify the Transform Data task from multiple COM objects into one, follow these steps:

• From the "Transformations" tab of a Transform Data task object that has already been assigned data sources, click on the "Delete All" button to remove all of the current transformations. This is the step that deletes all of the COM objects.
• Now click the "New" button, and select "Copy Column" from the "Create New Transformation" dialog box, then click on "OK." This is the step that create a single, new COM object that can be used for your transformation.

You will find that performing these two simple steps can boost the performance of the transformation in your DTS package. [2000] Updated 1-14-2005

*****

When creating new transformations using the SQL Server 2000 DTS Transform Data task, you are given nine different ways in order to transform data. For best performance, assuming it will do the job you need, select the "Copy Column" option. If what you need to do cannot be done using "Copy Column", the next fastest option is "ActiveX script". [2000] Updated 1-14-2005

*****

The Data Pump Task is faster than a Data Driven Query within a DTS package if there is a one-to-one mapping of the columns and no transformations are involved when moving data between tables. But if there are transformations involved, then a Data Driven Query will offer better performance. [2000] Updated 1-14-2005