Microsoft SQL Server 2000 Data Transformation Services- P10

Microsoft SQL Server 2000 Data Transformation Services- P10: 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- P10 Control Tasks426 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. The Execute Process Task 427 CHAPTER 22Executing a Batch File Containing osql and/or bcpCommandsIf you have existing bcp commands in batch files, whether moving data into or out of SQLServer, you can keep on using those batch files with your DTS package by calling them fromthe Execute Process task.You can also use osql, the SQL Server command-line utility for executing SQL commands, inthese batch files. For example, you could write a batch file that creates a view, uses that viewto bulk copy data out of SQL Server, and then drops the view. The file would look like this: 22osql /S(local) /E /dpubs /Q”create view dbo.vwAuthorName(fullname)➥as select au_fname + ‘ ‘ + au_lname from pubs.dbo.authors” PROCESS TASK THE EXECUTEbcp “pubs.dbo.vwAuthorName” out C:TempAuthorName.txt /c /T /S(local)osql /S(local) /E /dpubs /Q”drop view dbo.vwAuthorName”If you save this batch file as c: emp estdts.bat, you would then enter that filename in theWin32 Process of the Execute Process task. You would not use any parameters for the task.Running Other Data Movement or ManipulationApplicationsYou 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 DTSRunYou can execute DTS packages from the Execute Process task by using the DTSRun com-mand-line utility:DTSRun /E /N PackageName /F c: empLoadEmployee.dts NOTE You have greater control in executing one package from another when you use the Execute Package task. Control Tasks428 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. • Descripti ...