The SSIS Package Designer

The SSIS Package Designer contains the design panes that you'll use to create a SSIS package. The tool contains all the items you need to move data or create a workflow with minimal or no code. The Package Designer contains four tabs: Control Flow, Data Flow, Event Handlers, and Package Explorer. One additional tab, Progress, also appears when you execute packages. In this chapter, you'll mainly explore the Controller Flow tab. Unlike SQL Server 2000 DTS, where control and data flow were intermingled, control flow and data flow editors are completely separated by these tabs. This usability feature gives you greater control when creating and editing packages. The task that binds the control flow and data flow together is the Data Flow task, which you'll study in depth over the next two chapters.

Controller Flow

The controller flow is most similar to SQL Server 2000 DTS, since it contains most of the tasks you're used to in SQL Server 2000. It contains the workflow parts of the package, which include the tasks and precedence constraints. SSIS has introduced the new concept of containers, which was briefly discussed in Chapter 1. In the Control Flow tab, you can click and drag a task from the Toolbox into the Controller Flow designer pane. Once you have a task created, you can double-click the task to configure it. Until the task is configured, you may see a yellow warning on the task.

After you configure the task, you can link it to other tasks by using precedence constraints. Once you click on the task, you'll notice a green arrow pointing down from the task, as shown in Figure 2-17.

Figure 2-17

