Section 6

Containers

  • Lesson 41: Using Sequence Containers to Organize a Package
  • Lesson 42: Using For Loop Containers to Repeat Control Flow Tasks
  • Lesson 43: Using the Foreach Loop Container to Loop Through a Collection of Objects

Chapter 41

Using Sequence Containers to Organize a Package

Sequence Containers provide a simple and easy method for organizing the flow of a package and can help you divide a package into more manageable pieces. When you first begin exploring the Sequence Container, you may think organization is the only benefit it provides. However, to the creative developer, this container’s uses go far beyond simple organization. If you know how to use it, it can also grant you the following capabilities:

  • Grouping tasks so that you can disable a part of the package that’s temporarily not needed
  • Narrowing the scope of a variable to just the container
  • Collapsing and expanding the container to hide the tasks within
  • Managing the properties of multiple tasks in one step by setting the properties of the container
  • Using one method to ensure that multiple tasks execute successfully before the next task executes
  • Creating a transaction across a series of data-related tasks, but not on the entire package
  • Creating event handlers on a single container so that you can send an e-mail if anything inside one container fails, and perhaps even page yourself if anything else fails (event handlers are discussed in more detail in Lesson 48)

To add a Sequence Container to a package, drag and drop the Sequence Container in the design pane just like you would any other task. To have a task as part of the container, just drag the task within the outlined box.

Once tasks have been placed inside the Sequence Container, they can be connected by precedence constraints only to other tasks within the same container. If you attempt to connect a task inside the container to one outside, you receive an error.

Try It

In this Try It, you explore how Sequence Containers can be used inside a package. After this lesson, you will have a better idea of the versatility that using Sequence Containers can give you when you are developing your own packages.

You can download the completed Lesson41.dtsx at www.wrox.com.

Lesson Requirements

Create a package with Sequence Containers and test different uses of the container. Just use Script Tasks to test inside the containers because Script Tasks do not require any configuration. You really are just learning more about Sequence Containers in this lesson.

Hints

  • To do this example, you need three Sequence Containers with three Script Tasks inside each.

Step-by-Step

1. Create a new package and name it Lesson41 or download the completed Lesson41.dtsx package from www.wrox.com.
2. Drag three Sequence Containers onto your designer and then place three Script Tasks inside each container. Connect the precedence constraints from each container to make your package look like Figure 41-1. Feel free to run this package as is because the Script Task requires no further configuration. The package will complete successfully without changing any data.

Figure 41-1

c41f001.tif
3. The next several steps will help you better understand the benefits and limitations of this container. First, attempt to connect the precedence constraint from any Script Task inside Sequence Container 1 to any other object in the package. You will receive the error shown in Figure 41-2 because objects inside a Sequence Container cannot be connected to any component outside the container.

Figure 41-2

c41f002.tif
4. With individual tasks, you can execute a single task while excluding the rest of the package. You can also do this with entire containers. Right-click the first container and click Execute Container to execute just the tasks that are inside the container. Notice that just the first container executes while the rest of the package remains inactive. Once you’re ready to go to the next step, click the stop debugging button to continue.
5. Containers also enable you to scope variables exclusive to the contents of the container. Click once on Sequence Container 2 and open the Variables window. Create a variable called Lesson41 that has a scope set to Sequence Container 2 (creating variables is discussed in Lesson 32).
6. Next, right-click and disable Sequence Container 1 (the container in the middle) and then run the package. The results in Figure 41-3 demonstrate how Sequence Containers enable you to disable entire sections of a package with the container. Though you can’t see color in this figure, the outer two containers should have green checkmarks and the middle container is gray, indicating it is disabled. Stop debugging the package.

Figure 41-3

c41f003.tif
7. Finally, you can collapse a container simply by clicking the arrow pointing upward next to the container name. Figure 41-4 shows all three containers collapsed. This action does not change how the package runs, but just hides the content. To expand the containers again, click the arrows that are now pointed down.

Figure 41-4

c41f004.tif

This should give you a basic understanding of how Sequence Containers work. These same principles can be applied to containers that are discussed in the next lessons.

Please select Lesson 41 on the DVD, or online at www.wrox.com/go/ssis2012video, to view the video that accompanies this lesson.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset