I have been involved in data warehousing involving extracting from heterogeneous systems. When creating DTS packages I always follow these conventions:
[ul]
[li]Use native drivers where possible[/li]
[li]In the transform data task properties, under the Transformations Tab, delete the individual copy column transformations by clicking Delete All then create a new copy column transformation for all columns by clicking Select All, New, Copy Column, OK. (The depiction of this will be a set of converging arrows, rather than individual arrows)[/li]
[li]In the transform data task properties, under the Options Tab, SQL Server group, select Use Fast Load, Keep NULL values and Table Lock.[/li]
[/ul]
When running the package, always schedule the task to run on the server. In my organisation, there is 100mb/s ethernet between servers and only 10mb/s to PCs. You can schedule the task automatically, but this creates an encrypted UID for the package and is not easy to change. Instead create a Job with one step with the following parameters:
[ul]
[li]Type: Operating System Command(CmdExec)[/li]
[li]Command: dtsrun /s <your sql server name> /n <your dts package name> /e[/li]
[/ul]
When created either schedule the job, or right click to run ad hoc.
[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]