Unit-3

Creating ETL Solutions with

SSIS Implementing Control Flow in SSIS

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Introduction to ETL
• ETL stands for Extraction, Transformation and Loading. It is a process in data
warehousing to extract data, transform data and load data to final source. ETL covers
a process of how the data are loaded from the source system to the data warehouse. Let
us briefly describe each step of the ETL process.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 What is an ETL process ?
• ETL stands for Extraction, Transformation and Loading. It is a process in data
warehousing to extract data, transform data and load data to final source. ETL covers a
process of how the data are loaded from the source system to the data warehouse. Let
us briefly describe each step of the ETL process.
Extraction
• Extraction is the first step of ETL process where data from different sources like txt
file, XML file, Excel file or various sources collected.
Transformation
• Transformation is the second step of ETL process where all collected data is been
transformed into same format i.e. format can be anything as per our requirement before
loading it to data-warehouse i.e. it may be data-type format, data merge format,
splitting format, alphabet joining format, currency format etc.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Loading
• Final step of ETL process, The big chunck of data which is collected from various sources and
transformed then finally load to our data warehouse.
• To do ETL process in data-ware house we will be using Microsoft SSIS tool.
 ETL process with SSIS Step by Step using example
• We do this example by keeping baskin robbins (India) company in mind i.e. customer data which
is maintained by small outlet in an excel file and finally sending that excel file to USA (main
branch) as total sales per month. This data is necessary at head quarters (main branch) to track
performance of each outlet.
• So here also we will do same thing i.e. We will collect customer product purchase sales data from
small-small outlet (In an Excel Format) - Extraction
• Since baskin robbins is located in USA we need to convert or transform product purchase
amount to USD currency and we will also convert product name to uppercase for unique
representation - Transformation
• Finally loading this transofrmed data to database / datawarehouse (SQL Server Database) -
Loading
Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• Step 1 :- Extract Data From Excel File
• Before you read this steps kindly make sure you have installed microsoft business
intelligence along with SQL Server.
• step we will create a simple excel file with a columns names as CustomerCode,
CustomerName, ProductPurchase, Quantity, Amount, CustomerVisitedDate
respectively.
• Add some data as shown in below image.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• Give a nice name and save it your computer.
• Now open your SQL Server data tools if you don't have SQL server data tools installed
then install it first.
• if you already MSBI data tool just open it and go to FILE -> NEW -> PROJECT.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• Since we are doing ETL process and that process comes under SSIS so we need to
create Integration Services so choose that -> Integration Service Project.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• Give a nice name and create a project.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• Now go to SSIS Toolbox and drag and drop "Data Flow Task" to control panel as show
in below image.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• Just double click on "Data Flow Task" to take you to "Data Flow".
• Now go to SSIS Toolbox and from Other Sources tab just drag and drop Excel Sources. Why
excel source because our initial data which we want to extract it is in excel format.
• So just drag excel file and right click and rename it so that if any developer reads it can easily
able to understand.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• Now right click on that excel source -> Edit -> Click on New button - Browse Excel file
from your computer as shown in below image.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• Since our first column of excel file is having column names so we need to check this
below check box as you see in above image.
• Now select Data access mode as "Table on view" then select Excel sheet name from drop
down.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• If you want to preview you can also do that by clicking preview button.
• Finally click on OK button. So now your excel source is [Link] means we have
successfully extracted our excel data file to SSIS excel data source.

Step 2 : Transform Data (Convert to US currency and Upper case)

