SSIS Control Flow

Control Flow Tasks in SSIS

It contains container and control flow tasks in ssis

Container

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.

 Navigation:
Take for each loop container → rt click → edit

Enhance your skillset and give a boost to your career with the SSIS Certification Training Course.

Collection:

 

 

                 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.

Control Flow Tasks in SSIS

#1) 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.

Okàok.

Take OLEDB destination and connect.

#2) 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]
 
Navigation:

File system task  → rt click → edit →

Destination:

Is destination path variable: false

Destination connection: Browse to success folder

Over write destination: false.

Operation: move file

Source:

Is source path variable: false

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

#3) 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.

#4) 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.

Navigation:

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.

#5) Working with procedure

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

Values (@PID,@PName);

END

EXEC SAMP 10, KKK

Select *from for each-tab;

Drop procedure samp.

#6) 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.

#7) 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

Format:

Row delimiter: {CR}-{LF}

Column delimiter: ,(comma)

Source connection:

File: browse the file

#8) 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.

#9) 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

Navigation:

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.

#10) 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”

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator