Microsoft SQL Server 2000 Data Transformation Services- P8

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
51
lượt xem
3
download

Microsoft SQL Server 2000 Data Transformation Services- P8

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Microsoft SQL Server 2000 Data Transformation Services- P8: 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.

Chủ đề:
Lưu

Nội dung Text: Microsoft SQL Server 2000 Data Transformation Services- P8

  1. Other Data Movement and Manipulation Tasks 326 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.”
  2. The File Transfer Protocol (FTP) Task 327 CHAPTER 14 Creating the Task and Setting Its Properties You can create the FTP task with the Package Designer or with code. The last section of this chapter 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.1 The 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 can choose either an Internet site or a network directory. This choice is implemented in code by 14 setting the SourceLocation property to one of the DTSFTPSourceLocation constants: PROTOCOL (FTP) • 0—DTSFTPSourceLocation_InternetSite (The default choice) TRANSFER THE FILE • 1—DTSFTPSourceLocation_Directory TASK Most of the other properties you set on the FTP Site fill in the details about the source of the FTP 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.
  3. Other Data Movement and Manipulation Tasks 328 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 appear in a list on the left side of the screen. You can move some or all of them over to the list on the right so that they will be included in the transfer. FIGURE 14.2 Choose files to transfer on the FTP Transformation tab. These selections are implemented by the SourceFileName property. This property is a semi- colon-delimited string that contains the filenames, file paths, and file sizes in bytes. The string looks like this: “‘FileOne.dat’;’ftp.mcp.com’;’1234’;’FileTwo.dat’;’ftp.mcp.com’; ➥’4312’;’FileThre.dat’;’ftp.mcp.com’;’314’;” If you specify the site in the SourceSite property, you do not need to include the second para- meter. Also, when you are creating this task programmatically, you do not need to specify the size of the file. You do still need to include the spaces for these values, however.
  4. The File Transfer Protocol (FTP) Task 329 CHAPTER 14 This string is equivalent to the preceding one if ftp.mcp.com is assigned to the SourceSite property: “‘FileOne.dat’;’’;’’;’FileTwo.dat’;’’;’’;’FileThre.dat’;’’;’’;” The only other choice you have on the FTP Transformation tab is a check box for specifying whether or not files with the same name should be overwritten. This choice is implemented with the NonOverwritable property. This property has a default value of TRUE, which means that files are not overwritten. Creating the Task in Visual Basic I have created a Visual Basic procedure, fctCreateFTPTask, which creates a step, a task, and a custom task for an FTP task. All the properties of the task can be set with this procedure. You can find the code for it in the directory for Chapter 14 on the book’s CD as a Visual Basic Project, with files CreateFTPTask.vbp, CreateFTPTask.frm, and CreateFTPTask.bas. The code for fctCreateFTPTask is shown in Listing 14.1. The procedure needs some utility functions that are included with the code listings on the CD. The project requires references to the Microsoft DTSPackage Object Library and the Microsoft DTS Custom Tasks Object Library. LISTING 14.1 The Visual Basic Code to Create an FTP Task Option Explicit Public Function fctCreateFTPTask( _ pkg As DTS.Package2, _ Optional sBaseName As String = “FTPTask”, _ Optional sDestSite As String = “C:\Temp”, _ 14 Optional lNumRetriesOnSource As Long = 0, _ PROTOCOL (FTP) Optional sSourceFileName As String = “”, _ TRANSFER THE FILE Optional lSourceLocation As Long = 0, _ Optional sSourcePassword As String = “”, _ TASK Optional sSourceSite As String = “”, _ Optional sSourceUserName As String = “anonymous”, _ Optional bNonOverwritable As Boolean = True) As String On Error GoTo ProcErr Dim stp As DTS.Step2 Dim tsk As DTS.Task Dim cus As DTSCustTasks.DTSFTPTask
  5. Other Data Movement and Manipulation Tasks 330 PART III LISTING 14.1 Continued ‘Check to see if the selected Base name is unique sBaseName = fctFindUniqueBaseName(pkg, sBaseName) ‘Create task and custom task Set tsk = pkg.Tasks.New(“DTSFTPTask”) Set cus = tsk.CustomTask With cus .Name = “tsk” & sBaseName .Description = sBaseName .NonOverwritable = bNonOverwritable If sDestSite “” Then .DestSite = sDestSite End If If sSourceFileName “” Then .SourceFilename = sSourceFileName End If If sSourceSite “” Then .SourceSite = sSourceSite End If .SourceLocation = lSourceLocation If .SourceLocation = 0 Then If sSourcePassword “” Then .SourcePassword = sSourcePassword End If If sSourceUserName “” Then .SourceUsername = sSourceUserName End If .NumRetriesOnSource = lNumRetriesOnSource End If End With pkg.Tasks.Add tsk
  6. The File Transfer Protocol (FTP) Task 331 CHAPTER 14 LISTING 14.1 Continued ‘Create step for task Set stp = pkg.Steps.New With stp .Name = “stp” & sBaseName .Description = sBaseName .TaskName = tsk.Name End With pkg.Steps.Add stp fctCreateFTPTask = stp.Name Set tsk = Nothing Set cus = Nothing Set stp = Nothing ProcExit: Exit Function ProcErr: MsgBox Err.Number & “ - “ & Err.Description fctCreateFTPTask = “” GoTo ProcExit End Function Conclusion The FTP task is a helpful addition to DTS in SQL Server 2000. This task furthers the goal of creating an integrated, RAD environment for data movement and manipulation. 14 PROTOCOL (FTP) TRANSFER THE FILE TASK
  7. The Transfer Databases and CHAPTER 15 Other Transfer Tasks IN THIS CHAPTER • When to Use the Transfer Databases and Other Transfer Tasks 334 • Creating the Tasks and Setting Their Properties 335 • The Transfer Databases Task 336 • The Transfer Logins Task 337 • The Transfer Jobs Task 338 • The Transfer Master Stored Procedures Task 339 • The Transfer Error Messages Task 340 • Creating the Tasks in Visual Basic 341
  8. Other Data Movement and Manipulation Tasks 334 PART III The five tasks described in this chapter are the ones used by the Copy Database Wizard to move databases and associated meta data from one SQL Server to a separate SQL Server 2000. It’s important to be able to move meta data along with the transfer of databases. SQL Server stores most of the meta data needed for database manipulation inside each individual database, but there is a significant amount of meta data that is stored in the Master and Msdb system databases. Centralized meta data storage makes it possible for the meta data to be used by all the data- bases on a server. But the centralized meta data becomes a problem when you move an indi- vidual database to a new server. Unless you include all the needed meta data, the database will not operate properly on its new server. Each of the four additional transfer tasks involves the movement of a particular kind of data: • Logins, stored in master • System stored procedures, stored in master • Error messages, stored in master • Jobs, stored in msdb The most common difficulty I have seen in moving databases is getting all the logins moved properly. But all the meta data is important. Stored procedures, scheduled jobs, and batch processes can all fail if the proper meta data is missing. When to Use the Transfer Databases and Other Transfer Tasks The five transfer tasks are designed for two purposes: • The specific purpose of upgrading a SQL Server 7.0 database to SQL Server 2000. • The more general purpose of moving a database and associated meta data between data- base servers. You can only use databases on SQL Server 7.0 or SQL Server 2000 as the source for these transfer tasks. The destination must be SQL Server 2000. One or more databases can be included in a database transfer. For each included database, you can choose to copy it or move it. You cannot resolve most naming conflicts in the process of using these tasks. You need to resolve any conflicts before setting up these tasks, with the exception of conflicts in the nam- ing of database storage files.
  9. The Transfer Databases and Other Transfer Tasks 335 CHAPTER 15 You can include or exclude individual items of meta data, such as particular stored procedures, logins, messages, and jobs. You cannot exclude any of the objects included in the databases being transferred. If you just want to transfer some objects in a database, you should consider using the Copy SQL Server Objects task. If you want to modify the data as it is being moved, you should consider using one of the transformation tasks. These five tasks are the ones to use when you want to move or copy one or more whole data- bases. Creating the Tasks and Setting Their Properties The primary method of creating these tasks is to use the Copy Database Wizard, which is described in Chapter 25. You can also create the tasks in the Package Designer, although this is not as convenient as using the wizard. It is possible to create these tasks in code, but the object model for these tasks is not as well documented as the object model for the other DTS tasks. Most of the properties for these objects are not displayed with Disconnected Edit. The last section of this chapter shows how to create the basic tasks in code. The Source and the Destination for the Tasks All five of the transfer tasks have similar properties for the source and destination. Figure 15.1 shows the Source tab for the Transfer Databases task. FIGURE 15.1 All five transfer tasks have the same tabs for entering source and destination server information. 15 OTHER TRANSFER DATABASES AND THE TRANSFER The transfer tasks do not use DTS connections. Instead, the connection information must be entered for both source and destination in the task’s properties dialog. TASKS
  10. Other Data Movement and Manipulation Tasks 336 PART III NOTE This is one of the reasons why using the wizard is appealing. You enter the source and destination information once, and it is used for all of the tasks. The Transfer Database Task The Databases tab of the Transfer Databases dialog, shown in Figure 15.2, is the place where you choose which databases to copy and which databases to move. FIGURE 15.2 The Transfer Databases dialog shows you which databases you are allowed to copy or move. NOTE If you move a database, that database won’t be available on the source server after the step is executed. The database files on the source server are not removed, however. Whether you copy or move a database with the Copy Databases task, after the task is executed there will be a copy of all the database files on both the source and the destination. You could use the FileSystemObject in an ActiveX task to delete the source database files after the databases have been moved. You are not allowed to transfer a database if • The destination has a database with the same name. • The database is involved in replication.
  11. The Transfer Databases and Other Transfer Tasks 337 CHAPTER 15 • The source is a SQL Server in Windows 2000 and the destination is a SQL Server in Windows 98. • The database is unavailable because it is marked inaccessible, loading, offline, recover- ing, suspect, or in Emergency Mode. Figure 15.3 shows the File Locations tab. You can change the destination file or directory if there are problems with filename conflicts. You can also move the files to a different directory if there is inadequate space to copy the database to the default data location. FIGURE 15.3 You can modify the destination files for the databases while you are creating the task. The Transfer Logins Task You have two basic options on the Logins tab of the Transfer Logins dialog: • Include all server logins detected at package runtime. • Include logins for selected databases. The other meta data transfer tasks have similar options. If you choose to include logins for selected databases only, the list of choices is enabled, as shown in Figure 15.4. NOTE 15 When you’re transferring databases, it’s sometimes reasonable to include only the OTHER TRANSFER DATABASES AND THE TRANSFER logins that are being used in those databases. After all, if certain logins aren’t using those databases on the old server, why should those logins need to be transferred? TASKS
  12. Other Data Movement and Manipulation Tasks 338 PART III On the other hand, when a server has a group of user logins, it’s only a matter of time until more of those users are going to be given access to those particular data- bases. If you transfer all the logins, you will have an easier time giving database access permissions in the future. FIGURE 15.4 You can choose whether to transfer all of the logins or only some of them. The Transfer Jobs Task Figure 15.5 shows the Jobs tab of the Transfer Msdb Jobs dialog after the choice has been made to include selected jobs. FIGURE 15.5 You can choose which jobs to include or exclude in the transfer. Choosing to include only certain jobs probably makes sense, unless you are moving all the databases on a server. If a job involves a database that is not being moved, that job will fail if it is executed on a different server.
  13. The Transfer Databases and Other Transfer Tasks 339 CHAPTER 15 If you want to view details of the jobs you are considering transferring, they are listed in the Enterprise Manager tree under the Management\SQL Server Agent node. The Transfer Master Stored Procedures Task Figure 15.6 shows the Stored Procedures tab of the Transfer Master Stored Procedures dialog after the choice has been made to select individual stored procedures. FIGURE 15.6 You choose which of the Master database’s stored procedures to transfer. If you create a stored procedure in the Master database and it is named with an sp_ prefix, that stored procedure can be executed from all the databases on a server as if it were local. A stored procedure created like this is called a system stored procedure. There are many system stored procedures that are included with SQL Server. These procedures shouldn’t cause any problems when a database is being transferred because they exist on all SQL Servers. But if users have defined their own customized system stored procedures, there can be problems. System stored procedures can be referenced from within a database’s own stored procedures. An error will be generated if they don’t exist on the local server. TIP In my opinion, it’s usually best to transfer all the stored procedures. They won’t hurt anything, even if they’re never used. The only reason for following another strategy is if there are naming conflicts 15 OTHER TRANSFER DATABASES AND between stored procedures on separate servers. If you have different stored proce- THE TRANSFER dures that have the same names, you will have to carefully examine how you can sep- TASKS arate the two procedures and make them unique.
  14. Other Data Movement and Manipulation Tasks 340 PART III The Transfer Error Messages Task Figure 15.7 shows the Error Messages tab of the Transfer Error Messages dialog after the choice has been made to select specific error messages. FIGURE 15.7 The error messages and numbers are displayed as you select the ones to transfer. SQL Server stores all its error messages in a table called sysmessages in the master database. All the error numbers under 50000 are reserved for Microsoft’s use. Error numbers starting with 50000 are user-defined error messages. You can create user-defined error messages to send customized, application-specific error mes- sages back to client applications. Many developers create a separate set of error messages for each of their database applications. When used, these messages become an essential part of the database application. TIP Moving user-defined error messages from one server to another can be a problem if the same error number is used for two separate messages. Careful modifications will have to be made to the code to make sure the wrong error message isn’t called. I have seen database developers who have used the numbers in the 50000 to 51000 range for their user-defined error messages. Using those numbers brings a high risk of error number conflicts. I have seen other developers who use a 4-digit prefix for their database applications, which is assigned as a unique value for their organization, and another 4-digit value for each particular error. So altogether, the error numbers all have 8 digits. This sys- tem reduces the chance of conflict in error numbers.
  15. The Transfer Databases and Other Transfer Tasks 341 CHAPTER 15 Creating the Tasks in Visual Basic I have created a Visual Basic procedure, fctCreateTransferDatabaseTask, which creates a step, a task, and a custom task for a Transfer Databases task. Only the properties exposed in Disconnected Edit are set by this procedure. NOTE Most of the properties and methods of the transfer tasks are undocumented. You can examine these other properties when working with the tasks in Visual Basic, but I have not included them in my examples here. I have also created functions that create the other four tasks discussed in this chapter. You can find the code for these procedures in the directory for Chapter 15 on the book’s CD as a Visual Basic Project, with files CreateTransferDatabaseTask.frm, CreateTransferDatabaseTask.bas, and CreateTransferDatabaseTask.vbp. The code for fctCreateTransferDatabaseTask is shown in Listing 15.1. The procedure needs some utility functions that are included with the code listings on the CD. The project requires references to the Microsoft DTSPackage Object Library and the OMWCustomTasks 1.0 Type Library. LISTING 15.1 The Visual Basic Code to Create a Transfer Databases Task Public Function fctCreateTransferDatabaseTask( _ pkg As DTS.Package2, _ Optional sBaseName As String = “TransferDatabaseTask” _ ) As String On Error GoTo ProcErr Dim stp As DTS.Step2 Dim tsk As DTS.Task Dim cus As OMWTransferDatabases ‘Check to see if the selected Base name is unique sBaseName = fctFindUniqueBaseName(pkg, sBaseName) 15 OTHER TRANSFER DATABASES AND THE TRANSFER ‘Create task and custom task TASKS Set tsk = pkg.Tasks.New(“OMWCustomTasks.OMWTransferDatabases”) Set cus = tsk.CustomTask
  16. Other Data Movement and Manipulation Tasks 342 PART III LISTING 15.1 Continued With cus .Name = “tsk” & sBaseName .Description = sBaseName End With pkg.Tasks.Add tsk ‘Create step for task Set stp = pkg.Steps.New With stp .Name = “stp” & sBaseName .Description = sBaseName .TaskName = tsk.Name End With pkg.Steps.Add stp fctCreateTransferDatabaseTask = stp.Name Set tsk = Nothing Set cus = Nothing Set stp = Nothing ProcExit: Exit Function ProcErr: MsgBox Err.Number & “ - “ & Err.Description fctCreateTransferDatabaseTask = “” GoTo ProcExit End Function Conclusion The Transfer Databases task and the other transfer tasks are useful when you’re moving data from one server to another. These tasks cannot be manipulated in code as easily as most of the other tasks because their properties and methods are not documented or displayed in Disconnected Edit. The easiest way to use these tasks is through the Copy Database Wizard.
  17. PART Control Tasks IV IN THIS PART 16 Writing Scripts for an ActiveX Script Task 345 17 The Dynamic Properties Task 365 18 The Execute Package Task 377 19 The Message Queue Task 391 20 The Send Mail Task 405 21 The Analysis Services Tasks 411 22 The Execute Process Task 425
  18. Writing Scripts for an ActiveX CHAPTER 16 Script Task IN THIS CHAPTER • When to Use an ActiveX Script Task 346 • Creating an ActiveX Script Task 346 • Dynamically Modifying DTS Properties 348 • Programming a Loop in a DTS Package 352 • Using ADO Recordsets 355 • Manipulating Files and Directories 357 • Writing Task Log Records 358 • Converting VB Code to VBScript 359 • Creating an ActiveX Script Task in Visual Basic 361
Đồng bộ tài khoản