• As you now in our excel file we have column name called "Amount" and that amount is
in Indian currency. So we need to convert that Indian amount to USD amount so to do
that we will drag and drop "Derived Column" from SSIS toolbox.
• Now if you see on Excel Source file box there are two arrows "Red" and Blue". Just drag
that "Blue" arrow and join it to "Derived column" as shown in below image and rename
that "Derived column".

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• Now right click on "Derived column" i.e ConvertingAmounttoUSD and click on Edit as
show below image.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• Now in the below Derived Column Name give a new column name "USDAmount" and
Derived column select "add a new column" and in the Expression give the Formula i.e.
"Excel Column (Amount / 60)" and Data-type select as double precision float.
• Why "Amount / 60" this expression is it because we want to current Indian Amount to
USD currency so that's why we a added a new column "USDAmount" and added
calculated values to this new column.
• Finally save it.
• We have now USD amount, next step we need stand representation of product name i.e.
in UPPER CASE so for that we will add another "Derived Column" same way. Now
here we need to drag "Blue" arrow from "ConvertingAmounttoUSD" to new derived
column ("CapitalProductName") as shown below image.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• Same way right click on "CapitalProductName" go to EDIT choose column
"ProductPurchase" from columns section and drag it to "Derive Column Name. In the
Derived Column select "Replace 'ProductPurchase'.
• Expression you can either choose from string function of you can type it i.e. UPPER
(Column name). finally click on OK button.
• So as you can see we have completed data transformation part i.e. Convert Amount to
USD and Changed Product Names to UPPER case.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• Step 3 : Loading Data To SQL Server
• Before you start this step just open up your SQL server management studio and create
a new database if need or just a new table with same excel column names as shown
below image

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• Now go to your SSDT and from SSIS toolbox under Other Destination select "[Link]
Destination"and drag it to "Data Flow" and drag arrow from "CapitalProductName" to
"[Link] Destination".

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• Right click and click on edit add your server name if you do not know your extract
server name go to sql server management studio -> File -> click on connect and copy
that server name.
• Now come back to SSDT and click New button -> Again New -> give here server name.
Once you give server name automatically database dropdown will populate.
• Select database and click on OK button. Finally choose table as shown in below image
and save it (OK button).

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• So what we did here it that we have created a path from where excel data flow from
excel file -> Transform USD & UPPER CASE -> SQL Server Data Warehouse.
• Final step just go to DEBUG and click on Start button from top menu of SSDT or just
click on F5. Automatically data will flow from Excel Source -> SQL Server.
• This is how ETL process is executed using SSIS
Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Introduction to SQL Server Data tools
• SQL Server Data Tools (SSDT) transforms database development by introducing a
global, declarative model that spans all the phases of database development inside
Visual Studio. You can use SSDT Transact-SQL design capabilities to build, debug,
maintain, and refactor databases. You can work with a database project, or directly
with a connected database instance on or off-premise.
• Developers can use familiar Visual Studio tools for database development. Tools such
as: code navigation, IntelliSense, language support that parallels what is available for
C# and Visual Basic, platform-specific validation, debugging, and declarative editing in
the Transact-SQL editor.
• SSDT also provides a visual Table Designer for creating and editing tables in either
database projects or connected database instances. While you are working on your
database projects in a team-based environment, you can use version control for all the
files.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Introduction to SQL Server Data tools
• When it's time to publish your project, you can publish to all supported SQL platforms;
including SQL Database and SQL Server. SSDT platform validation capability ensures
that your scripts work on the target you specify.
• The SQL Server Object Explorer in Visual Studio offers a view of your database objects
similar to SQL Server Management Studio. SQL Server Object Explorer allows you to
do light-duty database administration and design work. You can easily create, edit,
rename and delete tables, stored procedures, types, and functions. You can also edit
table data, compare schemas, or execute queries by using contextual menus right from
the SQL Server Object Explorer.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Implementing data flow using SSIS
• The data flow is a special task of the control flow. It needs a canvas of its own, so
there’s an extra tab for the data flow, right next to the control flow.
• Adding a Data Flow task to the control flow of a package is the first step in
implementing a data flow in a package. A package can include multiple Data Flow
tasks, each with its own data flow. For example, if a package requires that data flows
be run in a specified sequence, or that other tasks be performed between the data flows,
you must use a separate Data Flow task for each data flow.
• After the control flow includes a Data Flow task, you can begin to build the data flow
that a package uses. For more information.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Configuring Dataflow
Creating a data flow includes the following steps:
• The data flow is a construct where you can read data from various sources into the
memory of the machine that is executing the SSIS package.
• While the data is in memory, you can perform different kinds of transformations.
Because it’s in memory, these are very fast.
• After the transformations, the data is written to one or more destinations (a flat file, an
Excel file, a database, etc.). In most cases, not all data is read into the memory at once -
although this is possible if you use certain kind of transformations – but the data is
read into buffers.
• Once a buffer is filled by the source component, it is passed on to the next
transformation which does it logic on the buffer. Then the buffer is passed to the
following transformation and so on until it is written to the destination. You can
imagine the data flow is like a pipeline, with data flowing through.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
• To create a data flow, you can drag it from the SSIS toolbox to the control flow canvas.
• Another option is to simply go to the data flow tab, where you will be greeted with the
following message:
• Clicking the link will create a new data flow task for you. You end up with an empty
canvas, just like in the Control Flow.
• As you can see in the screenshot above, the SSIS toolbox will change once you go to the
data flow canvas. All the tasks are now replaced with transformations, sources and
destinations for the data flow. At the top, you also have a dropdown box that lets you
easily switch between multiple data flows if you have any.
•

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Data Flow Sources

