Microsoft regularly introduces new features and capabilities into its SQL Server Integration Services tool, and...
has delivered a number of them over the past year.
One such feature is the SQL Server Integration Services database (SSISDB) Package Upgrade Wizard. This tool enables admins to upgrade the SSIS catalog database when the catalog database version is older than the current version of SQL Server. This occurs if an administrator forgets to remove the catalog database from an AlwaysOn Availability Group prior to upgrading the associated SQL Server instance. It can also happen in certain disaster recovery situations that require a restoration from backup. In these situations, the SSISDB Package Upgrade Wizard can upgrade the catalog database so that it matches the version of SQL Server that is being used.
This brings up another point. SQL Server 2016 Integration Services enables administrators to add an SSIS catalog database to an AlwaysOn Availability Group. So it is possible to protect a catalog database in the same way that you might protect any other SQL Server database.
As previously mentioned, the SSIS catalog database can experience a version inconsistency issue if the database exists within an availability group and SQL Server was upgraded without first removing that database from the availability group. The new SSISDB Package Upgrade Wizard can fix this problem by bringing the database version up to date, but it is better to prevent the problem from occurring in the first place.
Microsoft designed SQL Server 2016 so that an in-place upgrade cannot be performed if the SSIS database exists within an availability group. Administrators are forced to remove the database from the availability group, perform the in-place upgrade and then move the database back into the availability group. This prevents the version inconsistency issue from occurring.
SQL Server 2016 also includes improved package management. Admins can save a container or a control flow task as a reusable template through SQL Server 2016 Integration Services. This template exists in the form of a stand-alone DTSX file, and is more formally referred to as a control flow part; although some Microsoft documentation uses the abbreviated term package parts instead.
As you would probably expect, package parts are designed to reduce the administrative workload by providing reusability. A package part is created through the SSIS Designer, and the resulting part can be reused multiple times, either within a single package or across multiple packages.
More Azure functionality
Not surprisingly, Microsoft designed SQL Server 2016 to provide better interoperability with Microsoft Azure. One way in which it did this was by introducing an SSIS Feature Pack for Azure. This provides connectivity to Azure data sources and can transfer data between Microsoft Azure and data sources that are located on premises. For example, an administrator might use the feature pack to upload data to Azure Blob storage or to create an Azure HDInsight cluster. You can download the Azure Feature Pack for SSIS.
Of course, the primary function of SQL Server 2016 Integration Services is to provide connectivity to non-SQL Server data sources. As such, Microsoft released some new connectors for SSIS over the past year. These new connectors include:
- Connector for SAP Business Warehouse for SQL Server 2016
- Connector versions 4.0 for Oracle and Teradata
- Connector for Analytics Platform System Appliance Update 5
Microsoft also officially supports a variety of other data sources for use with SSIS, including:
- OData version 4 data sources
- Excel 2013
- Excel 2016
- The Hadoop distributed file system (HDFS)
Microsoft also supports the ORC file format for HDFS. It is now possible to connect to a Hadoop cluster via the Hadoop Connection Manager and to perform common HDFS operations.
In addition to the Connection Manager, there are several Hadoop and HDFS components included in SQL Server 2016 Integration Services. The HDFS File Source component, for example, helps an SSIS package to read data from an HDFS file, while the HDFS File Destination component enables data to be written to an HDFS file. Built-in flow control tasks include the Hadoop File System Task, the Hadoop Hive Task and the Hadoop Pig Task. It is also worth noting that Microsoft updated its Open Database Connectivity components for SQL Server 2016, but has not introduced any new ODBC functionality.
An updated look at SQL Server tools
Free SQL Server tools
Should you choose an Azure SQL database?