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 –
· Manoj Pandey Blog –
http://sqlwithmanoj.com/2015/05/09/microsoft-announced-sql-server-2016-new-features-and-enhancements/
Meet the Team – Catherine Sachdev, Marketing Assistant
Next up we’re introducing you to Catherine Sachdev. Catherine joined us just over a year
Jan
Data Lineage with Azure Purview
I wrote an introductory Purview blog post previously, where I explored what the tool is
Jan
The Next Era of Retail: How Technology is driving change in a COVID-19 World
The retail sector is of great importance and accounts for almost 5% of GDP and
Jan
Meet the Team – Alex Kordbacheh, Junior Consultant
It’s time for another Meet the Team blog! This time we’re introducing you to Alex
Dec
Use cases for Recursive CTEs
Introductions Recursive CTEs are a way to reference a query over and over again, until
Dec
Azure Sentinel is named a ‘Leader’ in the Forrester Research Wave Report
Microsoft have recently announced that they have been named a Leader by Forrester Research in
Dec
Getting Started with Azure Purview
Azure Purview (a.k.a Data Catalog gen 2) has been released into preview and is currently
2 Comments
Dec
An Introduction to ApexSQL Complete – Integration with SSMS
We all know that the idea of add-ins is to make our lives easier. In
Dec