Microsoft SSIS

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. In recent years, Microsoft has focused its business on cloud-based solutions such as Azure. AI and machine learning have also become increasingly important in product development.

Compared to the huge business the company does in a range of different markets, its BI revenues are relatively small. Nevertheless, Microsoft is a strong presence in the BI market and its offering is strategic to complement existing solutions and to drive cloud revenues. In the past, the vendor spread its BI capabilities across the Office, SharePoint and SQL Server product lines, providing tools for formatted reporting, analysis and dashboards.

Microsoft’s strategic development in the BI area dates back to 1996 when it purchased OLAP technology from Panorama. In just a few years, Microsoft moved from being a surprising entrant into the OLAP market to become the market share leader. Its main database technologies are bundled in SQL Server, which many companies use today to build a central data warehouse. This package includes data integration (Integration Services, also known as SSIS) as well as multidimensional and relational data management. Reporting Services (SSRS), a solution for formatted reporting, is also included with SQL Server.

Over time, SQL Server for BI and advanced analytics has expanded greatly in terms of performance, data support and functionality. Examples of this include its in-memory processing, the extension of the engine with JSON functions for the processing of unstructured data and the integration of R for advanced analytics. In addition to SQL Server, Microsoft has released other products that focus on the development of highly scalable solutions (e.g., SQL Server Big Data Cluster). These tools use Microsoft SQL Server functions or can be closely integrated with it.

Data and analytics capabilities in the Azure cloud are currently strategic for Microsoft. As a result, its most innovative services are only available in the cloud. Scalable on-premises analytical systems, such as Microsoft’s MPP database Analytics Platform Systems, are either not available anymore or are much more expensive than equivalent cloud services. However, this review only covers Microsoft SQL Server Integration Services.

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

In recent years, its integration functions have been extended to cover data quality and master data management with additional products. The graphical development interface (SQL Server Data Tools) supports the developer with predefined connectors (to SSAS, Azure products such as Azure Data Lake, 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 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

81 percent of survey respondents use SSIS for data warehousing and 78 percent use it for data integration, which is not surprising. Also 53 percent are using SSIS to do data warehouse automation, which can be designed using SSIS functionality to a certain degree. Alternatively, companies can take advantage of dedicated data warehouse automation tools that use SSIS as an engine to perform data integration. Somewhat surprisingly, 47 percent claim to use SSIS for data preparation. This suggests that data preparation is understood more as technical data preparation than as an iterative process that supports business users in preparing data sets for explorative analyses. In fact, SSIS does not support data preparation in the true sense of the term, since there are no interfaces for non-technical users. Interestingly, only 28 percent of users take advantage of additional data quality services and just 22 percent use SSIS for master data management.

The mean number of users per company using SSIS is 12, which is around the level we expected to see. 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 41 percent of SSIS users come from large companies. That’s a fairly large proportion if we assume that larger companies have bigger and more complex enterprise data warehouses. For scenarios with high complexity and data volumes, Microsoft has other more cost efficient and innovative tools in its portfolio. Therefore, in our experience, many companies are currently re-evaluating their classic SQL Server architecture to prepare themselves for analytics.

Current use

n=32

Total number of users per company

n=32

Total number of administrators per company

n=26

Company size (number of employees)

n=32

Want to see the whole picture?

BARC’s Vendor Performance Summary contains an overview of The Data Management Survey results based on feedback from Microsoft SSIS users, accompanied by expert analyst commentary.

Contact us to purchase the Vendor Performance Summary

Microsoft SSIS

Peer Groups Data Pipelining Products, Products to Support DW Automation
VendorMicrosoft Corporation
Number of responses32
ProductMicrosoft SQL Server Integration Services
OfficesWorldwide
Employees163,000 approx.
Customers75 million approx.
Revenues (2020)$143 billion
Websitewww.microsoft.com