Microsoft SQL Server Integration Services

Microsoft is the world’s largest software company. Founded in 1975 and headquartered in Redmond, it has become a household name, primarily due to its Windows operating system and Office suite. Aside from these products, Microsoft has a vast range of enterprise software and cloud offerings including its own database, browser, various servers and ERP solutions.

Microsoft SQL Server Integration Services (SSIS) is the SQL Server component to extract data from data sources, transform it and load it mainly into the SQL Server. The SSIS can be installed as an independent engine. In this case the SSIS are installed on a dedicated server with a MS SQL Server instance that is only used to handle the SSIS packages. The SSIS can also be implemented to run in parallel with the SQL Server. The SSIS are deeply integrated into the SQL Server product family as well as with some Azure Cloud Services (e.g. Blob Storage, HDInsight, Data Lake Store, SQL Data Warehouse, Data Lake Storage Gen2).

In recent years, the integration functions have been expanded to data quality and master data management with additional products. The graphical development interface (SQL Server Data Tools) supports the developer with predefined connectors (to MS SSAS, Azure products like Azure Data Lake, HDInsight or simply Excel), and various functions for loading and transforming data. Development support functions such as testing, and error analysis also support this. The SQL Server Management Studio helps with the management and flow of packages in the production environment.

Microsoft SSIS offers a good cross-section of technical functions for data integration. More than other BI vendors, Microsoft relies on its partners to develop complementary technologies (e.g., data governance solutions) as well as sell and implement its solutions. We see different customers of all company sizes using Microsoft products. Many smaller and mid-sized companies rely fully on Microsoft products to cover all their BI needs.

 

User & Use Cases

The survey confirms the use of SSIS primarily as a tool for the management of data warehouse systems. 69 percent of surveyed users say they use SSIS to automate loading routines. This could be due to the close integration with the SQL Server and other related services such as Analysis Services, Reporting Services and Data Quality Services. This enables end-to-end integration routes to be designed and planned by a single tool/environment. In this way, automatisms can be created. The otherwise typical functions for DW automation that specialists use, such as functions for data modelling, are not known to us within the SQL Server portfolio. Accordingly, the comparatively high numbers for data quality management (35%), data modelling (31%) and master data management (27%) are questionable. We think that SSIS is most likely operated in the context of additional Microsoft services or tools. Microsoft SQL Server Integration Services is an expert tool that primarily supports technical developers in the design and operation of data processing. This requires a high level of expertise that typically only few employees have.

It is worth mentioning that 59 percent of SSIS users come from medium-sized companies. This also corresponds to our perception of Microsoft SQL Server as a tool with a good price-performance ratio for classic data warehouse tasks, which we often encounter in this environment. For scenarios with high complexity and data volumes, Microsoft places other tools from its own portfolio. Therefore, in our experience, many medium-sized companies are currently rethinking their classic SQL server architecture to prepare themselves for analytics.

Current use

N=49

Percentage of employees using Microsoft SSIS

N=40

Number of users using AnalyticsCreator

N=49

Company size (number of employees)

N=49

Microsoft SQL Server Integration Services

Peer Groups Data warehousing automation products, ETL products, Global vendors (data management)
VendorMicrosoft Corporation
Number of responses49
ProductMicrosoft SQL Server Integration Services
OfficesWorldwide
Employees124,000
Customersn/a
Revenues (2018)$110 billion USD
Websitewww.microsoft.com