Types of Control Flow Tasks in SSIS

  • (4.0)

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

FREE Demo Class From Industry Experts - Enroll Now.!! MSBI training

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.

Take for each loop container → rt click → edit


                 Enumerator: For each file enumerator.
                  Folder: c:outputGroup
                  File: “.TXT”
                  Retrieve file name: select fully qualified select transverse folders.
                  Variable –Mappings:
                                             Variable drop down → select new variable →
                                              Name: group var → ok.
Take data flow task in the for each loop container.

Frequently Asked MSBI Interview Questions & Answers

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.
Take execute SQL task on control flow.
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”

Explore MSBI Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

Popular Courses in 2018

Get Updates on Tech posts, Interview & Certification questions and training schedules