To create an On Success precedence constraint, click on the arrow and drag it to the task you wish to link to the first task. In Figure 2-18, you can see the On Success precedence constraint between a File System task and a Data Flow task. (Notice the warning icon on the Data Flow task, because it hasn't been configured yet.) You can also see an On Failure constraint, which is represented as a red arrow between the File System task and the Send Mail task. This type of controller flow may send a message to an operator in the event that the file operation fails.

Figure 2-18

When you click on a transformation in the Data Flow tab, you'll also see a red arrow pointing down, enabling you to quickly direct your bad data to a separate output. In the Controller Flow, though, you'll need to use a different approach. If you'd like the next task to execute only if the first task has failed, create a precedence constraint as was shown earlier for the On Success constraint. After the constraint is created, double-click on the constraint arrow and you'll be taken to the Precedence Constraint Editor (shown in Figure 2-19).

Figure 2-19

In this editor, you can set what type of constraint you'll be using in the Value drop-down field: Success, Failure, or Completion. In SSIS 2005, you have the option of adding a logical AND or OR when a task has multiple constraints. In DTS 2000, a task with multiple constraints would execute only if all constraints evaluated to True. This, of course, was a problem when a task had two or more error constraints that preceded it because both tasks had to fail before the subsequent task would execute. In the Precedence Constraint Editor in SSIS 2005, you can configure the task to only execute if the group of predecessor tasks has completed (AND) or if any one of the predecessor tasks has completed (OR). If a constraint is a logical AND, the precedence constraint line is solid. If it is set to OR, the line is dotted. This is useful if you want to be notified if any one of the tasks fails by using the logical OR constraint.

In the Evaluation Operation drop-down box, you can edit how the task will be evaluated.

• Constraint: Evaluates the success, failure, or completion of the predecessor task or tasks

• Expression: Evaluates the success of a customized condition that is programmed using an expression

• Expression and Constraint: Evaluates both the expression and the constraint before moving to the next task

• Expression or Constraint: Determines if either the expression or the constraint has been successfully met before moving to the next task

If you select Expression or one of its variants as your option, you'll be able to type an expression in the Expression box. An expression is usually used to evaluate a variable before proceeding to the next task. For example, if you want to ensure that Variable1 is equal to Variable2, you would use the following syntax in the Expression box:

 @Variable1 == @Variable2 

Note

You can also single-click on the constraint and use the Properties Window to the right to set these properties if you prefer not to use the editor.

Task Grouping

A very nice usability feature in SSIS is the ability to group tasks logically in containers. For example, if you have a group of tasks that create and purge the staging environment, you can group them together so that your package is not cluttered visually. For example, in Figure 2-20 there are two tasks to create and purge staging. To group them, select both tasks by clicking one task and holding the CTRL key down while you select the second task. Then, right-click on the tasks and select Group.

Figure 2-20

Once you have the two tasks grouped, you'll see a box container around the tasks. This container will be called Group by default. To rename the group, simply double-click on the container and type the new name over the old one. You can also collapse the group so that your package isn't cluttered. To do this, just click the arrows that are pointing downward in the group. Once collapsed, your grouping will look like Figure 2-21. You can also ungroup the tasks by right-clicking on the group and selecting Ungroup.

Figure 2-21

Annotation

Annotation is a key part of any package that a good developer never wants to leave out. An annotation is a comment that you place in your package to help others and yourself understand what is happening in the package. To add an annotation, right-click where you'd like to place the comment and select Add Annotation. It is a good idea to always add an annotation to your package that shows the title and version your package is on. Most SSIS developers like to also put a version history annotation note in the package so that they can see what's changed in the package between releases and who performed the change. You can see both of these examples in Figure 2-22. Note that the group from Figure 2-21 has been expanded.

Figure 2-22

Connection Managers

You may have already noticed that there is a Connection Managers tab at the bottom of your Package Designer pane. This tab contains a list of data connections that both control flow and data flow tasks can use. Whether the connection is an FTP address or a connection to an Analysis Services server, you'll see a reference to it here. These connections can be referenced as either source or targets in any of the operations and can connect to relational or Analysis Services databases, flat files, or other data sources.

When you create a new package, there are no connections defined. You can create connections by right-clicking in the Connections area and choosing the appropriate data connection type. Once the connection is created, you can rename it to fit your naming conventions or to better describe what is contained in the connection. Even if you have a shared connection defined for your project, it won't be usable in the package until you add it to the Connection Managers tab. Nearly any task or transformation that uses data will require a Connection Manager. There are a few exceptions, such as the Raw File destination and source that you'll learn about in the next chapter, that allow you to define your connection inline. Figure 2-23 shows two connections: one to a relational database (AdventureWorks) and another to a flat file (Sample Data).

Figure 2-23

Variables

Variables are a powerful piece of the SSIS architecture; they allow you to dynamically control the package at runtime, much like you do in any .NET language. In SQL Server 2000 terms, variables are closest to global variables, but they've been improved on greatly, as you'll see in Chapters 5 and 6. There are two types of variables: system and user. System variables are ones that are built into SSIS, whereas user variables are created by the SSIS developer. Variables can also have varying scope, with the default scope being the entire package. They can also be set to be in scope of a container, task, or event handler inside the package. The addition of scope to variables is the main differentiating factor between SSIS variables and DTS global variables.

One of the optional design-time windows can display a list of variables. To access the Variables Window, right-click in the design pane and select Variables. The Variables Window (shown in Figure 2-24) will appear where the Toolbox was, and you can toggle between the two windows by selecting the corresponding tab below the window. By default, you will see only the user variables; to see the system variables as well, select the Show System Variables icon in the top of the window. To add a new variable, click the Add Variable icon in the Variables Window and type the name.

Figure 2-24

You'll find yourself using system variables throughout your package habitually for auditing or error handling. Some of the system variables that are in the scope of a package that you may find interesting for auditing purposes are listed in the following table.

Variable Name	Data Type	Description
CreationDate	DateTime	The date when the package was created.
InteractiveMode	Boolean	Indicates how the package was executed. If the package was executed from BIDS, this would be set to true. If it was executed as a job, it would be set to false.
MachineName	String	The computer where the package is running.
PackageID	String	The unique identifier (GUID) for the package.
PackageName	String	The name of the package.
StartTime	DateTime	The time when the package started.
UserName	String	The user that started the package.
VersionBuild	Int32	The version of the package.

Variables will be discussed in greater detail in each chapter. For a full list of system variables, please refer to Books Online under "System Variables."

Data Flow

When you create a Data Flow task in the Controller flow, a subsequent data flow is created in the Data Flow tab. You can expand the data flow by double-clicking on the task or by going to the Data Flow tab and selecting the appropriate Data Flow task from the top drop-down box (shown in Figure 2-25). The data flow key components are sources, destinations, transformations, and paths. The green and red arrows that were the precedence constraints in the Control Flow tab are now called paths.

Figure 2-25

When you first start defining the data flow, you will create a source to a data source and then a destination to go to. The transformations (also known as transforms throughout this book) modify the data before it is written to the destination. As the data flows through the path from transform to transform, the data changes based on what transform you have selected. The red arrow that connects the transforms named Fix Bad Records and Add Audit Info in Figure 2-25 writes the bad records to a destination such as an error queue or moves data down a different path if an error occurs. This entire process is covered in much more detail in Chapter 4.

Event Handlers

The Event Handlers tab allows you to create workflows to handle errors or changes in events. If you wanted to handle errors in SQL Server 2000, you had to create an On Failure precedence constraint that led to an error-handling task off of each task you wanted to monitor. Now in SQL Server 2005 SSIS, you can do this globally across your entire package. For example, if you want to trap any errors and have them e-mailed to you, you could create an OnError event handler and configure it to send a message out to an operator, as shown in Figure 2-26.

Figure 2-26

You can configure the event handler scope under the Executable drop-down box. An executable can be a package, Foreach Loop, For Loop, Sequence, or task host container. In the Event Handler box, you can specify the event you wish to monitor for. The events you can select are in the following table.

Event	When Event Is Raised
OnError	When an error occurs
OnExecStatusChanged	When an executable's status changes
OnInformation	When informational event is raised during the validation and execution of an executable
OnPostExecute	When an executable completes
OnPostValidate	When an executable's validation is complete
OnPreExecute	Before an executable runs
OnPreValidate	Before an executable's validation begins
OnProgress	When measurable progress has happened on an executable
OnQueryCancel	When a query has been instructed to cancel
OnTaskFailed	When a task fails
OnVariableValueChanged	When a variable is changed at runtime
OnWarning	When a warning occurs in your package

Event handlers are critically important to developing a package that is "self-healing" and can correct its own problems. You'll learn more about event handlers in Chapter 13.

Package Explorer

The final tab in the SSIS Package Designer is the Package Explorer tab. This tab consolidates all the design panes into a single view. It's similar to the disconnected edit dialog box in SQL Server 2000 DTS. The Package Explorer tab (shown in Figure 2-27) lists all the tasks, connections, containers, event handlers, variables, and transforms in your package, and you can double-click on any item here to configure it easily. You can also modify the properties for the item in the right Properties Window after selecting the item you wish to modify.

Figure 2-27

Executing a Package

When you want to execute a package, you can click on the Play icon on the toolbar, press F5, or choose Start from the Debug menu. This puts the design environment into execution mode, opens several new windows, enables several new menu and toolbar items, and begins to execute the package. When the package finishes running, BIDS doesn't immediately go back to design mode but rather stays in execution mode to allow you to inspect any runtime variables or to view any execution output. This also means that you can't make any changes to the objects within the package, but you can modify variables and objects' read/write properties. You may already be familiar with this concept from executing .NET projects.

To get back to design mode, you must click on the Stop icon on the debugging toolbar, press Shift+F5, or choose Debug Stop Debugging.