SSIS Tutorial for Beginners: What is, Architecture, Packages

Details

What is SSIS?

SQL Server Integration Service (SSIS) is a component of the Microsoft SQL Server database software that can be used to execute a wide range of data migration tasks. SSIS is a fast & flexible data warehousing tool used for data extraction, loading and transformation like cleaning, aggregating, merging data, etc.

It makes it easy to move data from one database to another database. SSIS can extract data from a wide variety of sources like SQL Server databases, Excel files, Oracle and DB2 databases, etc.

SSIS also includes graphical tools & wizards for performing workflow functions like sending email messages, FTP operations, data sources, and destinations.

In this SSIS tutorial for Beginners, you will learn:

Why we use SSIS?

Here, are key reasons for using SSIS tool:

History of SIS

Prior, to SSIS, SQL Server, Data Transformation Services (DTS) was used, which was part of SQL Server 7 and 2000

Version Detail
SQL Server 2005 The Microsoft team decided to revamp DTS. However, instead of update DTS, they decided to name the product Integration Services (SSIS).
2008 SQL server version Plenty of performance improvements were made to SSIS. New sources were also introduced.
SQL Server 2012 It was the biggest release for SSIS. With this version, the concept of the project deployment model introduced. It allows entire projects, and their packages are deployed to a server, in place of specific packages.
SQL Server 2014 In this version, not many changes are made for SSIS. But new sources or transformations were added which was done by separate downloads through CodePlex or the SQL Server Feature Pack.
In SQL Server 2016 The version allows you to deploy entire projects, instead, of individual packages. There are additional sources especially cloud, and big data sources and few changes were made to the catalog.

SSIS Salient Features

Here, are some important SSIS basics features:

SSIS Architecture

SSIS Architecture

Following are components of SSIS architecture:

Let's understand each component in detail:

1. Control Flow

Control flow is a brain of SSIS package. It helps you to arranges the order of execution for all its components. The components contain containers and tasks which are managed by precedence constraints.

2. Precedence Constraints

Precedence constrain are package component which direct tasks to execute in a predefined order. It also defines the workflow of the entire SSIS package. It controls the execution of the two linked tasks by executing the destination tasks based on the result of the earlier task — business rules which are defined using special expressions.

3. Task

A 'Task' is an individual unit of work. It is the same as a method/function used in a programming language. However, in SSIS, you don't use coding methods. Instead, you will use drag & drop technique to design surface and to configure them.

4. Containers

The container is units for grouping tasks together into units of work. Apart from offering visual consistency, it also allows you to declare variables and event handlers which should be in the scope of that specific container.

Four types of containers in SSIS are:

Sequence Container: allows you to organize subsidiary tasks by grouping them, and allows you to you apply transactions or assign logging to the container.

For loop container:Provides the same functionality as the sequence Container except that it also lets you run the tasks multiple times. However, it is based on an evaluation condition, like a looping from 1 to 100.

For each Loop Container: It also allows looping. But the difference that instead of using a condition expression, loop s done over a set of objects, likes files in a folder.

5. Data Flow

The main use of the SSIS tool is to extract data into the server's memory, transform it, and write it to another destination. If Control Flow is the brain, Data Flow is the heart of SSIS

6. SSIS Packages

Another core component of SSIS is the notion of a package. It is a collection of tasks which execute in an orderly fashion. Here, president constraints help manage the order in which the task will execute.

A package can help you to saves files onto a SQL Server, in the msdb or package catalog database. It can save as a .dtsx file, which is a structured file very similar to .rdl files are to Reporting Services.

7. Parameters

Parameters behave much like a variable but with a few main exceptions. It can be set outside the package easily. It can be designated as values that must be passed in for the package to start.

SSIS Tasks Types

In SSIS tool, you can add a task to control flow. There are different types of tasks which perform various kinds of works.

Some important SSIS tasks are mentioned below:

Task Name Descriptions
Execute SQL Task As its name suggests, it will execute a SQL statement against a relational database.
Data Flow Task This task can read data from one or more sources. Transform the data when it is in the memory and write it out against one or more destinations.
Analysis Services Processing Task Use this task to process objects of a Tabular model or as an SSAS cube.
Execute Package Task Use can use this SSIS task to execute other packages from within the same project.
Execute Process Task With the help of this task, you can specify command line parameters.
File System Task It performs manipulations in the file system. Like moving, renaming, deleting files, and creating directories.
FTP Tasks It allows you to perform basic FTP functionalities.
Script Task This is a blank task. You can write NET code which performs any task; you want to perform.
Send Mail Task You can send an email to notifying users that your package has is finished, or some error occurs.
Bulk Insert Task Use can loads data into a table by using the bulk insert command.
Script Task Runs a set of VB.NET or C# coding inside a Visual Studio environment.
Web Service Task It executes a method on a web service.
WMI Event Watcher Task This task allows the SSIS package to wait for and respond to certain WMI events.
XML Task This task helps you to merge, split, or reformat any XML file.

Other Important ETL tools

Advantages and Disadvantages of using SSIS

SSIS tool the offers the following advantages:

Disadvantages of SSIS

Few drawbacks of using SIS tools are as follows:

SSIS Best Practices Example

Summary

 

YOU MIGHT LIKE: