Now that we’ve created our SSIS project in the previous chapter, it’s
time to start to explore 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
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 relation 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 files, renaming files, deleting files,
and creating directories et cetera.
- 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
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
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.
Last Update: 8/31/2017