Enhancing Data-Pump Performance
By default, the DTS Import/Export Wizard creates packages with many-to-many column mappings, whereas DTS Designer assigns one-to-one column mappings to transformations to improve readability.
Each mapping is a separate transformation that must separately invoke the script engine. If you have a lot of data or a lot of transformations, this can adversely affect performance.
When you are using DTS Designer, and you have a lot of data or many transformations, consider mapping as many transformations as possible to a many-to-many.
If you have to do scripting in the Transform Data or Data Driven Query Tasks, you may be able to reduce the number of times a block of code needs to be executed by turning on the multiphase data pump and placing the code in the Pre source data function. This function executes only once for the task versus the main function, which executes for every record passing through the task.