Microsoft SQL Server 2000 Data Transformation Services- P8

Microsoft SQL Server 2000 Data Transformation Services- P8: 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- P8 Other Data Movement and Manipulation Tasks326 PART III The File Transfer Protocol (FTP) task allows you to retrieve files from network or Internet sites. NOTE The lack of FTP integration was a weakness of DTS in SQL Server 7.0. It didn’t prevent DTS from being used, but it reduced the convenience of the DTS development envi- ronment. You had to coordinate your FTP processes with the execution of your DTS packages, ensuring that the files were moved to the proper directories before the DTS package execution began. Now, with SQL Server 2000, FTP has become another step in the DTS process. When to Use the File Transfer Protocol (FTP) Task The FTP task can be used to move files or directories in your local network. It can also be used to get files from remote Internet sites. It cannot be used to send files to Internet sites. TIP It would be more convenient if the FTP task could be used to send files to Internet sites as well as getting files. There are tools that make an FTP site appear like a Windows directory. One of these tool, WebDrive (www.webdrive.com) is included on the book’s CD. With WebDrive or a similar tool you can use the FTP task to send files to remote Internet sites. When you’re using the FTP task, it’s important to recognize the possibilities for integration with the Dynamic Properties task and with the many ways you can use global variables. You can use the Dynamic Properties task to change the source, the destination, and the files involved with the FTP. You can set these values with global variables that have themselves been set with DTSRun, by another DTS package, by an Execute SQL task, or with an ActiveX Script task. There are times when an ActiveX Script task is more convenient than an FTP task for moving files around your local network. See the discussion of using the FileSystemObject for file and directory manipulation in Chapter 16, “Writing Scripts for an ActiveX Script Task.” The File Transfer Protocol (FTP) Task 327 CHAPTER 14Creating the Task and Setting Its PropertiesYou can create the FTP task with the Package Designer or with code. The last section of thischapter has an example of creating the task with code.The FTP Site tab of the File Transfer Protocol Task Properties dialog is shown in Figure 14.1.This is the place where you enter information about the site from which you want to FTP files.FIGURE 14.1The FTP task allows you to move files from a local network or from an Internet site.The primary choice you make on the FTP Site tab is the type of source location. You canchoose either an Internet site or a network directory. This choice is implemented in code by 14setting the SourceLocation property to one of the DTSFTPSourceLocation constants: PROTOCOL (FTP) • 0—DTSFTPSourceLocation_InternetSite (The default choice) TRANSFER THE FILE • 1—DTSFTPSourceLocation_Directory TASKMost of the other properties you set on the FTP Site fill in the details about the source of theFTP transfer: • SourceSite—Must be used for an Internet source. The property is set to an FTP site name, such as ftp.mcp.com. • SourceUserName—“Anonymous” is used as the default, which is the standard username for making a read-only connection to an Internet FTP site. Other Data Movement and Manipulation Tasks328 PART III • SourcePassword—The email address of the user is often used when connecting as “anonymous.” • NumRetriesOnSource—The number of attempts to be made before the task terminates with a failure. The other property you set on the FTP Site is the directory path, the DestSite property. You have to specify a local network directory for the destination of the FTP task. The FTP Transformation tab, shown in Figure 14.2, lets you choose files from the source that you want to be transferred. The files appe ...