Types of Control Flow Tasks in SSIS

Recommended by 0 users

Control flow Items

It contains container and control flow tasks in ssis


It contains other tasks

Eg: for each loop container

      For loop container

      Sequence container.


There are normal (and) as well as maintenance tasks.

For loop container:

It executes the underlying task to the specified no.of times. [iterative count we know here]

It has 3 sections.

  1. Initial section: The variables are initialized in the container.
  2. Assignment section: The variable incremented or decremented.
  3. Evaluating section: Here the condition verified.

→ For loop requires a “variable” to do the operations.


Create variable:

                                SSIS menu → variable → add variable →

                                                Name: counter

                                                Data type: integer

Take for loop container on control flow → Rc → Edit

                                                INITIAL Expression:@ counter=0

                                                Eval.Expression:@ counter<5

                                                Assign Expression:@ counter=@counter+1

Take data flow

Take flat file source (to be copied) and OLEDB destination connect it.

Note: the above eg in for loading a file data 3 times to table

For each loop container:

  • It is designed to load group of similar objects or working with similar objects whose count is “unknown”.
  • For loop is having a condition, so that we know the count; whereas for each loop we don’t know the count. Eg: loading set of records to dataset or similar data set records one by one.
  • Loading similar files from a folder to a table etc.
  • It uses enumerator for its operation.
  • The enumerates that support are

                  For each file enumerator

                  For each item enumerator

                  For each ADO enumerator

                  For each ADO.Net schema Row set enumerator

                  For each from variable enumerator

                  For each Node list enumerator

                  For each SMO enumerator

  • Enumerator values are “not changed” within a package (variable values are changed) Eg: loading the available files in the same structure from a folder to a table.



  1. Take for each loop container → rt click → edit


                  Enumerator: For each file enumerator.

                  Folder: c:\output\Group

                  File: “.TXT”

                  Retrieve file name: select fully qualified select transverse folders.

                  Variable –Mappings:

                                                  Variable drop down → select new variable →

                                                  Name: group var → ok.

  1. Take data flow task in the for each loop container.

Control Flow Tasks in SSIS

Data flow task

Take flat file source → specify one file in the group

Go to source connection manager → rt click → properties → expression → click ellipse

Property drop down list: select connection string.

Expression: click ellipse

Variable: drag and drop group var to the expression section.


Take OLEDB destination and connect.

File system task

It performs file and folder options such as coping, moving, deleting, creating…etc.

Eg:   1.moving the files from folder ‘x’ to folder ‘y’

         2. in above for each loop container example load successive files into another folder [i.e., which are not loaded that can be easily tracked]



                File system task  → rt click → edit →


                Is destination path variable: false

                Destination connection: Browse to success folder

                Over write destination: false.

                Operation: move file


                  Is source path variable: false

                  Source connection: browse to source connection manager (which was taken at data flow task source level)

Execute package task

                It executes the packages which are available in “file system” and “SQL server database”.

This is designed to execute another package with in main package. We can control the flow between these packages.


  1. Take execute package task on control flow
  2. Rt click → edit →

Location: file system

Connection: specify desktop any folder package

Password: if password is there, specify

Execute out of process: false

Note: execute out ok of process ‘true’ means, each package runs with a separate process.

Execute SQL Task

It execute SQL of any database (oracle, teradata, excel, SQL server….etc).

To connect to the corresponding database we must specify the corresponding type.

It executes queries, commands in the corresponding database.

Note:  the execute out of process option

v  If we turn into true the sub package runs separately from the main package process.


  1. Take execute SQL task on control flow.
  2. Rt click → edit

Connection type: OLEDB

Connection: local host: DB-MSBI

SQL statement: delete from party;

By pass prepare: true

If select “false” instead of “true”, in this situation SQL converts into the another query and it run server time on the target data base.

Working with procedure

                Create procedure [dbo].[samp] @ PID integer, @PName VARCHAR (30) AS BEGIN Insert into for each-tab (partyID, PartyName)

Values (@PID,@PName);



Select *from for each-tab;

Drop procedure samp.

Executing the procedure from executing SQL Task

→ SQL statement: EXEC SAMP 20, triven

Script task:

                It supports the scripting of VB.Net or C#.Net


                                                                                    Execute SQL task

Rt click → edit

              Script language: Microsoft visual basic 2008.

              Click edit script → add the below statement in the main ()

                                                                                    MsgBox (“MSBI class”)

                                                                                    Save → ok.

Real time:

  1. To reuse the existing code of OLTP systems coding.
  2. To write custom coding

Eg: loading multiple work sheet data in a single excel sheet to a table.

Bulk insert task

  • It loads bulk data with max.speed into the tables.
  • It cannot perform any intermediate operations table should already create.
  • Before loading into the table
  • It loads file only [direct file data to already created table]

Bulk inserts task navigation:

           Rt click → edit →

Destination connection:

                    Connection: local host: DB-MSBI

                     Table: party


                      Row delimiter: {CR}-{LF}

                      Column delimiter: , (comma)

                      Source connection:

                       File: browse the file

Back up database Task

      It takes backup of SQL server databases

Backup → rt click → edit → back up data base task →

            Name: SRC

            Backup: full

            Data bases: DB-MSBI

            Backup to *Disk: tape

Create a back file for every database → click ok.

Send mail Task

     This is designed to send e-mails to corresponding recipients.

It requires an SMTP server. (Simple mail transfer protocol)

send mail task → Rc → edit


send mail task → Rc → edit → Mail → SMTP connection → New → specify name → SMTP server (IP address) → click ok.

  • From (which user) → to (which user) → subject (job successfully finished)
  • Message source type: direct input → priority: high → click ok.

Active script task: It parses and executes active scripts.

Analysis services execute DDL Task: It executes DDL operation of analysis services.

Analysis service processing task: This is used to process the data of facts cube, dimensions in analysis services we use this task.

Execute DTD 2000 package task: It executes win 32 executable tasks.

FTP Task

It perform file operation such as sending, receiving files.

Transfer databases/error messages/jobs/login/master stored procedure SQL server object task.


→ The above tasks transfers the specified objects from “one SQL server instance” to “other SQL server instance”

All the above topics will be covered under MindMajix MSBI Training

0 Responses on Types of Control Flow Tasks in SSIS"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.