From the course: SQL Server Integration Services

Create a precedence constraint - SQL Server Tutorial

From the course: SQL Server Integration Services

Start my 1-month free trial

Create a precedence constraint

“

- [Male Instructor] The SSIS project now has two tasks: one that executes some SQL code and another that backs up the database. If we run the project right now, both of these tasks you're going to execute at the same time. I'd rather create the data table, enter the records first, using the execute SQL task, and then perform the back up after it. We can control the sequence of the package executes tasks using something called precedence constraints. Precedence constraints are depicted in the control flow diagram with lines and arrows that connect tasks together. You'll notice that when you click the execute SQL task, for example, you'll get a green arrow below it. To create the flow from one task to another, simply drag this arrow and drop it onto the next task. In this case, I'm going to drag it and drop it on the back up database task. Now, we're starting to visualize the flow chart of how this package will run. The execute sequel task will run first. Only when it's finished, will the back up database task start to run. In integration services terminology, the execute sequel task is known as the precedence executable, and the back up database task is called the constrained executable. The color of the arrow is important to pay attention to. A green arrow, like the one we have now, indicates a successful completion of the precedence executable. In other words, in this control flow, the back up database task will only run if the sequel task finishes successfully. If there's an error, the backup task won't run. You can control the type of precedence constraint by double clicking on the line to bring up the editor window. The option that I want to look at is this value. Right now it's set to success. The other options that you have in the drop down menu are failure and completion. Changing it to failure means that the back up task would only run if the sequel task fails for any reason. Let's select that option and press okay. You'll notice now that the line or the arrow has changed to a red color. Double click on the line again to get back into the editor, and this time I'll change it to completion. This sets up a situation where the back up will only run if the execute sequel task completes, regardless of whether it's finished successfully or failed. As soon as either outcome occurs, then the backup database will run. I'll say okay to this, and you'll notice that the line changes to a gray color, or it might be blue, depending on your accessibility settings. So by changing the value of the precedence constraint, you can control the conditions under which each task will run. As you create a control flow diagram linking multiple tasks and containers together, the flow chart might get a little bit messy and hard to follow. You can adjust the lines to clean up the display by clicking once on the line until you see the control points, and then dragging each segment of the arrow around. And if you have a lot of tasks, you can zoom in and out of the diagram using the slider on the right of the control surface. You can either zoom it in, or zoom it back out. Or you can press the button at the bottom to zoom to fit. Let's go back into the editor now and take a look at a couple of additional options that we have. Once again, I'll double click on the join line to bring up this editor. First, I'm going to change the value back to success. Then, let's take a look at this one here, called a valuation operation. It's currently set to constraint. The other options are expression, and then we have two combined operations, and and or. Constraint will only look at the status of the precedence executable to determine whether to run the next task or not. Using an expression, you can code a more complex logical statement, using functions, operators, and variables that evaluates to true or false. This will allow you to reach out into your database or server instance to define when to execute a task based off of external factors. I'm going to leave this set to constraint. At the bottom of the editor, you have the option to specify what happens when there are multiple constraints on a task. The logical and option will require that every constraint is satisfied before execution will occur. Logical or will only require that one constraint is met before the task will execute. So using all of these options together will allow you to dial in the precise circumstances under which your control flow will process tasks in the diagram.

Contents