What is the best approach using DTS

[sujosh]

I have several tables in Oracle with 30 - 50 million records. I want to write a DTS package and export to MSSQL Server. I have been currently using Enterprise manager to create a DTS package and run it after the package is created.

What is the best way to approach this? Should I use DTS run utility?

Any help is greatly appreciated!

Thanks

 

[TheJon]

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]

 

[sujosh]

Thanks Jon!! I am trying to create a package and see how this goes..

Thanks again!