Microsoft SSIS (SQL Server Integration Services) is an enterprise data integration, transformation and migration tool integrated with Microsoft’s SQL Server database. It can be used for various integration-related tasks, such as analyzing and cleaning data and performing extract, transform, and load, or ETL, processes to update data warehouses .
SSIS can extract, transform, and consolidate data from multiple relational databases, as well as sources such as XML data files and flat files, then load the processed information into an enterprise or enterprise data warehouse. other target systems. It includes a set of tools for developing and testing integration programs, called SQL Server Data Tools, as well as a server component for deploying and running the programs. Additionally, Microsoft’s SQL Server Management Studio software is integrated with SSIS to manage and monitor integration routines.
Microsoft SSIS was introduced with SQL Server 2005 as a replacement for an earlier integration tool called Data Transformation Services (DTS). Before DTS was released with SQL Server 7.0 in 1998, database administrators wrote custom data transformation tools or used third-party tools to transfer data.
Microsoft has added a number of features to SSIS over the years, including graphical tools and wizards, which allow users to build and debug packages; workflow functionality, such as file transfer protocol operations; the ability to execute SQL statements; the possibility of sending messages by e-mail; data sources and destinations for ETL; transformations to collect, clean, merge and copy data; a management service; the ability to administer the execution and storage of packages; and application programming interfaces (APIs) for SSIS object models. Among the most popular features are the data import/export wizard and packaged data source connectors.
SQL Server 2016 enhancements
SQL Server 2016 introduced the SQL Server Integration Services Database (SSISDB) Package Upgrade Wizard. This tool allows IT professionals to upgrade the catalog database when it is older than the current version of SQL Server. It can also be used in some disaster recovery situations that require restoring from a backup. The SSISDB wizard upgrades the catalog database to match the version of SQL Server being used.
SQL Server 2016 Integration Services allows administrators to add an SSIS Catalog database to an AlwaysOn Availability Group. It also includes improved package management, where administrators can save a control flow container or task as a reusable template through Integration Services. SQL Server 2016 also included an SSIS feature pack for Azure. This allows administrators to connect to Azure data sources and transfer data between the Azure cloud and on-premises data sources.
Microsoft released connectors for SSIS in 2016, including Connector for SAP Business Warehouse for SQL Server 2016, Connector versions 4.0 for Oracle and Teradata, and Connector for Analytics Platform System Appliance Update 5.
SQL Server 2017 enhancements
SQL Server 2017 brought new SSIS features, such as Scale Out for SSIS, which makes it easier to run SSIS on multiple machines. Administrators can avoid a single point of failure for the entire Scale Out deployment. Also included is improved handling of execution log failover from Scale Out Workers. Execution logs persist to local disk if the Scale Out Worker stops suddenly.
The SSIS Catalog also includes a new global property that specifies the default mode for running SSIS packages. This applies when IT professionals call the stored procedure with the runinscaleout parameter set to no.
SSIS is version specific, so users should have the version of SSIS that matches their SQL Server edition. Also, SSIS is not supported in SQL Express or Workgroup editions. Users must purchase the Standard, Business Intelligence, or Enterprise editions to get the SSIS designer and runtime components as well as basic data profiling tools and predefined transformations. Advanced adapters and advanced transformations are only available in the Enterprise edition.