• SSIS Data Flow Sources and Destinations

• These are some of the most common sources and destinations for the data flow:
• [Link]: With this component you can connect to various sources and destinations
using .NET providers.
• Excel
• Flat File
• ODBC: If you have an ODBC connection defined on your machine, you can use it to
read or write data. Keep in mind Visual Studio is a 32-bit application, so you might
want to have both 32-bit and 64-bit versions of the ODBC connection installed. You can
give them the same name to make the transition smoother.
• OLE DB: With this source you can connect to any database for which an OLE DB
provider is available. To date, the OLE DB source and destination are the fastest option
for SQL Server.
•
Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Data Flow Transformation
• SSIS Data Flow Transformations
• Some of the more common transformations:
• Aggregate: Like a GROUP BY in T-SQL.
• Conditional Split: Splits out streams based on one or more conditions.
• Data Conversion: Allows you to convert columns from one data type to another.
• Derived Column: Allows you to manipulate existing columns or create new columns using
expressions.
• Lookup: Similar to VLOOKUP in Excel. You match a row against a reference data set and
retrieve one or more columns.
• Merge: Merges two streams together. It needs sorted inputs.
• Merge Join: Like a JOIN in T-SQL, it can do inner, left join and full outer joins. It needs sorted
inputs as well.
• Multicast: Duplicates a stream into multiple streams.
• Script Component: With the component you can write your own transformations in .NET.
When you open the editor, you have to choose if the component is a source, destination or a
transformation. Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Derived Column Transformation
• SSIS Derived Column Transformation is one of the SQL Server Integration Services
that can be added within a Data Flow Task, it is used to add a new column to the data
pipeline by applying SSIS expressions.
• The developer can choose whether to add a new derived column or to replace an
existing column.
• The Derived Column transformation creates new column values by applying
expressions to transformation input columns.
• An expression can contain any combination of variables, functions, operators, and
columns from the transformation input.
• The result can be added as a new column or inserted into an existing column as a
replacement value.
• The Derived Column transformation can define multiple derived columns, and any
variable or input columns can appear in multiple expressions.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Conditional Split Transformation
• A Conditional Split Transformation in SSIS is just like the IF condition or CASE
statement. It checks the given condition and based on the condition result; the output
will send to the appropriate destination path. It has ONE input and MANY outputs.
• The Conditional Split transformation can route data rows to different outputs
depending on the content of the data. The implementation of the Conditional Split
transformation is similar to a CASE decision structure in a programming language.
The transformation evaluates expressions, and based on the results, directs the data
row to the specified output. This transformation also provides a default output, so that
if a row matches no expression it is directed to the default output.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Conditional Split Transformation
• Conditional Split is a MSBI component available in SSIS toolbox. This tool is used
during Data Flow task and during ETL process. Conditional Split as name says
"Condition" if you are a programmer then you can easily understand where we are
relating this word "Condition". "Conditional" in programming sector we say
"Conditional Statements" means a simple "IF statement" or a "CASE statement" where
depending on following condition a specific task executes.
• The Conditional Split transformation checks the given condition and route data to
appropriate destination depending on the given condition. For example if a men's age is
greater than 66 then He can go it into the Senior citizen quota or else He will not be
considered as senior citizen. Here the condition was age greater than 66 (age > 66).

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Conditional Split Transformation
• Conditional Split using example step by step
• To understand conditional split in more better way We will take up example of
government employees i.e. From this government employee data we will split
employees who's age is greater than 60 and to give them retirement. Further we split
retired employees as per their grades because to give them retirement amount and to
start their monthly pension.
• Conditional Split 1
• We will split employees in two parts i.e. If employee age is greater than 60 then we will
put them in retired employee list

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Conditional Split Transformation
• Step 1
• we will create a simple excel file with columns like EmpID, EmpName, EmpAge,
EmpSalary, EmpGrade. as shown in below image

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Conditional Split Transformation
• Step 2
• Now we will create a SQL table as our dataware house to store extracted and
filtered data from Excel source.

