Hands-On Microsoft SQL Server 2008 Integration Services part 34. Deploy and manage high-performance data transformation solutions across your enterprise using the step-by-step techniques in this fully revised guide. Hands-On Microsoft SQL Server 2008 Integration Services, Second Edition explains the tools and methods necessary to extract conclusive business intelligence from disparate corporate data. Learn how to build and secure packages, load and cleanse data, establish workflow, and optimize performance. Real-world examples, detailed illustrations, and hands-on exercises are included throughout this practical resource. . | 308 Hands-On Microsoft SQL Server 2008 Integration Services because both the containers were running under one transaction that was started by the package and when one of the tasks in a container fails the transaction rolls back all the work done by previous tasks. One lesson to learn from this exercise is that the parent container which is the package in this case must have its TransactionOption property set to Required to start a transaction and the child containers need to have at least the Supported attribute for this property. Exercise Case III Transaction Spanning over Multiple Packages In the last part of this exercise you will use a transaction to roll back the inconsistent data when your loading process uses multiple packages. When you have multiple packages to process you use the Execute Package task to embed them inside a single package to run them. The Execute Package task is basically a wrapper task that enables a package to be used inside another package. The Execute Package task is covered in Chapter 5. 26. Right-click the SSIS Packages node in the Solution Explorer window and choose New SSIS Package from the context menu. You will see that the new package has been added with the default name of and the screen is switched to the new package. Note that the Designer shows these two packages as tabs. 27. Go to right-click the Connection Manager and choose Copy. Switch back to and paste this connection manager in the Connection Managers area. 28. Again go to and cut the Sequence Container 1 with Loading Vehicle Task return to and paste this container on the Control Flow. You will see a validation error about the connection manager on the Loading Vehicle task. This is because the ID for the Connection Manager has been changed. 29. Double-click the Loading Vehicle task icon to open the editor. In the Connection field choose .
