In SQL Server Integration Services (SSIS), the Business Intelligence Development Studio (aka the designer) is perhaps the most visible new addition, and because the designer is how you'll generally interact with SSIS, it's also one of the most important. In "Making Package Magic," October 2004, InstantDoc ID 43805, I introduced the new Integration Services Import/Export Wizard and showed you how to use it to create a simple data-import package. This time around, I show you how to use the SSIS designer to load and modify the packages you create with the wizard. But first, let's look at some of the new features that make creating and editing packages simpler.
Precedence Constraints
SQL Server 2000 data transformation services (DTS) allows only three options for precedence constraints: Success, Failure, and Completion. In addition, all the precedence constraints for the same task use an AND operator, which means that all precedent constraints for a common task must have their precedence conditions met before the task will execute. (For a brief explanation of how precedence constraints work in DTS, see the Microsoft article "DTS Package Workflow" at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_elemwkflow_659z.asp.)
But SSIS provides a logical OR operator that lets the target container or task execute if any of the precedence constraint conditions are satisfied. This OR operator makes it easier to do things such as execute a task if one of several tasks fails. Figure 1 shows the Precedence Constraint Editor, in which you can choose constraint options and select whether SSIS uses an AND or an OR operator to process a package's constraints. You open the Precedence Constraint Editor by double-clicking a precedence constraint.
Figure 2 shows the workflow of tasks in a typical package. In the figure, you'll notice a Send Mail Task (SMTsk), which I've configured to send mail to me if either the SQL Task (SQLTsk) or the Data Flow Task (DFTsk) fails. The dotted lines in the figure indicate an OR precedence constraint, and the solid line indicates an AND relationship. In Figure 2's package, if either SQLTsk or DFTsk fails, SMTsk will execute. However, DFTsk will execute only if SQLTsk succeeds.
The Precedence Constraint Editor is also where you specify an expression. SSIS lets you use Boolean expressions to determine whether precedence constraints fire. You can also use expressions to see the execution result of the previous task or container. In Figure 2, you can see how the designer shows the expression @ResultCount>0 on the designer surface. In the Evaluation operation field, which controls how constraint and expression relate, you can select from four options: Constraint, Expression, Constraint and Expression, and Constraint or Expression. If you select Constraint, SSIS considers only the constraint to determine whether the next dependent container or task should execute. With the Expression option, Integration Services evaluates only the expression. With the Constraint and Expression option, both must evaluate to true for the precedence constraint to fire. And when you select Constraint or Expression, if either evaluates to true, the precedence constraint will fire. In Figure 2, the Data Flow Task will execute only if the SQL Task succeeds and the variable ResultCount is greater than 0. If either of those two tasks fails, the Send Mail Task will execute.
In Figure 2, notice the yellow indicator in the Send Mail Task. That icon means that a warning occurred during validation. Warnings tell the package writer that something might be wrong with the package or task, but that the problem probably won't cause the package to fail. In this particular case, I have an incorrectly formatted email address in the task. When I correct the email address, the warning indicator will go away. The red dot on the SQL Task indicates that there's a breakpoint on that task. Breakpoints are part of the suite of debugging features in SSIS 2005 that make it possible to perform real debugging of packages.
Context Menus
One of the first things you should do when you start the Business Intelligence Development Studio is right-click everything. Just about everywhere, you'll find context menus giving you quick and convenient ways to access options. Most of the menu items are self explanatory, but Figure 3 shows a few interesting context-menu options. In particular, notice the new Disable option, which lets you disable one task, a whole container, or multiple tasks simultaneously by selecting the tasks you want to disable and right-clicking one of them. Disabling means that when you execute the package, the disabled task or container won't execute. The default behavior for disabled tasks and containers is to return as though they succeeded. This feature is available for custom tasks without any special coding and is helpful when you're diagnosing troublesome packages. You can also use the Disable option with the ForceExecutionResult property (which you find in the property grid for all containers and tasks) to simulate certain conditions or isolate parts of a package for specific purposes, such as forcing sequences or execution paths. For example, you can force a task to simulate failure by setting the ForceExecutionResult property to Failed in the property grid and disabling the task. Sometimes, while you're building a package, a task isn't yet functional because you don't have all the inputs or information you need. In such a situation, you can disable the task until the needed inputs become available.
Source-control integrated support is another new option. Figure 4 shows the Business Intelligence Development Studio's Solution Explorer. Notice the icons next to the files, projects, and solution. The icons are visual indicators of each file's source-control status. Right-clicking any file brings up a context menu that lets you check out, check in, compare, and synchronize files—if you have Visual SourceSafe installed.