What is SSIS?

Microsoft SSIS?or SQL Server Integration Services is a data migration and integration tool that comes with the Microsoft SQL Server database that can be used to extract, integrate, and transform data. SSIS is an Extract, Transform and Load (ETL) solution.

SSIS is an upgrade of Data Transformation Services (DTS), which was an old data transformation solution included with SQL Server. The tool comes with the Microsoft SQL Server database and is used to extract data from relational databases and files so that it can be transformed.

File types supported by SSIS include XML, flat files, and Microsoft Excel. Many enterprises use the software as a data warehousing solution. Some of the tasks you can do with SSIS include:

• Analyzing
• Cleansing
• Loading
• Transformation
• Aggregation
• Merging
• Extraction

SSIS Version History

SSIS may have been around for many years but it’s actually the successor to another program called Data Transformation Services (DTS). DTS was a component of SQL Server 7 and 2000 before Microsoft decided to upgrade the platform into the SSIS platform enterprises use today. The versions of SSIS are as follows:

VersionDescriptionSSIS 2005The first release of SSIS that replaced DTS.SSIS 2008 / 2008 R2Improved the performance of the previous version and introduced new sources.SSIS 2012Introduced the project deployment model, enabling users to deploy entire projects and packages to a server rather than being limited to individual packages.SSIS 2014No changes but added new sources and transformations through CodePlex and the SQL Server Feature Pack.SSIS 2016Updated SSIS so that the user can deploy entire packages and added additional cloud and big data sources.

SSIS is important because it provides the user with a platform to move data from one source to another. The user can extract data from sources like SQL databases, Oracle databases, DB2 databases, and Microsoft Excel files before exporting them to another location. Other data integration and extraction features make the transition more manageable.

One of the reasons why SSIS is so widely used is because of its versatility. The program’s support for a range of databases and its simple graphical user interface (GUI) help new users to complete transformations and data warehousing tasks. Users have complete control to design an ETL process around their own requirements.

Related Post: SQL Server Monitoring Tools

The Main SSIS Component

There are a number of core components that make up the SSIS GUI in the SSIS Designer. These include:

• Control Flowtab – Control flows are parts of a package that provide structure, tasks, and precedence constraints.
• Data Flow tab – Pull and transform data from a variety of sources on the device. Data flow components include sources, transformations, and destinations.
• Event Handlerstab – Create tasks in response to events at run-time.
• Package Explorer tab – View a top-down perspective of package elements.

Control Flow

The control flow is where you control the workflow of tasks inside a package and the order in which those tasks will be executed. You can use this through the Control Flow tab. In SSIS, control flows are part of packages and enable the user to execute different tasks.

Types of tasks you can execute include executeSQL tasks, data flow tasks, execute package tasks, script tasks, send mail tasks, and more (we’ve discussed tasks in further detail below). Control flows also include precedence constraints that connect executables, containers, and tasks together while dictating which order to complete the tasks.

Data Flow

A data flow is essentially an element that allows you to take data from a source, transform it, and place it into another destination. For example, you could use a data flow task to take data from a database and transform it into a Microsoft Excel file. You can manage data flows via the Data Flow tab and add them to the control flow.

There are three components that make up the data flow:

• Sources – Extract data from sources including files, databases, and other locations.
• Transformations – Process the data after it has been extracted from the source.
• Destinations – Write the data in its end destination.

As part of the data flow, you can choose the transformations you want to prepare the data to reach its end location and complete the ETL cycle. Transformations handle tasks like data cleansing, merging, sorting, joining, and distributing?so that you can use the data where you need it.

Event Handlers

Executables create events at run time and you can use the events handler to perform tasks in response. Tasks you can perform include cleaning up temporary data storage, retrieving system information, refreshing data in a table, and sending emails to notify you when there is an error or when a task fails.

Event handlers are similar to packages in the sense that they have tasks and containers. You can create custom event handlers in the Event Handlers tab and then add them to a package.

Package Explorer

In the Package Explorer tab, you can view the hierarchy of elements of a package including connection managers, log providers, executables, precedence constraints, parameters, event handlers, variables, tasks, and containers. The package explorer is important for checking the contents of a package.