BACKGROUND IMAGE: iSTOCK/GETTY IMAGES
Microsoft SQL Server Integration Services, which is included with Microsoft's SQL Server database product, provides extract, transform and load functionality for data integration and data movement between applications. Microsoft SSIS shares common development and management tools with SQL Server Analysis Services and SQL Server Reporting Services. In addition, the product works with various Microsoft Azure platform products such as SQL database as a service and HDInsight.
Fundamentally, SSIS is built as an ETL architecture. However, since it's bundled with Microsoft SQL Server, it can be used as an extract, load and transform architecture when using SQL Server as either a source or a target database.
Microsoft SSIS can connect to a diverse set of data types such as databases, files, web services, extensible markup language (XML) and those supported on the Azure platform, although this requires an additional license. It's tightly integrated with Excel, SQL Server Analytics Services and other SQL Server in-database capabilities.
SSIS packages are split into control flows that manage the overall workflow performed, while data flows control the movement and transformation of data between sources and targets.
SSIS offers a wide variety of data transformations, including slowly changing dimensions, change data capture, aggregating, splitting, merging, fuzzy lookup, data mining and pivoting data.
Development and administration is enabled by SQL Server Data Tools, which is built on Visual Studio. This provides a common development environment for all the products bundled with Microsoft SQL Server and Azure SQL databases, such as SQL Server Reporting Services, SQL Server Analysis Services and data mining.
What key enhancements are in the latest version?
Microsoft SQL Server 2016 includes the following features:
- Improved performance, logging, error handling and reuse of components.
- Enhanced big data and cloud connectivity to Hadoop, Azure and HDInsight for Hive and Pig tasks.
- Enhanced change data capture, built-in R services and XML validation.
Who uses Microsoft SSIS?
Since SSIS is bundled with Microsoft SQL Server licenses, organizations using it are typically also using SQL Server as the SSIS target for such integration use cases as business intelligence, data warehousing and data movement. The most extensive use of SSIS is in midmarket and small-to-medium-size businesses that are leveraging their resource commitment to a Microsoft environment. SSIS is also used for tactical data integration projects, such as loading business intelligence data sets (i.e., data marts), within large companies.
In which editions of Microsoft SQL Server is SSIS available?
SSIS became available with the 2005 version of Microsoft SQL Server. Each new version of SQL Server included increased SSIS functionality. Microsoft SQL Server is licensed in enterprise, developer, business intelligence, standard, web and express editions with Microsoft SSIS being available in the first four editions mentioned.
How is Microsoft SQL Server priced?
Microsoft SQL Server Integration Services is included with a Microsoft SQL Server database license. Contact Microsoft for SQL Server edition pricing.
What Microsoft Azure cloud services offers developers
Learn how to use Microsoft SQL Server 2016 Polybase
Microsoft SQL Server 2016 reviews from beta testers