Overview
Now that we’ve created our SSIS project in the previous chapter, it’s time to start exploring the control flow and its abilities. The control flow allows you to execute different tasks and organize a workflow between the tasks. In this section, we’ll give an overview of the objects you can add to the control flow.
Explanation
SSIS Tasks
In an SSIS package, you can add tasks to the control flow. A task is a unit of work, and you have different kinds of tasks to perform different kinds of work. Explaining all tasks would take us too far in this tutorial, so here’s an overview of some of the most common tasks:
- Execute SQL Task: These tasks will execute a SQL statement against a relational database.
- Data Flow Task: These special tasks can read data from one or more sources, transform the data while in memory, and write it out against one or more destinations. We’ll describe the data flow in more detail in the next sections of the tutorial.
- Analysis Services Processing Task: You can use this task to process objects of an SSAS cube or Tabular model.
- Execute Package Task: With this task, you can execute other packages from within the same project. You can also pass variable values to the called package.
- Execute Process Task: Allows you to call an executable (.exe). You can specify command line parameters. With this task, you can, for example, unzip files, execute batch scripts, and so on.
- File System Task: This task can perform manipulations in the file system, such as moving, renaming, and deleting files, creating directories, etc.
- FTP Tasks: Allows you to perform basic FTP functionalities. However, this task is limited because it doesn’t support FTPS or SFTP.
- Script Task: This task is essentially a blank canvas. You can write .NET code (C# or VB) that performs any task you want.
- Send Mail Task: Here you can send an email. Ideal for notifying users that your package has done running or that something went wrong.
In the screenshot, you can see an Execute SQL Task that has been added to the control flow:
There are of course more tasks. Some are for working with Azure or big data systems, others are for performing DBA tasks (and are essentially the building blocks of SQL Server Maintenance Plans). You can find an overview in the documentation.
SSIS Containers
Next to tasks, you also have containers. These give you more power over how tasks are executed. You can add one or more tasks to a single container.
- For Loop Container: With this container, you can execute all tasks inside for a fixed number of executions. This is equivalent to for loops in a programming language.
- For each Loop Container: This container doesn’t execute a fixed number of times like the for loop, but the number of executions is determined by a collection. This can be for example the number of files in a directory or the number of rows in a table. This makes the container more flexible than a for loop container.
- Sequence Container: This container simply groups tasks together. The tasks will execute together. This container is useful to split your control flow into logical units of work.
Here you can see a couple of tasks inside a sequence container. When you would execute the sequence container, all three tasks will execute at the same time.
Additional Information
- For more related tips check out the Integration Services Tips on this page.
