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’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 by 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., Analytics Platform System including Parallel Data Warehouse, a Hadoop distribution with HDInsight). These tools use functions of Microsoft SQL Server or can be closely integrated with it.
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
97 percent of survey respondents use SSIS for data integration, 69 percent use it for data warehousing and 56 percent say they use it 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, 41 percent of those surveyed state that they use the product for master data management and 32 percent for data quality management. Under certain circumstances, the additional SQL Server services for master data management and data quality management can also be used here in conjunction with SSIS. The median number of users/technical users per company is relatively low, but the high mean (369 users) indicates that there are some very large deployments.
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 has other tools in its portfolio. Therefore, in our experience, many medium-sized companies are currently rethinking their classic SQL server architecture to prepare themselves for analytics.
Number of users using Microsoft SSIS
Number of technical users using Microsoft SSIS
Company size (number of employees)
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 SQL Server Integration Services users, accompanied by expert analyst commentary.Contact us to purchase the Vendor Performance Summary