• Employees age with below 60 will go into employee table and all employees
age above 60 will go into "RetiredEmployee" table.
Prepared By : Paresh Limbad
•
Ruparel Education Pvt. Ltd.
201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Conditional Split Transformation
• Step 3
• Create a new SSIS project -> On the control flow tab just drag and drop "Data Flow
Task" as shown in below image.

• Step 4
• Just rename it with a suitable name and double click on it. Once you double click it will
take you to "Data Flow" tab.
Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Conditional Split Transformation
• Step 5
• Since we have our source file in an excel format so we will drag and drop "Excel Source
from SSIS toolbox as shown in below image.

• Step 6
• Configure our excel file to this "Excel Source“. How to configure that you know very
well.

Prepared By : Paresh Limbad

•
Ruparel Education Pvt. Ltd.
201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Conditional Split Transformation
• Step 7
• Now we will drag and drop "Conditional Split" component from SSIS toolbox.
• Step 8
• Right click -> Click on Edit and these two condition as shown in below image.

•
Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Conditional Split Transformation
• So what we are doing is we are splitting output to two formats on condition of their
respective ages.
• So if an employee age is greater than 60 than we will add it "RetiredEmployee" table
and if an employee age is less than 60 than we will add it to "employee" table.
• Once you have added this conditions just apply it and click on OK button.

Prepared By : Paresh Limbad

•
Ruparel Education Pvt. Ltd.
201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Conditional Split Transformation
• Step 9
• To add split data to database we need "[Link] destination" component so as we
know that we are getting outputs in two formats and that formats we need to
separately add it to two different tables i.e "RetiredEmployee" and "employee" table so
for this we need two "[Link] destination" components.
• Let's add two "[Link] destination" and configure it.
• When you drag an output arrow from "Conditional Split" component to [Link]
component it will prompt box state that on which condition you want split.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Conditional Split Transformation

• Just select condition and configure both "[Link] destination" respectively.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Conditional Split Transformation

