SSIS: Everything you need to know

SSIS: What is it?

SSIS stands for SQL Server Integration Services. Microsoft SSIS is a part of Microsoft SQL Server database software that allows data to be migrated easily and quickly. It is a smooth and handy data warehousing tool. SSIS can be used for things like:

  • Aggregating data
  • Extracting data
  • Loading data
  • Transforming data
  • Cleaning data
  • Merging data

Extracting data can be done from many different sources namely SQL Server databases, Excel files, Oracle databases, DB2 databases, Visual Studio, etc.

SSIS: Architecture

The architecture of SSIS consists of four different parts, namely:

  • Object model
  • Duration
  • Data flow
  • Maintenance


Object model

This is the overarching structure of “Integration Services”. SSIS is the latest version of this. The object model has been rewritten and different APIs (Application Programming Interface) have been applied.

Duration

This engine component manages transactions and package workflow. It is possible to run components such as containers, tasks and events.

Data flow

This engine reads and manages the movement and transformation of data. Programmers can program this engine to automate the configuration of components. Custom components can also be made.

Maintenance

Packages are stored and monitored in the SSIS Package Store. Maintenance is not necessary for the functionality of SSIS, but it is necessary to list and check packages.

SSIS Packages

An SSIS Package is an Integration Service Project with which a task can be performed. These are tasks such as loading data, moving data or backing up a database. SSIS Packages can be easily scheduled. The tasks can be performed regularly, but also once if necessary.

The most important parts of the SSIS Packages are the control flow and data flow. The control flow configures all tasks. This provides insight into which tasks are linked with priority restrictions. If tasks are not connected, they will run in parallel when the Package is executed. The data stream is in the SSIS control stream.

ETL Tools: Is SSIS a good tool?

ETL stands for Extract, Transform and Load. These are the three steps of integrating data. SISS can extract, transform and load data well, making SSIS a good ETL tool. SSIS also has graphical tools and wizards for workflow functions such as sending mails, FTP operations, data sources and destinations.

SSIS: Visual Studio

SSIS Projects is installed as an extension of Visual Studio. It is available for VS 2008, VS 2010, VS 2012, VS 2015, VS 2017 and VS 2019. The designation varies by version. At VS 2008, SSIS is called “Business Intelligence Development Studio”. In VS 2010 it is called SQL Server Data Tools. At 2012 it is called SSDT-BI, or SQL Server Data Tools – Business Intelligence. With VS 2015 and VS 2017 it is called SSDT. Since VS 2019 it is therefore called SSIS. SSIS is not yet available for Visual Studio 2022. Microsoft is planning to make SISS available for Visual Studio, but the concrete date of this is still unknown.

Read everything you need to know about Visual Studio here.

Do you have questions? Contact us!

For more information about which Microsoft software and licenses are best for you, please contact us. We are available from Monday to Friday from 9 a.m. to 5 p.m.