New management role for SSIS 2016

SSIS 2016 management

Working in regulated environments presented some challenges with the SQL Server Integration Services, SSIS, project deployment model which was introduced with the 2012 release of SQL Server. Specifically, there was no way to use the native reporting and dashboard features of the Integration Services Catalog without being a system administrator or having the ability to administer the SSISDB catalog. That's a clear violation of the separation/segregation of duties polices for the major compliance regulations.

The upcoming release of SQL Server 2016 provides a new built-in role, ssis_logreader that should address the need to allow support staff the ability to support SSIS without violating separation of duties requirement. They will be able to view the history for a package - its average, minimum and maximum durations along with run frequencies as well as delve into any errors associated to those runs. All of this from the comfort of the native reports built within the SQL Server Management Studio. No more will they need to write custom queries and master the object model.

We see this as an excellent step forward in the maturation of the administrative experience for SSIS.

SSIS 2016 Incremental Package Deployment

The community technology preview 2.3 for SQL Server 2016 is the first in the series of CTPs to deliver Business Intelligence functionality. Specific to SSIS, this release delivers support for the oData v4 protocol, Error Column, custom logging levels and the topic for this post: incremental package deployment.

Project Deployment Model

The classic problem with SSIS projects is that there was nothing that linked the packages to the business problem they were designed to solve once development was complete. To get the sales data loaded, we need to get both the header data as well as the detail data populated or we have an incomplete order. The 2012 release of Integration Services sought to address that by delivering a new deployment model: the project deployment model. This allowed solutions to be compiled into a deployable unit, the ispac, which ensures that all the pieces that were developed are delivered.

An aspect of this new deployable unit was that redeployments required the complete ispac to be reinstalled. One could not just deploy an updated version of the order detail processing package by itself. The updated package had to be compiled into an ispac and the new ispac replaced the existing one. For organizations that struggle with change control, this can appear to be a much larger change than it truly is.

Incremental package deployment

The upcoming 2016 release of SQL Server provides a path for providing incremental updates to an existing SSIS project. Individual packages can now be deployed in a piecemeal fashion into a project. This can greatly simplify a change control request as only the packages that have modified will be deployed into the project.

The UI defaults to "Project Deployment" which consumes an ispac file, as it always has.

Choosing "Package Deployment" allows for the selection of a folder that contains the updated SSIS package(s).

Further reading

• Marketing CTP 2.3 blog
• Engineering CTP 2.3 blog
• SQL Server Integration Services 2016 Incremental Package Deployment