• Just select condition and configure both "[Link] destination" respectively.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Conditional Split Transformation
• Step 10
• Finally run the project and check the output in the SQL table.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Destination
• The OLE DB Destination is used in order to load data into a destination supported by
an OLE DB provider such as SQL Server, SQLite, Microsoft Access database and
others. The destination connection configuration must be done within an OLE DB
connection manager and it can be located on a local or remote server.
• OLE DB Destination provides many data access modes to load data into the
destination, each one of these data access modes has its own configuration and
available options:

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Destination
• Table or View: select an existing table or view / create a table
• Table or View – fast load: select an existing table or view / create a table – using fast
load options
• Table name or View name variable: select a variable that contains a table or view
name
• Table name or View name variable – fast load: select a variable that contains a
table or view name – using fast load options
• SQL Command: use the result of a SQL Statement to specify the destination
metadata. This option can be used in two cases:
• If the destination table contains too many columns and you need to select only some
specific one
• The destination is composed of many tables/views

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Destination
• SQL Server Destination
• SQL Server destination is used to load data into a local SQL Server database. It bulk
loads the data into tables or views. This component cannot be used for SQL Server
located on the remote server. Also, it reads the connection configuration from an OLE
DB connection manager.
• There are many options that can be configured in the destination editor such as:
• Keep identity
• Keep Nulls
• Check Constraints
• Table Lock
• Fire triggers: Specify whether to execute the insert triggers defined on the destination
table during the bulk load operation
•
Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Destination
• First Row: Specify the number of the first row in the input to load during the
bulk insert operation
• Last Row: Specify the number of the last row in the input to load during the
bulk insert operation
• Maximum number of errors: Each row that cannot be imported is counted as
one error
• Timeout: Bulk insert query timeout
• Order Columns: specify columns that contain sorted data

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Control Flow
• A package consists of a control flow and, optionally, one or more data flows. SQL Server
Integration Services provides three different types of control flow elements: containers that
provide structures in packages, tasks that provide functionality, and precedence constraints
that connect the executables, containers, and tasks into an ordered control flow.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Control Flow
• The following diagram shows a control flow that has one container and six tasks. Five of the
tasks are defined at the package level, and one task is defined at the container level. The
task is inside a container.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Control Flow
• The Integration Services architecture supports the nesting of containers, and a control flow
can include multiple levels of nested containers. For example, a package could contain a
container such as a Foreach Loop container, which in turn could contain another Foreach
Loop container and so on.
• Event handlers also have control flows, which are built using the same kinds of control flow
elements.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Control Flow
• SSIS Tasks using control flow
• 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 Prepared
variable values to the called package.
By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain Control Flow
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.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
For Loop Container
• The For Loop Container enables you to create looping in your package similar to how
you would loop in nearly any programming language. In this looping style, SSIS
optionally initializes an expression and continues to evaluate it until the expression
evaluates to false.
• In the example in below , the Script Task called Wait for File to Arrive is continuously
looped through until a condition is evaluated as false. Once the loop is broken, the
Script Task is executed.
• For another real-world example, use a Message Queue Task inside the loop to
continuously loop until a message arrives in the queue. Such a configuration would
allow for scaling out your SSIS environment.
• The following simple example demonstrates the functionality of the For Loop
Container, whereby you’ll use the container to loop over a series of tasks five times.
Although this example is basic, you can plug in whatever task you want in place of the
Script Task. Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
• Create a project using for loop container.
• Create a new table ‘city’ in sql server.
2. Now open SQL Server Data tools.
3. Create New project Integration Service project
4. Select Control flow tab
5. Select from SSIS tool box For loop container Drag on Screen.
6. Then after From SSIS tool box select Execute task and put inside the for loop container.
•

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
7. Then after double click and edit the SQL task first and do the following changes in the task
editor.
• Connection  select new connection
• SQL statement  write insert into city values(‘Junagadh’);
• Click ok.
8. Now right click on screen and select variable
9. In variable screen add the following values.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
10. Now Double click on for loop container and edit the following values and click ok.

11. Now move to SQL server the write the query Select * from city and check the query.
12. Now move the Visual SSDT tools and execute the task.
Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container

13. After success full of following task move to SQL server and execute the query.
Select * from city

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
For each loop container
• The Foreach Loop Container is a powerful looping mechanism that enables you to loop
through a collection of objects.
• As you loop through the collection, the container assigns the value from the collection to
a variable, which can later be used by tasks or connections inside or outside the
container.
• You can also map the value to a variable. The types of objects that you will loop through
vary based on the enumerator you set in the editor in the Collection page.
• The SSIS ForEach Loop container will repeat the control flow task for N number of
times, which is similar to the Foreach loop in any programming language. The SQL
Server Integration Services or SSIS provides eight types of enumerators. In this article,
we will show you the steps involved in configuring the SSIS ForEach Loop File
Enumerator.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
• SSIS Foreach File Enumerator:
• This is used to enumerate files present in the specified folder. SSIS ForEach
Loop File Enumerator will also traverse the subfolder present in the specified
folder. For example, if you want to move multiple files from one folder to
another or uploading multiple files to the FTP server using the FTP task, etc.
Before we step into the package creation, let us see the source data.

