Managing with control flow

- The Integration Services Control Flow. Here I have SQL Server Development Tools up and I'm running a project in here and I've already built out and added a couple tasks, as you can see. A package must have a Control Flow in it. From there on, everything else is optional, but a Control Flow within Integration Services has three potential objects that it can use. It has tasks, here we're seeing two tasks.

Actually, there's four, but this task, this task, and then these other tasks, which are copies of this one and this one. All I did, by the way, was right-click and copy and then paste them on out. So we have tasks, we have containers. There's three kinds of containers. A Sequence Container, which I'm showing you here, a For Loop and a For Each Loop, which we'll use a little bit later on. The other type of object that we can have is known as a precedent constraint.

A precedent constraint does this for me. If I click on this, you see the green arrow, and if I drag it over and drop it so that the green arrow, the on success arrow, connects this task to this one, what that means is in the term, precedent constraint. The second task, Back up Database, will not execute until we have success on this Execute SQL Task.

Let me delete. See, now I can change it. Failure, so now this won't run unless I have a failure, or on completion, which is the black. I'll right-click it again and I'm going to come in here and I'm going to delete it on out and I want to show you this working. By the way, this Sequence Container down here, the object of a Sequence Container is because you could rapidly, in these packages, end up with a lot of tasks, which makes debugging difficult.

You could take various tasks and put them into Sequence Containers, so then you can dictate what happens in what order, so that a Sequence Container is a containment of other tasks and it just makes it easier to debug. Let's connect this over here and now we're going to execute the package. I'm gonna go over here in Solution Explorer and I'll right-click on it and I'll call Execute Package. We'll let that run. Now you see the little spins, which means it's executing.

You see the Back Up Database, that was almost instantaneous. The Back Up Database Ran and then we popped over to the Execute SQL Task. I'll show you that code in a second. It's just a little query, wanting to know what happened. Then you'll notice then the Sequence Container went through and ran in the same manner. Let's break. I'm gonna go Debug, Stop Debugging, so we can get back over to where we were. Now, I said I'd show you what this code looks like, so I'm going to double-click on this Execute SQL Task and that will open on up.

If I come on down here, here's the SQL Statement. If I get my ellipsis, there's the actual code that I popped in, and this is just a little canned code that says, "Select the top 1,000 results "from all of these columns from this database." I'll cancel on out of it. And cancel out of this one. Now, let's watch those precedent constraints happen again. Now this time I'm going to come in and I'll select this Back Up Database and I'll move that down and constrain the Sequence Container.

Within the Sequence Container, click there, delete it and let's reverse it just so it's cleaner to see. By the way, here's your zoom bar up and down if you want to zoom in and zoom out. Now I'm going to move this down so I can select. All right, we've changed some selections. So you should be able to figure out by now that Execute SQL is going to run at the same time as Back Up Database, because the Execute SQL over here is not constrained by the precedent constraint.

Now they'll run in parallel and then on completion we'll switch down to the Sequence Container. Let's see how she works. You see Execute SQL happened almost immediately. For all practical purposes, it was immediate. Now Back Up Database executed, we transferred down to the Sequence Container, which ran again the Execute SQL task and then the Back Up Database once again. So we have completed. Now, let's break out and I'm going to go Debug up in here and I'm going to go Stop Debugging, or Shift + F5 to do the same thing.

So that you don't become concerned because you haven't actually been adding these tasks and doing the code, don't be concerned, because later on in the course when we come through and we start building packages, then I'll walk through and I'll actually build a package. At that time, you can come through and build your own. Again, what you have there, that's the concept of Control Flow, so a package has to have a Control Flow in order to operate. The only other thing I want to show you very quickly because we cover this in more detail later is this Data Flow tasks, by the way.

See, I am in Control Flow and here's the things I can do in Control Flow, moving on down and there's my containers and here's other tasks. You come through and take a look at some of the other tasks and if you're familiar with SQL, you can see most of these are just SQL maintenance tasks. That's why we create maintenance packages within Integration Services. If I take a Data Flow task, before I do that, let me go to Data Flow tab, you notice there's nothing there. Back over to Control Flow and if I take a Data Flow task and drop it here and then go over to Data Flow, now all of a sudden I do have a Data Flow task.

I point that out, because we are going to go in later on into Data Flow. That's, frankly, where we're gonna spend most of our time and Control Flow constrains Data Flow. You can have multiple Data Flow tasks, very common. One Control Flow will constrain these Data Flow tasks so that if you're doing something like you're inserting into a database, a parent-child, and you've gotta do your insertions in a certain order, then you'll use one Control Flow to constrain the Data Flow tasks so that you go in the correct order.

And there's your Control Flow.