{"id":617,"date":"2009-07-07T00:00:00","date_gmt":"2009-07-07T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/working-with-precedence-constraints-in-sql-server-integration-services\/"},"modified":"2021-06-03T16:45:29","modified_gmt":"2021-06-03T16:45:29","slug":"working-with-precedence-constraints-in-sql-server-integration-services","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/working-with-precedence-constraints-in-sql-server-integration-services\/","title":{"rendered":"Working with Precedence Constraints in SQL Server Integration Services"},"content":{"rendered":"
\n

The control flow in a SQL Server Integration Services (SSIS) package defines the workflow for that package. Not only does the control flow determine the order in which executables (tasks and containers) will run, the control flow also determines under what conditions they’re executed. In other words, certain executables will run only when a set of defined conditions are met.<\/p>\n

You configure the workflow by using precedence constraints<\/i>. Precedence constraints link the individual executables together and determine how the workflow moves from one executable to the next. Figure 1 shows the control flow for the PrecedenceConstraints.dtsx<\/b> package. The precedence constraints are the green and red arrows (both solid and dotted) that connect the tasks and container to each other. (There can also be blue arrow, as you’ll learn later in the article.)<\/p>\n

\"750-ST_PrecConstraints01.jpg\"<\/p>\n

Figure 1: Control Flow in the PrecedenceConstraints.dtsx SSIS package<\/p>\n

As you would expect, the arrows define the direction of the workflow as it moves from one executable to the next. For example, after the first Execute SQL<\/b> task runs, the precedence constraints direct the workflow to the next Execute SQL<\/b> task and the Sequence<\/b> container. One or both of these executables will run, depending on how the precedence constraints have been configured.<\/p>\n

When a precedence constraint connects executables, the originating executable (the first to run) is referred to as the precedence executable.<\/i> Multiple precedence constraints can originate from the precedence executable. In the first Execute SQL<\/b> task in Figure 1, two precedence constraints originate from that precedence executable.<\/p>\n

The task or container that is on the downstream end of the precedence constraint is referred to as the constrained executable<\/i>. The constrained executable will run only if the conditions defined on the precedence constraint are met. If the conditions are not met, the constrained executable will not run. As a result, by configuring the precedence constraints, you can create complex workflows, while minimizing the need to configure duplicate tasks and containers.<\/p>\n

Note that I created the PrecedenceConstraints.dtsx package in SSIS 2005. However, I upgraded the package to SSIS 2008 to ensure that precedence constraints are implemented the same in both versions. You can download the SSIS 2005 version of the PrecedenceConstraints.dtsx<\/a> package at the bottom of the article. <\/p>\n

If you want to run the PrecedenceConstraints.dtsx package, you should first run the following Transact-SQL code against the AdventureWorks sample database:<\/p>\n

IF EXISTS(\r\n  SELECT table_name FROM information_schema.tables \r\n  WHERE table_name = 'Employees')\r\nDROP TABLE Employees\r\nGO\r\nCREATE TABLE Employees \r\n(\r\n  EmployeeID INT PRIMARY KEY,\r\n  FirstName NVARCHAR(50) NOT NULL,\r\n  LastName NVARCHAR(50) NOT NULL,\r\n  Jobtitle NVARCHAR(50) NOT NULL\r\n)\r\nGO\r\nIF EXISTS(\r\n  SELECT table_name FROM information_schema.tables \r\n  WHERE table_name = 'EmployeeLog')\r\nDROP TABLE EmployeeLog\r\nGO\r\nCREATE TABLE EmployeeLog \r\n(\r\n  LogID INT IDENTITY PRIMARY KEY,\r\n  LogFile VARCHAR(50) NULL,\r\n  LogDateTime DATETIME NOT NULL DEFAULT GETDATE()\r\n)\r\n<\/pre>\n
The code creates the Employees table and EmployeeLog table, both of which are necessary to run the PrecedenceConstraints.dtsx package. The package itself archives employee data, logs information about the package execution, truncates the Employees table if necessary, retrieves data through the HumanResources.vEmployee view, and loads it into the Employees table. The package uses different types of precedence constraints to control the workflow for these various operations. We’ll look at the workflow and precedence constraints in closer detail as we work through the article.<\/p>\n
Defining Workflow by Success or Failure<\/h1>\n
By default, when a precedence constraint connects two executables, the constrained executable will run after the precedence executable successfully runs. However, if the precedence executable fails, that part of the workflow is interrupted, and the constrained executable does not run. You can override this behavior by setting the Value<\/b> property on the precedence constraint. The property supports three options:<\/p>\n