• We have File System Task Folder inside the Documents drive, and Our task is to
load data from text files to SQL Server table. We have two table which stored
in csv format. Like cust and cust1 in a drive.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
• The enumerator determines the type of objects that we plan to enumerate. (Enumerate
refers to the process of going through a collection of items one-by-one.) The
ForeachLoop container supports the following enumerator types:
• Foreach File Enumerator: Enumerates files in a folder
• Foreach Item Enumerator: Enumerates items in a collection, such as the executables
specified in an Execute Process task.
• Foreach ADO Enumerator: Enumerates rows in a table, such as the rows in an ADO
recordset.
• Foreach [Link] Schema Rowset Enumerator: Enumerates schema information
about a data source.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
• Foreach From Variable Enumerator: Enumerates a list of objects in a variable, such as
an array or [Link] DataTable.
• ForeachNodeList Enumerator: Enumerates the result set of an XML Path Language
(XPath) expression.
• Foreach SMO Enumerator: Enumerates a list of SQL Server Management Objects
(SMO) objects, such as a list of views in a database.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
• Foreach File Enumerator
• You use the Foreach File enumerator to enumerate files in a folder. For example, if the
Foreach Loop includes an Execute SQL task, you can use the Foreach File enumerator
to enumerate files that contain SQL statements that the Execute SQL task runs. The
enumerator can be configured to include subfolders.
• The content of the folders and subfolders that the Foreach File enumerator enumerates
might change while the loop is executing because external processes or tasks in the loop
add, rename, or delete files while the loop is executing. These changes may cause a
number of unexpected situations:
• If files are deleted, the actions of one task in the Foreach Loop may affect a different set
of files than the files used by subsequent tasks.
• If files are renamed and an external process automatically adds files to replace the
renamed files, the actions of tasks in the Foreach Loop may affect the same files twice.
• If files are added, it may be difficult to determine for which files the Foreach Loop
affected. Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
• Foreach Item Enumerator
• You use the Foreach Item enumerator to enumerate items in a collection. You define
the items in the collection by specifying columns and column values. The columns in a
row define an item. For example, an item that specifies the executables that an Execute
Process task runs and the working directory that the task uses has two columns, one
that lists the names of executables and one that lists the working directory. The
number of rows determines the number of times that the loop is repeated. If the table
has 10 rows, the loop repeats 10 times.
• To update the properties of the Execute Process task, you map variables to item
columns by using the index of the column. The first column defined in the enumerator
item has the index value 0, the second column 1, and so on. The variable values are
updated with each repeat of the loop.
The Executable and WorkingDirectory properties of the Execute Process task can
then be updated by property expressions that use these variables.
Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
• Foreach ADO Enumerator
• You use the Foreach ADO enumerator to enumerate rows or tables in an ADO or
[Link] object that is stored in a variable. For example, if the Foreach Loop includes
a Script task that writes a dataset to a variable, you can use the Foreach ADO
enumerator to enumerate the rows in the dataset. If the variable contains an [Link]
dataset, the enumerator can be configured to enumerate rows in multiple tables or to
enumerate tables.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
• Foreach [Link] Schema Rowset Enumerator
• You use the Foreach [Link] Schema Rowset enumerator to enumerate a schema for
a specified data source. For example, if the Foreach Loop includes an Execute SQL
task, you can use the Foreach [Link] Schema Rowset enumerator to enumerate
schemas such as the columns in the AdventureWorks database, and the Execute SQL
task to get the schema permissions.
• Connection
Select an [Link] connection manager in the list, or click <New connection...> to
create a new [Link] connection manager

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
• Foreach From Variable Enumerator
• You use the Foreach From Variable enumerator to enumerate the enumerable objects
in the specified variable. For example, if the Foreach Loop includes an Execute SQL
task that runs a query and stores the result in a variable, you can use the Foreach
From Variable enumerator to enumerate the query results.
• Variable
Select a variable in the list, or click <New variable...> to create a new variable.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
• Foreach NodeList Enumerator
• You use the Foreach Nodelist enumerator to enumerate the set of XML nodes that
results from applying an XPath expression to an XML file. For example, if the Foreach
Loop includes a Script task, you can use the Foreach NodeList enumerator to pass a
value that meets the XPath expression criteria from the XML file to the Script task.
• The XPath expression that applies to the XML file is the outer XPath operation, stored
in the OuterXPathString property. If the XPath enumeration type is set
to ElementCollection, the Foreach NodeList enumerator can apply an inner XPath
expression, stored in the InnerXPathString property, to a collection of element.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
• Foreach SMO Enumerator
• You use the Foreach SMO enumerator to enumerate SQL Server Management Object
(SMO) objects. For example, if the Foreach Loop includes an Execute SQL task, you can
use the Foreach SMO enumerator to enumerate the tables in
the AdventureWorks database and run queries that count the number of rows in each
table.
• Connection
Select an existing [Link] connection manager, or click <New connection...> to
create a new connection manager.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
• Sequence Containers
• The Sequence container defines a control flow that is a subset of the package control
flow. Sequence containers group the package into multiple separate control flows, each
containing one or more tasks and containers that run within the overall package
control flow.
• The Sequence container can include multiple tasks in addition to other containers.
Adding tasks and containers to a Sequence container is similar to adding them to a
package, except you drag the tasks and containers to the Sequence container instead of
to the package container. If the Sequence container includes more than one task or
container, you can connect them using precedence constraints just as you do in a
package.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
• There are many benefits of using a Sequence container:
• Disabling groups of tasks to focus package debugging on one subset of the package
control flow.
• Managing properties on multiple tasks in one location by setting properties on a
Sequence container instead of on the individual tasks.
• For example, you can set the Disable property of the Sequence container to True to
disable all the tasks and containers in the Sequence container.
• Providing scope for variables that a group of related tasks and containers use.
• Grouping many tasks so you can more easily managed them by collapsing and
expanding the Sequence container.
• You can also create task groups, which expand and collapse using the Group box.
However, the Group box is a design-time feature that has no properties or run-time
behavior.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Explain SSIS Container
• Set a transaction attribute on the Sequence container to define a transaction for a
subset of the package control flow. In this way, you can manage transactions at a more
granular level.
• For example, if a Sequence container includes two related tasks, one task that deletes
data in a table and another task that inserts data into a table, you can configure a
transaction to ensure that the delete action is rolled back if the insert action fails.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Create Dynamic Package Using Container
• Explain to create Packages Dynamically
• If you need to create packages dynamically, or to manage and execute Integration
Services packages outside the development environment, you can manipulate packages
programmatically. In this approach, you have a continuous range of options:
• Load and execute an existing package without modification.
• Load an existing package, reconfigure it (for example, for a different data source), and
execute it.
• Create a new package, add and configure components object by object and property by
property, save it, and execute it.
• You can use the Integration Services object model to write code that creates, configures,
and executes packages in any managed programming language. For example, you may
want to create metadata-driven packages that configure their connections or their data
sources, transformations, and destinations based on the selected data source and its
tables and columns. Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Create Dynamic Package Using Container
• In SQL Server Data Tools (SSDT), you can create a new package by using one of the
following methods:
• Use the package template that Integration Services includes.
• Use a custom template
• To use custom packages as templates for creating new packages, you simply copy them
to the DataTransformationItems folder. By default, this folder is in C:\Program
Files\Microsoft Visual Studio
10.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject.
• Copy an existing package.
• If existing packages include functionality that you want to reuse, you can build the
control flow and data flows in the new package more quickly by copying and pasting
objects from other packages.

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Create Dynamic Package Using Container
• If you create a new package by copying an existing package or by using a custom
package as a template, the name and the existing package are copied as well. You
should update the name and the new package to help differentiate it from the package
from which it was copied. For example, if packages have the same GUID,(Globally Uniq
identifier) it is more difficult to identify the package to which log data belongs. You can
regenerate the ID property and update the value of the Name property by using the
Properties window in SQL Server Data Tools (SSDT).

Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,
 Create Dynamic Package Using Container
• Use a custom package that you have designated as a template.
• The packages that you create in SQL Server Data Tools (SSDT) using SSIS Designer
are saved to the file system. To save a package to SQL Server or to the package store,
you need to save a copy of the package.
• Create a package in SQL Server Data Tools using the Package Template
• In SQL Server Data Tools (SSDT), open the Integration Services project in which you
want to create a package.
• In Solution Explorer, right-click the SSIS Packages folder, and then click New SSIS
Package.
• Optionally, add control flow, data flow tasks, and event handlers to the package. For
more information, see Control Flow, Data Flow, and Integration Services (SSIS) Event
Handlers.
• On the File menu, click Save Selected Items to save the new package.
• Note You can save an empty package.
Prepared By : Paresh Limbad

Ruparel Education Pvt. Ltd.

201, Punit shopping Center, [Link], Junagadh. M. 76000 440 51,