Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Avoid Using DTS
It is as important to know when to use a tool as to know how to use a tool. Although DTS is a very powerful and flexible tool, it is not always the best tool for all ETL jobs. For example, I recently met a developer who needed to dynamically create the SQL Server destination table based on the changing structure of the source table, and then load the data. Although we figured out a nice solution using DTS, the more elegant and easier solution was to use Visual Basic and an ADO recordset. What are the other situations where you might want to consider using an alternative tool to DTS?
When you are moving data with limited transformations between tables, a Transact-SQL query will be significantly more efficient. DTS can be used to workflow several Transact-SQL statements or stored procedures, provide rollback, error notification, and so on. But for raw speed, using Transact-SQL either with the Execute SQL Task, or by itself, should be one of your first considerations.
For moving data between two SQL Servers, use linked servers and Transact-SQL. For multiple sources on one or more SQL servers, consider running a distributed query by using SELECT INTO.