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 the cloud-based platform 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 small and still growing. Microsoft has a strong presence in the BI market – it has become the market leader for analytic tools with Power BI – 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 (SQL Server Integration Services, also known as SSIS) as well as multidimensional and relational data management. SQL Server Reporting Services (SSRS), a solution for formatted reporting, is also included with SQL Server on-premises. In the cloud, separate services are offered for data integration.
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.
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. It 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, Excel and Azure products such as Azure Data Lake) and various functions for loading and transforming data. Development support functions such as testing and error analysis also support this. 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 own solutions. We see customers of all sizes using Microsoft products. Many smaller and mid-sized companies rely on Microsoft products to cover all their BI needs.
User & Use Cases
94 percent of survey respondents use SSIS for data warehousing and 87 percent use it for data integration, which is not surprising. Also 48 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, 45 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 23 percent of users take advantage of additional data quality services and just 29 percent use SSIS for master data management.
Compared to last year, there has been a significant increase in data warehousing and data integration use cases, while others have remained more or less stable or, in the case of data warehouse automation, decreased slightly.
The mean number of users per company using SSIS is 14, 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 39 percent of SSIS users come from large companies. That is 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.
Use cases
n=31
Extend of usage in the company
n=24
Total number of users per company
n=31
Total number of developers per company
n=24
Company size (number of employees)
n=31
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- Register for a free sample Vendor Performance Summary download
- If you have any questions, feel free to contact us