Microsoft SQL Server 2000 Data Transformation Services- P9

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

0
35
lượt xem
5
download

Microsoft SQL Server 2000 Data Transformation Services- P9

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

  1. Control Tasks 376 PART IV LISTING 17.1 Continued Set cus = tsk.CustomTask 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 fctCreateDynamicPropertiesTask = stp.Name Set tsk = Nothing Set cus = Nothing Set stp = Nothing ProcExit: Exit Function ProcErr: MsgBox Err.Number & “ - “ & Err.Description fctCreateDynamicPropertiesTask = “” GoTo ProcExit End Function Conclusion The Dynamic Properties task improves the level of control within a DTS package. The next two chapters describe the two tasks that give you control between packages—the Execute Package task and the Message Queue task.
  2. The Execute Package Task CHAPTER 18 IN THIS CHAPTER • When to Use the Execute Package Task 378 • Creating the Task and Setting Its Properties 379 • Setting Values of Global Variables in the Child Package 380 • The NestedExecutionLevel Property of the Package 383 • Creating and Calling a Utility DTS Package 384 • Creating the Task in Visual Basic 387
  3. Control Tasks 378 PART IV The Execute Package task lets you execute one DTS package from another. When to Use the Execute Package Task Here are some reasons you might want to divide your DTS functionality between several packages: • The packages have become too complex. They will be easier to understand if the tasks are divided between several packages. • You have a task or group of tasks that performs a utility function. This functionality can be put into a separate package so that the same code can be used by several packages. • Some of the DTS functionality needs to have a higher level of security. You can put that functionality into a separate package where it can be executed but not viewed or edited. There are several ways to execute one package from another, in addition to using the Execute Package task. This task is the easiest way to execute another package, but some of the other ways have additional functionality. Here are the other possibilities: • Use DTSRun from an Execute Process task. If you use an encrypted command line, you can hide all the parameters used in the execution, including the name of the server, pack- age, and user. • Use the DTS object model from an ActiveX Script task. You can change the properties of the package and its tasks before executing the package. You can read the values of global variables after executing the package. • Use OpenRowset to query a package from an Execute SQL task. You can return a record- set from the child package when you use this strategy. • Use the OLE Automation stored procedures from an Execute SQL task. This strategy allows you to execute a package from the context of a remote server. You can also mod- ify the properties before executing and read the values of global variables after executing, as when you use the object model from an ActiveX Script task. You can set the values of global variables in the child package when you use the Execute Package task and in all but one of the other strategies. Using OpenRowset from an Execute SQL task is the only method that does not allow you to set the values of global variables in the package you are executing. You can only receive values back from a child package when you use the DTS object model in an ActiveX Script task or with the OLE Automation stored procedures in an Execute SQL task. You cannot receive values of global variables back from a child package when you use the Execute Package task.
  4. The Execute Package Task 379 CHAPTER 18 One of the most important advantages of using the Execute Package task rather than one of the other methods is that you can include parent and child packages together in transactions. To enable transactions that span packages, you have to do the following: • Ensure that transactions are enabled for the package as a whole. The package transaction properties are set on the Advanced tab of the DTS Package Properties dialog. • In the Workflow Properties dialog for the step associated with the Execute Package task, select the Join Transaction If Present option. • The Microsoft Distributed Transaction Coordinator client must be running on all the computers that are executing one of the packages. There is more information about using transactions in DTS packages in Chapter 24, “Steps and Precedence Constraints.” Creating the Task and Setting Its Properties You can create the Execute Package task in the Package Designer or in code. The last section of this chapter shows how to create the task in code. The Package Designer’s Execute Package Task Properties dialog is shown in Figure 18.1. 18 PACKAGE TASK THE EXECUTE FIGURE 18.1 You choose package and connection information on the General tab of the Execute Package Task Properties dialog.
  5. Control Tasks 380 PART IV Most of the properties of the Execute Package task are concerned with package and connection information. You set these properties on the General tab of the Execute Package Task Properties dialog: • Description—The description of the task. • FileName—The file from which the DTS package is to be loaded. When the DTS pack- age is not stored in a file, this property should be an empty string. • UseRepository—If FileName is an empty string and this property is TRUE, the package is loaded from the repository. If FileName is an empty string and this property is FALSE, the package is loaded from SQL Server storage. • RepositoryDatabaseName— The database that contains the instance of Meta Data Services from which the package is to be retrieved. This property is only used for a pack- age loaded from the repository. If it is an empty string, the database that contains the default instance of Meta Data Services is used. • PackageName, PackageID, VersionID— You choose the package and version from those available in the storage location you have specified. When you choose a package, the Execute Package task will always use the most current version of that package. If you choose a particular version of a package, the task will always use the same version, whether or not new versions are created. The value of these three properties is displayed in the interface. To change the Package or Version ID, you have to pick a new item in the Select Package dialog. • PackagePassword— Either the owner password or the user password can be used to exe- cute the package. • ServerName— The server on which the DTS package is stored. • ServerUserName, ServerPassword, and UseTrustedConnection—Connection informa- tion for the server on which the DTS package is stored. Setting Values of Global Variables in the Child Package You can pass information from the parent package to the child package by setting the values of global variables. There are two ways you can do this: • With fixed values, where you set the values of the global variables at design time in the Execute Package task. • With dynamic values, where the child package is sent the current values assigned to the global variables as the parent package is being executed.
  6. The Execute Package Task 381 CHAPTER 18 You send global variables with the fixed values method by adding global variables on the Inner Package Global Variables tab of the Execute Package Task Properties dialog, as shown in Figure 18.2. You can choose the name, the datatype, and the value for the global variable. 18 FIGURE 18.2 PACKAGE TASK You set fixed values for global variables on the Inner Package Global Variables tab. THE EXECUTE The variables you set on this tab are not global variables in the parent package. They set the values of global variables in the child package. You send global variables with the dynamic values method by adding global variables on the Outer Package Global Variables tab, as shown in Figure 18.3. This tab allows you to choose from the existing global variables in the parent package. These parent package global variables set the values of global variables in the child package to the parent package values at the time the Execute Package task is executed. You can send global variables with both methods in the same Execute Package task. If you use the same name for both a fixed value and a dynamic value global variable, the global variable is sent with the fixed value. The two types of global variables are received the same way in the child package. If a global variable with the specified name exists in the child package, the value sent from the parent package is assigned to it. If that global variable doesn’t exist in the child package, a new global variable is created. This new global variable only persists during the execution of the child package.
  7. Control Tasks 382 PART IV FIGURE 18.3 You choose existing global variables on the Outer Package Global Variables tab when you want to use the dynamic values method. CAUTION Global variable names are always case sensitive. If you are attempting to pass a vari- able and you do not match the case of all the letters in the name, a new variable will be created in the child package. Also, a global variable passed to a child package overrules the option for required explicit declaration of global variables. A new global variable will be created in the child package when a global variable with the same name doesn’t exist, even if explicit declaration of variables is required in that package. The Execute Package task has a GlobalVariables collection that contains a collection of GlobalVariable objects. All of the fixed-value global variables that you pass to the child pack- age are members of this collection. The Execute Package task has an InputGlobalVariableNames property that contains a semi- colon-delimited list of global variable names. These are the names of the dynamic value global variables that are being sent to the child package: “GlobalVariableName1”;”GlobalVariableName2”;”GlobalVariablename3”
  8. The Execute Package Task 383 CHAPTER 18 NOTE It’s hard to find the right terminology to talk about these two kinds of global vari- ables. I am not satisfied with the Inner Package/Outer Package labeling that the inter- face gives to the two kinds of global variables, because both types become global variables in the inner package. I think it’s better to focus on the function of the global variables. If you have fixed values that you always want to send to the child package, you cre- ate global variables that use the fixed value method. You specify the fixed value, along with the global variable name, on the Inner Package Global Variables tab. If you have dynamic values that you want to set while your parent package is execut- ing, you create global variables with the dynamic value method. You create global variables in the parent package and send them to the child package on the Outer Package Global Variables tab. The NestedExecutionLevel Property of the 18 Package PACKAGE TASK THE EXECUTE The DTS Package2 object has a property called NestedExecutionLevel. This read-only prop- erty reports the level of nesting that has been reached through the use of the Execute Package task. This property returns a value of 0 for a package that is executed directly and a value of 1 for the first package called by the Execute Package task. If that called package calls another pack- age, the NestedExecutionLevel property will return a value of 2 for the innermost package. DTS has an absolute limit of 32 for the NestedExecutionLevel. If that level is reached and another Execute Package task is executed, the package will fail with the following error: “Nested package execution limit of 32 was exceeded.” If you are calling DTS packages recursively with the Execute Package task, you can query the NestedExecutionLevel property to break out of the recursion before this error occurs: Dim pkg, stp Set pkg = DTSGlobalVariables.Parent Set stp = pkg.Steps(“stpExecutePackage”) If pkg.NestedExecutionLevel = 32 Then stp.DisableStep = True End If
  9. Control Tasks 384 PART IV CAUTION Books Online recommends against using the Execute Package task to call packages recursively because of the danger of a stack overflow shutting down the Enterprise Manager. Creating and Calling a Utility DTS Package In Chapter 12, “The Execute SQL Task,” I described how to use OLE Automation procedures in an Execute SQL task to force a DTS package to execute on a particular server. I have encap- sulated that somewhat complex code into a utility DTS package so that it can be called with the Execute Package task. The name of the utility package is the Remote Execution Utility. You call it from an Execute Package task, and it runs the selected DTS package on any server you choose. The utility writes execution information to an INI file. The whole process is shown in Figure 18.4. Execute Execute Package Task SQL Task Calling Package Remote Execution Utility Called Package Executes Locally Executes Locally Executes on Server of Your Choice FIGURE 18.4 The Remote Execution Utility contains the functionality to execute a Called Package on any server and report the results to the Calling Package. You can find the utility and a sample of a Calling Package and a Called Package in three files on the CD—CallingPackage.dts, RemoteExecutionUtility.dts, and CalledPackage.dts. To use these sample packages, you have to do the following: 1. Save the Remote Execution Utility and the Called Package to SQL Server. You can put them on any servers you like, as long as you reference those servers appropriately in the Execute Package task in the Calling Package. 2. The Calling Package has to be set to log to SQL Server. Check the logging settings in the Package Properties dialog. 3. In the Execute Package task of the Calling Package, you must choose the Remote Execution Utility as the package to be executed. 4. Set the global variables in the Execute Package task to appropriate values.
  10. The Execute Package Task 385 CHAPTER 18 You are only required to send one value to the Remote Execution Utility—the PackageName. The utility will open the Called Package from SQL Server storage on the local computer using integrated security. The utility will execute the Called Package in the context of the local server, again using integrated security. You can override these default values by sending these global variables to the Remote Execution Utility: • PackageStorageServer—The name of the server where the Called Package is stored using SQL Server storage. • PackageStorageUserName and PackageStoragePassword. • PackageExecutionServer—The name of the server where the Called Package is to be executed. • PackageExecutionUserName and PackageExecutionPassword. • ConnectionServer—You can change all the connections in the executed package to ref- erence a particular server. If this parameter is an empty string, the connections will not be changed. • ConnectionUserName and ConnectionPassword. The Remote Execution Utility reports results in an INI file. The following global variables can 18 be sent to the utility to set up the reporting: PACKAGE TASK THE EXECUTE • CallingPackageID—Any identification value from the Calling Server. I recommend sending the short lineage value for the current execution of the Calling Server. You could also use the full lineage value, the package ID, the package version ID, or the package name. • ReportName—The name of the INI file used to report results. If not supplied, the name of the INI file will be set to RemoteExecutionReport.ini. • ReportDirectory—The directory where the INI file is to be written. The default value is set in the Remote Execution Utility to the user’s temporary directory. • StepName—The step associated with a Transform Data task for which you want a com- plete report. If this is not set, no step will be given special reporting. CAUTION The initial version of SQL Server 2000 limits the headings used for INI files in the Dynamic Properties task to 255 characters. Because of this limitation, the Calling Package will not be able to read the values for the current package execution after 23 entries have been made into the INI file.
  11. Control Tasks 386 PART IV The Remote Execution Utility has a parameter called TimeoutInSeconds. The utility will ter- minate with an error if the Called Package does not complete execution within the specified period. By default, the Calling Package sets the timeout to 60 seconds. CAUTION Do not use message boxes in the Called Package. They will not be displayed and will prevent the Remote Execution Utility from completing. The timeout will not termi- nate the utility if an attempt is being made to display a message box. To end the package, you have to stop SQL Server or terminate the Enterprise Manager process. The sample for the Calling Package, shown in Figure 18.5, has five steps: • Set Calling Package ID—An ActiveX Script task that sets the Calling Package ID. In the sample package, the Short Lineage variable of the package’s current execution is used. That value is set in a dynamic value global variable so that it will be sent to the utility. It is also set in the Dynamic Properties task so that the proper section of the INI file will be read for the report. • Remote Execution—The Execute Package task that calls the Remote Execution Utility. • Determine Reporting—An ActiveX Script task that sets the directory and name of the INI file used in reporting. If an empty string has been chosen for these values, this task disables the two reporting tasks. • Find Results—A Dynamic Properties task that reads the values of the report INI file into global variables. • Report Results—An ActiveX Script task that displays a message box reporting on those global variables. In a production environment, you could set this string as the message of a Send Mail task, which could then be sent to the appropriate recipients. The Calling Package doesn’t have to be this complicated, of course. If you use an unchanging value for CallingPackageID, you don’t need the Set Calling package ID task. If you don’t want to view the results in the package, you don’t need the Find Results and Report Results tasks. The only thing you need to call the utility is a Dynamic Properties task.
  12. The Execute Package Task 387 CHAPTER 18 FIGURE 18.5 18 You only need a Dynamic Properties task to call the utility, but you may want to add other tasks to set global variables PACKAGE TASK THE EXECUTE and report results. Creating the Task in Visual Basic I have created a Visual Basic procedure, fctCreateExecutePackageTask, that creates a step, a task, and a custom task for an Execute Package task. All the properties of the task can be set with this procedure. The procedure does not let you add any members to the GlobalVariables collection. You can add dynamic value global variables with the InputGlobalVariableNames property. You can find the code for it in the directory for Chapter 18 on the book’s CD as a Visual Basic Project, with files CreateExecutePackageTask.vbp, CreateExecutePackageTask.frm, and CreateExecutePackageTask.bas. The code for fctCreateExecutePackageTask is shown in Listing 18.1. The procedure needs some utility functions that are included with the code listings on the CD. The project requires a reference to the Microsoft DTSPackage Object Library.
  13. Control Tasks 388 PART IV LISTING 18.1 The Visual Basic Code to Create an Execute Package Task Option Explicit Public Function fctCreateExecutePackageTask( _ pkg As DTS.Package2, _ Optional sBaseName As String = “ExecutePackageTask”, _ Optional sFileName As String = “”, _ Optional sInputGlobalVariableNames As String = “”, _ Optional sPackageID As String = “”, _ Optional sPackageName As String = “”, _ Optional sPackagePassword As String = “”, _ Optional sRepositoryDatabaseName As String = “”, _ Optional sServerName As String = “(local)”, _ Optional sServerPassword As String = “”, _ Optional sServerUserName As String = “”, _ Optional bUseRepository As Boolean = False, _ Optional sVersionID As String = “”) As String On Error GoTo ProcErr Dim stp As DTS.Step2 Dim tsk As DTS.Task Dim cus As DTS.ExecutePackageTask ‘Check to see if the selected Base name is unique sBaseName = fctFindUniqueBaseName(pkg, sBaseName) ‘Create task and custom task Set tsk = pkg.Tasks.New(“DTSExecutePackageTask”) Set cus = tsk.CustomTask With cus .Name = “tsk” & sBaseName .Description = sBaseName .FileName = sFileName .InputGlobalVariableNames = sInputGlobalVariableNames .PackageID = sPackageID .PackageName = sPackageName .PackagePassword = sPackagePassword .RepositoryDatabaseName = sRepositoryDatabaseName .UseRepository = bUseRepository
  14. The Execute Package Task 389 CHAPTER 18 LISTING 18.1 Continued .ServerName = sServerName .VersionID = sVersionID If sServerUserName = “” Then .UseTrustedConnection = True Else .ServerPassword = sServerPassword .ServerUserName = sServerUserName .UseTrustedConnection = False End If End With pkg.Tasks.Add tsk ‘Create step for task Set stp = pkg.Steps.New With stp .Name = “stp” & sBaseName .Description = sBaseName 18 .TaskName = tsk.Name PACKAGE TASK THE EXECUTE End With pkg.Steps.Add stp fctCreateExecutePackageTask = stp.Name Set tsk = Nothing Set cus = Nothing Set stp = Nothing ProcExit: Exit Function ProcErr: MsgBox Err.Number & “ - “ & Err.Description fctCreateExecutePackageTask = “” GoTo ProcExit End Function
  15. Control Tasks 390 PART IV Conclusion The Execute Package task gives you a convenient way to execute one package from another, which can help you organize your DTS processing more efficiently. The biggest drawback of the task is the inability to return values from the child package to the parent package. Chapter 12, “The Execute SQL Task,” and Chapter 26, “Managing Packages with Visual Basic and Stored Procedures,” show you how to execute packages from one another in a way that allows for two-way communication.
  16. The Message Queue Task CHAPTER 19 IN THIS CHAPTER • When to Use the Message Queue Task 392 • Creating the Task and Setting Its Properties 393 • Creating the Task in Visual Basic 400
  17. Control Tasks 392 PART IV The Message Queue task uses message queuing to send and receive messages from DTS pack- ages. You can send and receive simple string messages or messages that contain files or values for global variables. You use the Message Queue task to coordinate the execution of two or more DTS packages. One package has a Message Queue task configured for sending. When that task executes, it adds a message to the specified queue. The other package has a Message Queue task config- ured for receiving. When that task is executed, it looks for an appropriate message in the queue. If it finds one, the task completes successfully. If it does not find the message, it contin- ues checking until the message appears in the queue or the period of time specified by the ReceiveMessageTimeout property expires. NOTE If you don’t have the MSMQ client installed on your computer, you will receive a warning message when you attempt to create a Message Queue task. You are allowed to create the task and set its properties. However, the task will not success- fully execute unless you install the MSMQ client. When to Use the Message Queue Task Both the Message Queue task and the Execute Package task are used to coordinate the execu- tion of two or more DTS packages. Here are the factors in deciding which one of these tasks to use: • Use the Message Queue task when you need asynchronous processing of two or more packages. Use the Execute Package task to call one package from another in situations where both packages are available at the same time. You can use the Message Queue task in situations where one or more of the packages are executing on computers that are not always connected to the network. • Use the Dynamic Properties task when you need the tasks of two or more packages to be joined in a transaction. The Message Queue task does not support transactions. • You can use the Message Queue task to send string messages, global variables, or files from one package to another. You can send only global variables with an Execute Package task.
  18. The Message Queue Task 393 CHAPTER 19 Here are some examples of situations where you could use a Message Queue task: • One DTS package has tasks that shouldn’t be executed before the successful execution of other packages. You can create the package with several Message Queue tasks for receiv- ing messages. The other packages can add a message to the queue to signal when they have successfully completed execution. Each of the message-sending packages can send a string message, send a file, or set global variables in the receiving package. • A complex DTS package could be divided into parts that are executed on different com- puters. The precedence constraints connecting steps can be replaced by a set of send and receive Message Queue tasks. Global variables that are used throughout the package can be sent back and forth with those tasks. The main limitation is that you can’t bind all the packages together into a transaction, as you would be able to do if you were using Execute Package tasks. • After creating a set of local cubes, the cube files could be sent throughout an organiza- tion with a Message Queue task. All the computers receiving the cube files could save the cube file to a local path without removing the message from the queue. When a new local cube was created, the sending package could remove the previous message from the queue before adding the message with the new cube. Creating the Task and Setting Its Properties The primary choice you make when creating a Message Queue task is whether its function is to send or receive. You make this choice in the Messages box of the Message Queue Task Properties dialog, shown in Figure 19.1. This dialog shows you a listing of all the messages that have been created when you are configuring the task to send. 19 THE MESSAGE QUEUE TASK FIGURE 19.1 The Properties dialog of the Message Queue task when sending a message.
  19. Control Tasks 394 PART IV The choice to send or receive messages is implemented with the custom task’s TaskType prop- erty. This property uses the DTSMQType constants: • 0—DTSMQType_Sender • 1—DTSMQType_Receiver You can also set the Description property for the task in the Message Queue Task Properties dialog. The Name property is set automatically. Setting the Queue Path Whether you are sending or receiving messages, you have to enter the path of the queue that you want the task to use. The queue path has the following syntax. • For a private queue: computer_name\PRIVATE$\queue_name • For a public queue: computer_name\\queue_name • For a public queue on the local computer: .\\queue_name The queue path is assigned with the QueuePath property of the custom task object. Sending Messages The Message Queue Task Properties dialog has buttons for adding, editing, and deleting mes- sages from the task. The Add and Edit buttons call the Message Queue Message Properties dia- log, shown in Figure 19.2. FIGURE 19.2 The Message Queue Message Properties dialog allows you to add or edit messages.
  20. The Message Queue Task 395 CHAPTER 19 The primary choice in the Message Queue Message Properties dialog is the type of message to send—String Message, Data File Message, or Global Variables Message. Depending on which one you choose, the dialog presents an interface for typing in the string, picking the file, or adding the global variables. The custom task object has a collection of messages called the DTSMQMessages collection. The DTSMQMessage object has the MessageType property, which uses one of these DTSMQMessageType values: • 0—DTSMQMessageType_String • 1—DTSMQMessageType_DataFile • 2—DTSMQMessageType_GlobalVariables The DTSMQMessage object also has a set of properties, one of which is used for each of the message types: • MessageString—The text of the string message being sent. • MessageDataFile—The path and filename of the data file being sent. • MessageGlobalVariables—A semicolon-delimited list of the names of the global vari- ables being sent: ‘NameVar1’;’NameVar2’;’NameVar3’; NOTE The DTSMQMessage object has two additional properties that cannot be set in the Message Queue Message Properties dialog and are only partially documented in Books Online—UseTransaction and WaitForAcknowledgement. You can view these properties with Disconnected Edit. 19 THE MESSAGE Receiving Messages QUEUE TASK If you choose to create a Message Queue task for receiving, you choose one of the three options in the Message Types box of the Message Queue Task Properties dialog. All of these types give you choices for filtering the messages. The message type choice is implemented with the ReceiveMessageType property of the Message Queue custom task. This property uses the same DTSMQMessageType values that are used by the Message object’s MessageType property.
Đồng bộ tài khoản