- Shopping Bag ( 0 items )
Ships from: fallbrook, CA
Usually ships in 1-2 business days
Ships from: acton, MA
Usually ships in 1-2 business days
Brian Knight currently writes for SQL Magazine and also has a weekly column at the database website Swynk (www.swynk.com), where he co-runs the SQL Server area. He also works as a Sr. SQL Server DBA and spends most of his time deep in DTS and SQL Server.
Todd Robinson (MCP, MCP+I, MCP+SB, MCSE, MCSE+I, MCSD, MCDBA, MCT, CCNA) lives in Atlanta, GA where he is an internet application developer and data warehousing guru for Metlife. He specializes in MTS/COM+, ASP, XML, SQL Server and related Windows DNA technologies. In addition to his normal job duties at Metlife, he is President of GradesNow.com an internet application service for teachers, students and parents to allow access to grades online.
In the past, as a company upgraded its database system, a programmer would have to reprogram the communication layer through complex code. To remedy the time-to-market problem this created, Microsoft invented different ways to communicate with legacy and modern relational databases via an open standard layer called OLE DB. This breakthrough allowed programmers to communicate with IBM's DB2 database using code similar to that used to communicate to a Microsoft's SQL Server database. Suddenly portability became much easier.
This solved the problem of a program's portability, but what happens when you have a DB2 database that you need to convert to a SQL Server database? For example, your company purchases another company that still uses a legacy VSAM (mainframe driven) system. Your company, however, has its corporate database infrastructure in SQL Server. Converting the VSAM system may be too expensive and time consuming. In the meantime, you need the data that a sales representative is entering into a dumb terminal (DT) from the company you bought to flow to your SQL Server system in the shipping department. In the past, this process would have taken quite a while to program and employee turnover would be so bad from the merger that by the time you finished the development of a workflow system, you would have no one to support it.
This scenario is being repeated throughout the data processing industry daily. As companies merge, they need a way to rapidly develop an application to transition data from any source to any destination. Doing this on the mainframe is expensive and the human resources to do this are dwindling.
Developers also need to be able to transform data. This means that you can have the data look one way on the source and pass the data through a cleansing process to appear a different way on the destination. We can, for example, perform calculations to generate new values for loading, combine multiple columns into a single column, or conversely, break a single column into multiple columns. Imagine that the company you purchased calls sales territories by one set of names, and you'd like to call them by another; this could cause problems when the data was queried, as the values would appear to belong to different territories. However, you can allow them to keep their current system and just scrub the data (that is, make it consistent) as it enters your tracking systems. Another common example is seen when the fields FirstName and LastName need to become a single field named Name. In either case, programmers would have had to spend months staring at their computer screen turning over line after line to perform this simple translation.
DTS to the rescue! Data Transformation Services (DTS) was first introduced with the release of SQL Server 7.0. It was immediately hailed as a revolution in data transformation, since it was, after all, built into SQL Server.
DTS is a collection of utilities and objects that allow you to import, export, and convert data from any data source to any data source, whether to/from another database system (heterogeneous) or to/from another SQL Server. DTS isn't just about data! DTS can move SQL Server objects, execute programs, FTP files, and even has the flexibility to be expanded with your own custom COM components. Data source here refers to any source in an OLE DB, ODBC, or text file format.
In this chapter we will:
The Next Generation in Database Solutions
In SQL Server 6.5, data movement was done through clunky utilities such as Transfer Manager. Transfer Manager allowed us to transfer data with a single step from one SQL Server to another SQL Server. However, we had no access to any other data source, such as Oracle or Microsoft Access.
DBAs also had access to Bulk Copy Program (BCP). BCP is a non-logged event that inserts data into SQL Server in bulk fashion without any type of transformation. A BCP file's columns can be de-limited by a character such as a comma or a tab. BCP files can also use fixed column width to assign where each column begins and ends. The problem with BCPing files was that the schema positioning on the server and in the BCP file had to exactly match. For example, Column l in the flat file had to be Column l in the SQL Server...
Appendix A: Microsoft Data Transformation Services Package Object Library Reference
Appendix B: ADO Object Summary
Appendix C: Visual Basic Functions and Keywords
Appendix D: Variable Naming and Other Code Conventions
Appendix E: Microsoft Scripting Run Time Reference
When did you first start using SQL Server?
BK: I first began to use SQL Server with version 4.2 about 5 years ago. I've enjoyed watching the product grow to its current state through the years and look forward to its future development.
TR: I first began to use SQL Server with version 4.2 about 5 years ago and haven't skipped a version since.
How did you become a Wrox author?
BK: I first realized I wanted to write the DTS book when I saw what a huge need there was on the market for it. I write several weekly articles for SWYNK.COM and some feature articles for SQL Server magazine and I'm constantly asked detailed questions from those articles about the topics we discuss in the book.
TR: I've always been a big fan of Wrox books. I own a lot of them. I contacted Wrox in 1999 to see if there was anything on the horizon related to DTS coming to market. In the latter part of 1999, all I was working on was ETL for a project at MetLife so I became intimate with DTS. As luck would have it, I got to technically review a few chapters on DTS for Rachelle Reese and Frank Millers book, and then was brought on to co-author a book about SQL Server DTS exclusively.
Why is DTS so useful?
BK: DTS is a fantastic technology for transferring data from any OLE DB source to any destination. The fact that it is an open-ended technology makes it extremely useful and compatible with any project.
TR: In a nutshell, you can take data from practically any source and put it in any destination. In addition, the SQL Server specific tasks in SQL 2000, make data and object migration in SQL Server a breeze. However, even though DTS is a part of SQL Server, it is not as tightly coupled as one would think. You could use DTS to migrate data from DB2 to Oracle without any problems.
What do you think makes this book stand out from others on the market?
BK: The book focuses on real world scenarios. Its main competitive advantage is the extended case study that walks a reader through an entire warehouse data loading project. We also show the reader how to create their own tasks and practical uses for DTS in their current applications.
TR: As the custom with all Wrox books, we not only tell you about the technology, but give you real world examples to problems that you have probably come across. In addition, it should appeal to all users. We take you from the beginning, using the wizards, all the way to building your own custom components.
What do you think is the most exciting feature in SQL Server 2000?
BK: The reason that we've been upgrading to SQL Server 2000 is because of the improved functionality in DTS. We've found that we're spending far less time developing customized data load applications and utilizing the strengths that DTS already has built into it.
TR: Obviously, the best part is the advanced features of DTS that were added makes developing complete load processes even easier than before. But there are also significant enhancements with data warehousing and tighter integration with XML. Add to that the greater scalability and improvements in speed, you have a world class RDBMS platform.
What developments would you like to see in the future?
BK: I'm looking forward to another feature, Distributed Partitioned Views maturing. The direction that Microsoft has taken with this feature is fantastic, but very difficult to implement and manage. In the next release, I'm expecting this to improve vastly.
TR: As XML matures, more and more features will be added to allow better integration with the standards that are coming out. Since SQL Servers 2000 release, Microsoft has released 2 add-ons already, which adds additional XML support.
Provided by Wrox Books
Posted October 5, 2002
Posted October 8, 2002
I needed information on scheduling and the customtask interface. I did not find anything helpful in the book at all on these topics. In all fairness though there are a significant number of examples on other topics.Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Posted September 2, 2000
Posted April 9, 2009
No text was provided for this review.
Posted December 30, 2009
No text was provided for this review.