Microsoft SQL Server 2000 Data Transformation Services- P10

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

0
42
lượt xem
4
download

Microsoft SQL Server 2000 Data Transformation Services- P10

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- P10: 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- P10

  1. Control Tasks 426 PART IV The Execute Process task is one of the least complex of all the DTS tasks. Its only purpose is to run an executable program or a batch file. When to Use the Execute Process Task The importance of the Execute Process task is in the way it integrates DTS packages with other applications and batch processes. Many companies have existing programs that transfer data. You can use DTS as a control panel to run all of your data transformation applications. You can use the various DTS tasks when you want to manipulate data in a new way. You can use the Execute Process task to coordinate your existing data manipulation applications with the rest of what you are doing with DTS. Consider the following specific ways of using the Execute Process task. Bulk Copying from SQL Server to a Text File If you are creating a new bulk copy operation to load SQL Server, I suggest that you use the Bulk Insert task. But you can’t use it if you want to bulk copy data out of SQL Server. However, you can integrate that bulk copy into DTS by using the Execute SQL task. Use the bcp command-line utility to do the bulk copying. Here’s a sample of how to do that. Use the following values in the Execute Process Task dialog: • Win32 Process—bcp • Parameters—out “SELECT au_lname, au_fname as FullName FROM pubs..authors ORDER BY au_lname” queryout C:\Authors.txt -c -S(local)-T Figure 22.1 shows the Execute Process Task Properties dialog with the properties set to execute this bulk copy. FIGURE 22.1 You can use the Execute Process task to bulk copy data out of SQL Server into a text file.
  2. The Execute Process Task 427 CHAPTER 22 Executing a Batch File Containing osql and/or bcp Commands If you have existing bcp commands in batch files, whether moving data into or out of SQL Server, you can keep on using those batch files with your DTS package by calling them from the Execute Process task. You can also use osql, the SQL Server command-line utility for executing SQL commands, in these batch files. For example, you could write a batch file that creates a view, uses that view to bulk copy data out of SQL Server, and then drops the view. The file would look like this: 22 osql /S(local) /E /dpubs /Q”create view dbo.vwAuthorName(fullname) ➥as select au_fname + ‘ ‘ + au_lname from pubs.dbo.authors” PROCESS TASK THE EXECUTE bcp “pubs.dbo.vwAuthorName” out C:\Temp\AuthorName.txt /c /T /S(local) osql /S(local) /E /dpubs /Q”drop view dbo.vwAuthorName” If you save this batch file as c:\temp\testdts.bat, you would then enter that filename in the Win32 Process of the Execute Process task. You would not use any parameters for the task. Running Other Data Movement or Manipulation Applications You may have external programs that you need to run before or after some of your DTS tasks, such as • Specialized FTP processes that cannot easily be adapted to the DTS FTP task. • Programs that unzip text files. • Applications that convert binary files to text. • Batch files that call OLTP systems to export data. • Programs that process large text files, such as SyncSort. • Customized parsing programs. Executing DTSRun You can execute DTS packages from the Execute Process task by using the DTSRun com- mand-line utility: DTSRun /E /N PackageName /F c:\temp\LoadEmployee.dts NOTE You have greater control in executing one package from another when you use the Execute Package task.
  3. Control Tasks 428 PART IV Creating the Task and Setting Its Properties You can create the Execute Process task in the DTS Designer or in code. Neither of the DTS wizards creates an Execute Process task. The Execute Process Task Properties dialog has only one tab. The dialog lets you set five of the task’s seven properties. You have to use code or Disconnected Edit to view or modify the Name and FailPackageOnTimeout properties. The Execute Process Task Properties Here are the task’s properties: • Name—The name of the task. Cannot be viewed or modified in the Execute Process Task Properties dialog. • Description—The description of the task. • ProcessCommandLine—The command line that is executed. You enter the command line in two boxes in the dialog—the Win32 Process and the parameters. The DTS Designer concatenates the two values to create the value for this property. • SuccessReturnCode—The code that is expected for a successful execution of the appli- cation. If the application returns a different code, the Execute Process task is marked as failed. The default return code is 0. • Timeout—The number of seconds that the Execute Process task waits for a return code from the application. If no return code is received within this time period, the task is marked as failed. The default timeout value is 0, which means that the task will wait indefinitely. • TerminateProcessAfterTimeout—If you specify a value for a time out, you can also choose to terminate the application that was executed when that timeout occurs. Whether or not you terminate the application on timeout, the DTS package will continue its exe- cution. The default value for this property is FALSE. • FailPackageOnTimeout—This property causes the whole DTS package to be terminated if a timeout occurs in the Execute Process task. This value cannot be set or viewed in the dialog. The default value is FALSE. The GetExpandedProcessCommandLine Method of the CreateProcess2 Object In code, the Execute Process task is implemented by the CreateProcess2 object. This object inherits all the properties of the CreateProcess object.
  4. The Execute Process Task 429 CHAPTER 22 The CreateProcess2 object adds no new properties, but it does add one method. This method, GetExpandedProcessCommandLine, can be used to return a command line with all parameter variables expanded. For example, you could have a ProcessCommandLine property of bcp pubs.dbo.authors out %TEMP%\authors.txt /c /T /S(local) The GetExpandedProcessCommandLine method would return a value like this: bcp pubs.dbo.authors out C:\Temp\authors.txt /c /T /S(local) 22 Creating the Task in Visual Basic PROCESS TASK THE EXECUTE I have created a Visual Basic procedure, fctCreateExecuteProcessTask, that creates a con- nection, a step, a task, and a custom task for an Execute Process task. All the properties of the task can be set with this procedure. The fctCreateExecuteProcessTask function is used in the DTSPackageGenerator utility that is included with this book. You can also find the procedure in the directory for Chapter 22 as a Visual Basic Project, with files CreateExecuteProcessTask.vbp, CreateExecuteProcessTask.frm, and CreateExecuteProcessTask.bas. The code for fctCreateExecuteProcessTask is shown in Listing 22.1. The procedure needs some utility functions that are included with the code listings on the CD. LISTING 22.1 The Visual Basic Code to Create an Execute Process Task Option Explicit Public Function fctCreateExecuteProcessTask( _ pkg As DTS.Package2, _ Optional sBaseName As String = “ExecuteProcessTest”, _ Optional sProcessCommandLine As String = “”, _ Optional lSuccessReturnCode As Long = 0, _ Optional lTimeout As Long = 0, _ Optional bTerminateProcessAfterTimeout As Boolean = False, _ Optional bFailPackageOnTimeout As Boolean = False) On Error GoTo ProcErr Dim stp As DTS.Step2 Dim tsk As DTS.Task Dim cus As DTS.CreateProcessTask2
  5. Control Tasks 430 PART IV LISTING 22.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(“DTSCreateProcessTask”) Set cus = tsk.CustomTask With cus .Name = “tsk” & sBaseName .Description = sBaseName .ProcessCommandLine = sProcessCommandLine .SuccessReturnCode = lSuccessReturnCode .Timeout = lTimeout .TerminateProcessAfterTimeout = bTerminateProcessAfterTimeout .FailPackageOnTimeout = bFailPackageOnTimeout 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 fctCreateExecuteProcessTask = stp.Name Set tsk = Nothing Set cus = Nothing Set stp = Nothing ProcExit: Exit Function ProcErr: MsgBox Err.Number & “ - “ & Err.Description fctCreateExecuteProcessTask = “” GoTo ProcExit End Function
  6. The Execute Process Task 431 CHAPTER 22 Conclusion The Execute Process task is simple, but it is a key player in the integration of DTS with your other data manipulation applications. This is the last chapter discussing the DTS tasks. Part V continues with a discussion of the DTS package as a whole and the steps that control the flow of the tasks. 22 PROCESS TASK THE EXECUTE
  7. PART DTS Packages and Steps V IN THIS PART 23 The DTS Package and Its Properties 435 24 Steps and Precedence Constraints 469 25 Rapid Development with the Copy Database Wizard and the DTS Import/Export Wizard 501 26 Managing Packages with Visual Basic and Stored Procedures 525 27 Handling Errors in a Package and Its Transformations 553 28 High Performance DTS Packages 565 29 Integrating DTS with Meta Data Services 587
  8. The DTS Package and Its CHAPTER 23 Properties IN THIS CHAPTER • Identifying DTS Packages 436 • Storing DTS Packages 438 • Encrypting DTS Packages 444 • Retrieving Information About Packages 445 • Package Logs and Error Files 451 • DTS Packages as Data Sources 460 • Other DTS Package Object Properties and Methods 465
  9. DTS Packages and Steps 436 PART V The package is at the highest level in the DTS object hierarchy. You can’t create tasks, steps, connections, or global variables outside of a package. It’s the DTS level that is used for saving, loading, and executing a particular set of DTS steps that have their associated tasks. As with many of the DTS tasks, the DTS Package object has a new implementation in SQL Server 2000 as the Package2 object. This object inherits the properties and methods of the Package object and adds several new ones. This chapter describes several facets of the DTS package as a whole. See Chapter 24, “Steps and Precedence Constraints,” for a discussion of transactions and thread execution in a DTS package and its steps. Identifying DTS Packages A DTS package, like most other DTS objects, has a Name property and a Description prop- erty. The name is set when the package is first created and cannot be changed. You can change the description as often as you like. Every time a DTS package is saved, a new version of the package is created. You can view the version history of a DTS package stored in the repository or in SQL Server by right-clicking on the name of the package in the Enterprise Manager and selecting Versions. The DTS Package Versions dialog is shown in Figure 23.1. FIGURE 23.1 The DTS Package Versions dialog shows all the versions that have been created for a particular DTS package. This dialog gives you the option of opening any version of the package for editing. For pack- ages saved in SQL Server, you also have the option of deleting any of the versions.
  10. The DTS Package and Its Properties 437 CHAPTER 23 NOTE When you choose a particular version of a DTS package for editing and then save your changes, you don’t overwrite the previous version. Instead, a new version is saved. The only way you can get rid of previous versions is by explicitly deleting them. You can delete particular versions of a package saved in the repository or in SQL Server by using the RemoveFromRepository method or the RemoveFromSQLServer method. These meth- ods remove one version of a package. If the version to be removed is not specified, the most recent one is removed. Packages and their versions are identified by 16-byte globally unique identifiers (GUIDs). The Package GUID and Version GUID are displayed on the General tab of the DTS Package Properties dialog (see Figure 23.2). 23 PACKAGE AND ITS PROPERTIES THE DTS FIGURE 23.2 The DTS Package Properties dialog displays all the identification information for a package. When a package is first created, the two GUID values will be the same. When later versions are created, the Package GUID remains the same and the Version GUID is always changed. Either the Package GUID or the Version GUID can be used to identify a package for retrieval or deletion. When the Package GUID is used by itself, only the most recent version of the package is referenced. If the Version GUID is used, any of the package’s versions can be refer- enced.
  11. DTS Packages and Steps 438 PART V These values are implemented as the PackageID and VersionID properties of the Package object. These are read-only properties that use the 128-bit uniqueidentifier data type. Storing DTS Packages DTS packages can be saved in four different locations, each of which has its advantages: • SQL Server storage provides the fastest saving and retrieval speed. • Meta Data Services storage provides the ability to save and track meta data. • File system storage allows DTS packages to be easily shared between users. • Visual Basic storage provides programmatic access to DTS objects and properties. The Save DTS Package dialog has a list box with the four DTS package storage options. The other choices you make in this dialog change as you choose a different storage location. Figure 23.3 shows the dialog as it appears when you are saving to Meta Data Services. FIGURE 23.3 The Save DTS Package dialog presents choices to the user that differ depending on which storage method is selected. You can save, retrieve, and delete packages in the different storage locations by using methods of the Package object. There is also one method, SaveAs, that is not implemented in the inter- face but can be used programmatically. This method saves a package with a new name but does not store this new package in any form of persistent storage. The SaveAs method has one parameter—NewName. Saving DTS Packages to SQL Server The definition of a package saved to SQL Server is stored in the sysdtspackages table in the msdb system database. The image data type is used to save the package.
  12. The DTS Package and Its Properties 439 CHAPTER 23 Here are the details on saving and retrieving packages when saving to SQL Server: • Packages saved to one instance of SQL Server must have unique names. • You can assign a User password, an Owner password, or both to the package. • Users must have permission to access the msdb database to save or retrieve the DTS package. DTS packages are saved to the SQL Server with the SaveToSQLServer method of the Package object. SaveToSQLServer has the following parameters: • ServerName—The server where the package should be stored. • ServerUserName—The logon name for the server specified in ServerName. • ServerPassword—The password for the ServerUserName logon. • Flags—Security choice. Described below. • PackageOwnerPassword—Password needed to view or edit package structure. • PackageOperatorPassword—Password needed to execute package. • PackageCategoryID—Not currently being used. • pVarPersistStgOfHost—Pointer to the screen layout information for the package. 23 PACKAGE AND ITS • bReusePasswords—Whether or not package passwords are allowed to be reused. PROPERTIES THE DTS NOTE The save and load methods of the package object both have a parameter called pVarPersistStgOfHost, which is a pointer to the screen layout of the package as it appears in the Package Designer. This pointer cannot be used when saving packages from VBScript, Visual Basic, or Visual C++. It is only available for internal use by the Package Designer. The visual representation of a package is always lost when you save a package pro- grammatically. That’s unfortunate if you’ve gone to a lot of work making all the tasks and precedence constraints appear in a logical arrangement. The next time you load the package, the Package Designer will apply the default arrangement, which often is not very visually appealing—especially if there are a lot of tasks. There are hints that Microsoft might give developers the ability to save and re-create the visual representation in the future. For now, don’t spend too much time making your packages look nice if you’re planning on saving them programmatically. Table 23.1 contains two storage flags, which present the choice between using SQL Server authentication (the default) or a trusted connection.
  13. DTS Packages and Steps 440 PART V TABLE 23.1 Constants Used for the Flags Parameter of the SaveToSQLServer Method Constant Value Meaning DTSSQLStgFlag_Default 0 Use SQL Server Security DTSSQLStgFlag_UseTrustedConnection 256 Use Trusted Connection The Package2 object has a new method called SaveToSQLServerAs that is used to save a pack- age with a new name and a new Package ID. This method has the same parameters as SaveToSQLServer, except for an additional first parameter called NewName. The LoadFromSQLServer method is used to retrieve a package that is stored in SQL Server. The RemoveFromSQLServer method is used to delete a package from SQL Server storage. Their parameters are similar to those used by the saving methods: • ServerName • ServerUserName • ServerPassword • Flags—Optional parameter. Uses the constants in Table 23.1 • PackagePassword—Not used for RemoveFromSQLServer. • PackageGUID—Optional. Not needed if a PackageVersionGUID or a PackageName is pro- vided. • PackageVersionGUID—Optional. If the PackageVersionGUID is not provided, the most recent version of the package is loaded or removed. • PackageName—Optional. Not needed if either of the GUID parameters is provided. • pVarPersistStgOfHost—Pointer to the screen layout information for the package. Not used for RemoveFromSQLServer. Listing 23.1 has a sample of Visual Basic code that creates a package, saves it in SQL Server storage, loads it from SQL Server, changes its description, and saves it to SQL Server with a different name. The original package is then deleted. You can find this code on the CD in a file called SaveAndRetrieveMethods.bas. To use it in a VB project, you have to include a reference to the Microsoft DTSPackage Object Library. LISTING 23.1 Code That Illustrates How to Work with Packages Stored in SQL Server Sub subDTSSaveAndRetrieveMethods() Dim pkg As New DTS.Package2 Dim FirstPackageID As String
  14. The DTS Package and Its Properties 441 CHAPTER 23 LISTING 23.1 Continued pkg.Name = “Test Save And Retrieve Methods” ‘Save to the local server ‘Use integrated security pkg.SaveToSQLServer “(local)”, , , DTSSQLStgFlag_UseTrustedConnection pkg.LoadFromSQLServer “(local)”, , , _ DTSSQLStgFlag_UseTrustedConnection, , , _ , “Test Save And Retrieve Methods” FirstPackageID = pkg.PackageID pkg.Description = “Description to be saved in second package.” pkg.SaveToSQLServerAs “Renamed Package”, “(local)”, , , _ DTSSQLStgFlag_UseTrustedConnection pkg.RemoveFromSQLServer “(local)”, , , _ DTSSQLStgFlag_UseTrustedConnection, _ FirstPackageID Set pkg = Nothing 23 PACKAGE AND ITS End Sub PROPERTIES THE DTS Saving DTS Packages in Meta Data Services Microsoft Meta Data Services provides a standard method for different products to share infor- mation. The package’s characteristics are available through the interfaces provided by the repository’s information models. See Chapter 29, “Integrating DTS with Meta Data Services.” Here are the details on saving and retrieving packages when using the repository: • As with packages saved to SQL Server, packages saved to a single instance of Meta Data Services must have unique names. • When saving to Meta Data Services, there is a button on the Save DTS Package dialog to bring up the Scanning Options dialog. These options are also discussed in Chapter 29. • DTS package encryption is not available for packages saved to Meta Data Services. • By default, Meta Data Services is located in the SQL Server msdb database. If you cre- ate a package using Visual Basic, you can specify a different Meta Data Services data- base. • Users must have permission to access the database that is hosting the instance of Meta Data Services that is being used.
  15. DTS Packages and Steps 442 PART V The methods for working with Meta Data Services packages are similar to those for SQL Server packages: • SaveToRepository • SaveToRepositoryAs • LoadFromRepository • RemoveFromRepository Here are the parameters of the SaveToRepository method: • RepositoryServerName—The server where this instance of Meta Data Services is stored. • RepositoryDatabaseName—The database where this instance of Meta Data Services is located. • RepositoryUserName—The logon name for the server specified in RepositoryServerName. • RepositoryUserPassword—The password for the RepositoryUserName logon. • Flags—Security choice. Same as SaveToSQLServer, except the constants used are DTSReposFlag_Default and DTSReposFlag_UseTrustedConnection. • CategoryID—Not currently being used. • pVarPersistStgOfHost—Pointer to the screen layout information for the package. Storing DTS Packages in the File System DTS can save one or more packages in a single COM-structured storage file. Each saved pack- age can have one or more versions, all stored in the same file. Packages stored in files are not displayed in the Enterprise Manager, as are the packages stored in SQL Server or in the default Meta Data Services location. To retrieve a package from a file, right-click on Data Transformation Services in the Console Tree and choose Open Package. A Select File dialog appears. After you have selected a *.dts file, the packages and versions in that particular file are displayed in the Select Package dialog, as shown in Figure 23.4. FIGURE 23.4 The Select Package dialog showing the packages and their versions.
  16. The DTS Package and Its Properties 443 CHAPTER 23 Here are the details on saving and retrieving packages when saving to a file: • The naming rules are different for DTS packages stored in files. You can have many packages with the same name in one file. This is in addition to having different versions of one package, of course. The different packages are distinguished by their Package GUID. • If you want the package to be encrypted, you can provide an Owner password, a User Password, or both. • To use a package stored in a file, a user must have the appropriate file system permissions. There is no method to delete a package or a version of a package from a file. The other meth- ods are similar to the SQL Server and Meta Data Services options: • SaveToStorageFile • SaveToStorageFileAs • LoadFromStorageFile SaveToStorageFile has fewer parameters than the comparable methods for the other storage 23 types: PACKAGE AND ITS PROPERTIES • UNCFile—File name to be used in saving the package. Microsoft recommends using a THE DTS Uniform Naming Convention (UNC) filename, but that is not required. • OwnerPassword— Password needed to view or edit package structure. • OperatorPassword—Password needed to execute package. • pVarPersistStgOfHost—Pointer to the screen layout information for the package. • bReusePasswords—Determines whether or not package passwords can be reused. Saving DTS Packages as Visual Basic Files The fourth way to save a DTS package is to save it as a Visual Basic code module. This type of saving is very different from the other three: • There is no versioning capability. • There are no security options unless you apply file-level security. • You don’t have any methods to restore a package from this type of storage. If you store a package as a Visual Basic code file, you have to load that file into Visual Basic to re-create it. • You don’t even have a method to save the package. The Package Designer implements the save to VB internally without using the DTS object model.
  17. DTS Packages and Steps 444 PART V CAUTION If you are developing a package with the Package Designer and choose Save To VB, the package will be saved to the code file. If that file already exists, you will receive a message asking if you want to overwrite the previous file. The next time you select the Save button, nothing happens. For the other three sav- ing choices, a new version is created. But when you save to VB, the Save button does not work. It appears that the Save operation is completed successfully. No error is returned. But the current version of the package is not saved at all. You have to select Save As, and if you then select Save to VB or any of the other choices, your package will again be saved. Storage in a Visual Basic file has several advantages: • It allows you to use a text editor to modify names throughout a package. • It gives you a starting point for creating, modifying, and executing DTS packages from Visual Basic. • It can help you understand the programmatic structure of your package. The structure of the Visual Basic file that Save to VB generates is discussed in Chapter 26, “Managing Packages with Visual Basic and Stored Procedures.” NOTE Save to VB is a new feature in SQL Server 2000. It is based on a utility called ScriptPkg.exe that was included on the installation CD with SQL Server 7.0. Encrypting DTS Packages DTS package security differs depending on the package storage location. A DTS package saved to SQL Server or to a file can be given an Owner password, a User password, or both passwords. If one or both of the passwords is assigned, the package is encrypted. The encryp- tion includes all the objects, collections, and properties in the packages except for Name, Description, PackageID, VersionID, and CreationDate, which are used to identify packages for retrieval.
  18. The DTS Package and Its Properties 445 CHAPTER 23 When a person attempts to retrieve an encrypted package, he must supply the password. The two passwords give the following permissions: • The Owner password gives the right to execute the package, as well as the right to view and edit the package’s objects and properties. • The User password gives the right to execute the package, but not to view or edit its objects and properties. Retrieving Information About Packages You can retrieve information programmatically about the packages that are stored on a particu- lar SQL Server, in a particular instance of Meta Data Services, or in a particular storage file without opening the packages at all. This is useful, especially when you want to obtain a list of names, versions, and IDs of all the available DTS packages. The DTS Application object is used to provide a variety of information about the system and DTS packages: • Chapter 30, “Programming with the DTS Object Model,” discusses the system informa- tion available through this object. 23 PACKAGE AND ITS • The next section in this chapter, “Package Logs and Error Files,” describes how to PROPERTIES THE DTS retrieve package logging information. • Chapter 29, “Integrating DTS with Meta Data Services,” describes how to retrieve lin- eage information. • This section discusses how you can use the Application object to retrieve information about SQL Server and Meta Data Services packages. The SavedPackageInfos collection of the Package2 object is used to retrieve information about the packages stored in a particular storage file. Since saving a package to VB creates a separate file with no versioning, there are no comparable strategies for packages saved in that way. Package Stored in SQL Server By using the Application object, you can gain a reference to the PackageInfo object for each version of every package stored on a particular SQL Server. The PackageInfo object contains this subset of the Package2 object’s properties: • Name and Description • PackageID and VersionID • Owner
Đồng bộ tài khoản