Define control flow tasks - SQL Server Tutorial

- [Instructor] In Visual Studio we currently have an integration services project started, which gives us an empty package that you can see over here on the main portion of the screen. This is an area called the Design Surface. If you close the Package tab, you can get it right back by double clicking on it over here in the Solution Explorer. Now, the Design Surface has a number of different tabs across the top. Right now we're looking at the Control Flow tab. This is where you'll define what your integration services package is going to do. You do that by dragging tasks from the Toolbox on the left into the Design Surface and then connect them together to build a flow chart-like diagram that defines the sequence that they should be executed in. So let's take a look at the Toolbox. At the top are a pair of tasks that are grouped together under the Favorites heading. If you click on a task to select it, the information window at the bottom of the screen gives you a description of what that task is going to do. So when I click on the Data Flow Task, it says that the task moves data between sources and destinations while performing transformations and cleansing. If the description is getting cut off you can make the information window larger just by dragging on this top edge. The other task underneath Favorites is called Execute SQL. This task executes SQL statements or store procedures in a relational database. Under the Favorites group are additional common tasks followed by some group containers, if you scroll down. And these are useful for creating looping procedures or to simply organize your Design Surface. And then at the very bottom of the list are some other tasks here in a separate group. You can rearrange any of these tasks by right clicking on them and moving them into a different category. So for instance, if you find that you use the Back Up Database Task regularly you can right-click on it and choose to move it up to Favorites. It'll move it up here into the top category. Let's go ahead and right-click on it again and move it back to Other. So let's create a simple control flow diagram using these tasks. I want to create a package that'll create a table in the Kinetico database, and then add a couple of records, and then back up the database. I'll start by adding in Execute SQL Task by double clicking on it in the toolbox. That'll add a reference over here in the control flow diagram. You'll notice that it currently has a red X which means it's not ready to run, and if you hover your mouse over it it says that there is no connection manager specified. That means that the task doesn't know what database it's supposed to execute SQL code in. So we need to connect this task to the Kinetico database and we also need to specify the specific SQL code that we want the task to execute. We can do both of those things in the editor window for this task. Double click on the task icon to bring up the editor. Here's where you'll configure the various properties that control exactly what the task does. On the General page we're going to find the connection properties. For connection type there's several different options using the dropdown menu. We can connect to Excel files or a SQL mobile database, but you won't find anything that says SQL server. That's because the connection to SQL server databases are done through something called Object Linking and Embedding, or OLE DB. Let's go ahead and leave that one selected. Then we'll come down to the next property for the actual connection. I'll click here then click the dropdown menu and choose New connection. On this screen, press the New button again to open the Connection Manager. Using the server name dropdown menu choose the server instance that you want to connect to. In my case, the Kinetico database is on a server named SQLSERVER2019. It might take this dropdown menu a moment to populate, so just be patient while it updates. If this dropdown menu doesn't populate with server names to select you can also type the name here into this box. If you're connecting to the default instance you just need the name of the computer. Otherwise, use the computer name, back slash, instance name. Once you've selected the server you'll specify the login credentials here. Now I'm just going to use the standard Windows authentication for my user account. Next, we'll specify the actual database name. Using the dropdown menu it shows me all the different databases that are on this server and I'll choose Kinetico from the dropdown list. Then I'll press the Test Connection button to make sure everything's working, and it looks good. I'll say OK, and say OK again. And OK one more time to finish the connection. Next, we need to specify what code this task is going to execute. For these SQL statement property I'll click here and then click on the ellipsis button to bring up a small text window. Here's where you're going to type in the transact SQL command that you want to execute. I have one copied onto my clipboard that you can find in the exercise files, so I'm going to paste that in. This script is going to remove the People table if it already exists, then create a new table called People and add a couple of records. Press OK and then press OK again to finish configuring the task. Notice that the red X icon is now gone and this task is ready to run. Let's add one more task to the diagram. Back in the toolbox I'm going to scroll down until I get to the other task category and I'm going to double-click on Backup Database. That'll add in another task to the control flow diagram. It comes in on top of the Execute SQL Task, so you can simply click on it and drag it into a new location in the diagram. Then just like before, we need to configure the task. Double-click on the icon to open it up in the editor. First we need to connect to the server. So over here I'll press the New button on the Connection line. We'll give the connection a name, I'll call it Kinetico. Then I'll click on the ellipsis button to choose the actual server. It might take a moment for this window to open up, but once it does select the same server that we had before and press OK. And I'm going to use Windows NT Integration Security, which is basically just Windows authentication. I'll say OK and that creates my connection to the server. Then in the databases drop-down we get to choose which database on the server that we want to back up. You can choose all of them if you'd like, but I just want to target the Kinetico database. So I'll come down here and place a check mark there and press OK. You can then view a sample of the T-SQL code that'll be executed to actually run the the backup by clicking on this View T-SQL button and you'll see that it's listed out right there. Let's press Close and then we can say OK to finished configuring the task. At this point, we have a package that contains two tasks. One will execute some SQL code and the other one will back up the database. This is a good point to save the project and you can do that by pressing the disc icon up here on the toolbar or simply using the Ctrl + S shortcut key. When you do that, you'll notice that the little asterisk icon here disappears from the tab. This indicates that this package is currently in a saved state. The next thing that we need to do is define the sequence that these tasks will run in, and we'll do that with a precedence constraint next.