Is DTS (MS SQL) really suitable as the sole ETL tool when it comes to the daily operation (and support) of a data warehouse? My primary concern is about how easy it is to create jobs and subjobs, schedule these in a flexible manner (e.g., reloading only one or more tables). Also it seems to be hard to see where things go wrong in the load process, when they do and why. Statistics is another issue, flexible access to the number of rows loaded into which tables (last load and historically), how long each translation/loading took, an overview of which steps that went wrong, etc. I do not see that DTS supports this kind of stuff in an easy way without setting up some shell to use it from. What’s your opinion? Have you seen any successful data warehouse implementation that uses only DTS without any extra administrative shell?

Scott Howard’s Answer: I don't think DTS was ever intended to be a full-featured ETL tool. You have accurately expressed its limitations, thus also the reason for all the many successful ETL vendors that thrive today. Take a good look at those other ETL tools with your DTS shortcomings in mind as those are the major features important to you. Once a vendor starts to wow you with their many ancillary features, you may lose sight of why you need an ETL tool in the first place.

Chuck Kelley’s Answer: DTS is a good as the rest of the second generation ETL products when it comes to the things that you question. You will need a good administrative shell, for the near term, to get all the information you need to administer a good DW environment.

David Marco’s Answer: The answer to your question "Is DTS really suitable as the sole ETL tool" is that it all depends (great consulting answer). If you work at a small company with limited amounts of data, limited budget and you have a SQL Server environment then DTS could be your sole ETL tool. In general, Global 2000 companies will need to go with a more industrial strength tool such as Ascential DataStage or Informatica PowerCenter. At EWS we have a comparison study of Ascential DataStage, Informatica PowerCenter and MS DTS. This study can be purchased by visiting http://www.ewsolutions.com/research_paper.asp.

Clay Rehm’s Answer: DTS is suitable in a "small" data warehouse or in a data mart build/refresh environment, depending on what small means to you. You get what you pay for – since DTS comes free with SQL Server, it is a great utility but it will not do everything that Informatica, Ascential DataStage, DataMirror, D2K, Ab Initio, Sagent, Mercator, etc will do. If you can get DTS to work in your environment, all the power to you. If not, than I suggest you evaluate the tools in the market place AFTER you have developed your evaluation criteria.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access