New Features in SQL Server 2016 – Part 4: Integration Services

In the current SQL Server 2016 CTP2, there are not many exciting improvements made to SSIS.  The hope is that the rumours are true and that the more exciting and beneficial features will come later, probably closer to the actual release.

The below have been announced by Microsoft and are available in CTP2:

·        AlwaysOn Support.

·        Incremental Package Deployment.

·        Project Upgrade

The speculation and noises coming out of the SQL Community imply two features will be available soon.  As nothing has been officially released, I cannot offer much more insight right now.  Look out for an updated blog as and when something is announced.

·        Azure Data Factory integration with SSIS.

·        Enhanced SSIS – Designer support for previous SSIS versions and support for Power Query.

AlwaysOn Support

This high-availability, disaster recovery solution is used for lots of large scale databases as an alternative to mirroring.  Integrating this into SSIS will provide some great benefits.

1.      Easily deploy to a centralised SSIS Catalog (e.g SSISDB database).

2.      SSISSDB can now be added to an AlwaysOn Availability group.

3.     When a failover occurs, a secondary node will automatically become the primary.

Incremental Package Deployment

You can now deploy one or more packages to an existing or new project, without the need to deploy the whole project.  The incremental process can be actioned from:

1.      Deployment Wizard.

2.      SQL Server Management Studio (SSMS).

3.      Stored Procedures.

4.      Management Object Model (MOM) API.

I have tested the package deployment in CTP2 and it works in exactly the same way as project deployment.  The wizard, especially has the exact same look and feel.  In order to run this in CTP2, you will need to navigate to the standard SQL Server 2016 directory, which is usually in:

64 Bit:   C:Program FilesMicrosoft SQL Server130DTSBinn
32 Bit:   C:Program Files (x86)Microsoft SQL Server130DTSBinn

To run the wizard, simply double click the ‘ISDeploymentWizard.exe’ executable in the folder or run it from command line.  You can now select ‘Package’ or ‘Project’ deployment in the options – see below:

There is an option to set password security on individual packages and individually which ones you want to deploy at one time.

When SQL Server 2016 is officially released, I would imagine the package deployment wizard will be integrated into Visual Studio and SSDT and implemented without the need of command line or an exe.

Project Upgrade

Not much has changed here, apart from:

1.      Project-level connection managers work as normal.

2.      Package layout/annotations are always retained.

References

For more information on all of the new SSIS SQL Server 2016 features, the below resources/blogs are highly recommended.

·        Official Microsoft Page –
https://msdn.microsoft.com/en-us/library/bb522534(v=sql.130).aspx

·        Gilbert Quevauvilliers – BI blog –

https://gqbi.wordpress.com/2015/05/07/bi-nsight-sql-server-2016-power-bi-updates-microsoft-azure-stack/

·        Manoj Pandey Blog –
http://sqlwithmanoj.com/2015/05/09/microsoft-announced-sql-server-2016-new-features-and-enhancements/