Microsoft SQL Server 2000 Data Transformation Services- P2

Microsoft SQL Server 2000 Data Transformation Services- P2: 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- P2 Getting Started with DTS26 PART I NOTE Many DTS developers have run into the security problem when they schedule their DTS packages. You can log on to your computer with the same user ID as the SQL Agent is using. This will allow you to test your DTS packages in the security context that will be used when the package is executed by the SQL Agent. The performance issue can be confusing because some of the DTS tasks are executed locally where the package is executing, while others are executed remotely on the database server. Here are some of the tasks that are executed in the local context: • All the transformation tasks—The Transform Data, Data Driven Query, and Parallel Data Pump tasks. The performance of these tasks can be significantly degraded when they are executed on one machine and the data they are processing is on another. • The Execute Process task. • The Execute Package task. The following list contains tasks that are executed on the database server and not on the machine where the package is executed. If you are only using these tasks, you will not have performance problems caused by execution context: • The Bulk Insert task • The Copy SQL Server Objects task • The Transfer Databases task • The Execute SQL task Here are some of your options for executing a DTS package on a database server, instead of on your workstation, when you do not have direct access to the server: • Use remote management software and execute the package using DTSRun. • Schedule the package on the server. Make sure the SQL Server Agent has all the permis- sions needed to carry out the package’s operation. • Execute the package on the server using the OLE Automation system stored procedures or by using xp_cmdshell with DTSRun. You could do either of these from an Execute SQL task within another DTS package. Chapter 26 has more information on the solution using the OLE Automation system stored procedures. A Quick Look at DTS 27 CHAPTER 1Using the DTS Object Model for Programmatic 1Control A QUICK LOOK AT DTSDTS is implemented with the Component Object Model (COM). You can manipulate DTSpackages and their components through the appropriate collections, objects, properties, andmethods that are exposed in the DTS object model. NOTE If you’re a programmer, you’ll be interested in this section. If you’re not a programmer (and I know a lot of excellent database developers who aren’t), you’ll still find this material valuable. You can access the properties of DTS objects through two new features—the Dynamic Properties task and Disconnected Edit. These tools allow you to manipulate object properties directly, without pro- gramming. Throughout the book, I will attempt to connect what you can do in the DTS Designer interface with what you can do with the DTS object model, so that you can use code, the Dynamic Properties task, and Disconnected Edit to extend your control over DTS.The DTS object model is the theme of Chapter 30. Almost all of the chapters in this book referto the various structures within the object model.The DTS Object ModelThe Package object is at the top of the DTS object hierarchy. All the objects that do the workof a DTS package stem from this object.The Package object contains four collections of objects: • Connections—Defined links to particular sources of data. • Tasks—The actions that take place in a package. • Steps—An object associated with a task that controls how the task fits into the workflow of the package as a whole. • Global Variables—Variables that allow for sharing of information between the various tasks. Values of global variables can also be sent into a package.Each task has an associated object called a custom task that contains the properties specific tothat particular kind of DTS task. Getting Started with DTS28 PART I There are several other DTS objects that do not fit under the Package object. These other objects primarily provide information: • Application—System properties and informatio ...