Microsoft SQL Server 2000 Data Transformation Services- P12: Data Transformation Services in Microsoft SQL Server 2000 is a powerful tool for moving data. It’s easy to use, especially when you’re creating transformation packages with the DTS Wizard. But it’s also a flexible tool that you can customize to gain a high degree of control over the transformation of your data. | DTS Packages and Steps 526 Part V You can extend the flexibility of DTS by managing packages programmatically. You can manage packages with any programming language that supports COM. This chapter focuses on managing packages with Visual Basic and with the OLE Automation system stored procedures available in SQL Server. Much of the information in this chapter is also relevant to other programming languages. Many of the chapters in this book have examples that show how to use Visual Basic with DTS. Chapter 12 The Execute SQL Task and Chapter 18 The Execute Package Task have examples of using the OLE Automation stored procedures. Chapter 30 Programming with the DTS Object Model has a summary of the programming objects available in DTS. Chapters 31 Creating a Custom Task with VB and 32 Creating a Custom Transformation with VC show how to extend DTS functionality by programming custom tasks and custom transformations. Working with DTS Packages in Visual Basic You have many advantages when you use Visual Basic with DTS instead of using the DTS management tools built into SQL Server 2000 The ability to integrate DTS functionality with the rest of your application. More flexibility in responding to errors generated by DTS. A more convenient development environment for writing programming code. There are also some disadvantages The DTS Designer and the DTS Wizard generate many DTS objects automatically. A good deal of programming is needed to re-create these structures using Visual Basic. The DTS Designer gives a visual representation of the programmatic flow in the data transformation. This visual representation is missing when you work with DTS in Visual Basic. Because there are distinct advantages to working with DTS in Visual Basic and to working with the SQL Server 2000 DTS design tools the best development strategy often uses both. Because you can save a package as Visual Basic code you can quickly move from one environment to the other. Installation Requirements The
