Microsoft SQL Server 2000 Data Transformation Services- P12

Microsoft SQL Server 2000 Data Transformation Services- P12: Data Transformation Services in Microsoft SQL Server 2000 is a powerful tool for movingdata. It’s easy to use, especially when you’re creating transformation packages with the DTSWizard. But it’s also a flexible tool that you can customize to gain a high degree of controlover the transformation of your data.
Nội dung trích xuất từ tài liệu:
Microsoft SQL Server 2000 Data Transformation Services- P12 DTS Packages and Steps526 PART V You can extend the flexibility of DTS by managing packages programmatically. You can man- age 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 envi- ronment to the other. Installation Requirements The earliest version of Visual Basic you can use with DTS in SQL Server 2000 is 5.0 with Service Pack 3. You also have to install the SQL Server client tools on both your development computer and all the computers that are going to be running the packages. Managing Packages with Visual Basic and Stored Procedures 527 CHAPTER 26Saving a Package to Visual Basic 26It’s usually easiest to start the development of a DTS package with the DTS Wizard or the PACKAGES WITH VISUAL BASIC MANAGINGDTS Designer. When you want to work with the package’s objects from Visual Basic, you cansave the package to Visual Basic code. You can do this from both the DTS Wizard and the DTSDesigner. The Save DTS Package dialog from the DTS Designer is shown in Figure 26.1.FIGURE 26.1The DTS Designer and the DTS Wizard both allow you to save a package as a Visual Basic code file.There are several reasons why you might want to save a package to Visual Basic: • To search for and replace variable names, DTS object names, data structure names, or server names throughout a package. • To verify or modify a particular setting for a property in all objects of a package. • To merge two or more packages (although you have to avoid conflicts with object names and connection ID values when you do this). • So that you can continue development of the package in the Visual Basic development environment. • To dynamically modify and execute the package as a part of a Visual Basic application. • To learn about programming with the DTS object model.There is one significant problem in saving a package to Visual Basic—you lose the package’svisual display if you open it up again with the Package Designer.The DTS Package Designer provides an excellent graphical user interface for displaying theflow of a data transformation application. You can place connection, task, and workflow iconsin the places that most clearly illustrate what is happening in the data transformation. DTS Packages and Steps528 PART V This visual display is lost if you save a package to Visual Basic and then execute the Visual Basic code to save the package to one of the other three forms of storage. SaveToSQLServer, SaveToStorageFile, and SaveToRepository all have a parameter called pVarPersistStgOfHost, which is used to store a package’s screen layout information. Unfortunately, this parameter can only be referenced internally by the DTS Designer. It is not possible to use this parameter from Visual Basic to save or re-create the package’s visual dis- play in the designer. When a package is saved from Visual Basic using any of the DTS Save methods, the default layout is created. The wizards also use the default layout. This layout is fine for simple pack- ages, but it’s usually inadequate for more complex ones. Figure 26.2 shows a complex package in the DTS Designer before it ...