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/
How Artificial Intelligence and Data Add Value to Businesses
Knowledge is power. And the data that you collect in the course of your business
May
Databricks Vs Synapse Spark Pools – What, When and Where?
Databricks or Synapse seems to be the question on everyone’s lips, whether its people asking
1 Comment
May
Power BI to Power AI – Part 2
This post is the second part of a blog series on the AI features of
Apr
Geospatial Sample architecture overview
The first blog ‘Part 1 – Introduction to Geospatial data’ gave an overview into geospatial
Apr
Data Lakehouses for Dummies
When we are thinking about data platforms, there are many different services and architectures that
Apr
Enable Smart Facility Management with Azure Digital Twins
Before I started writing this blog, I went to Google and searched for the keywords
Apr
Migrating On-Prem SSIS workload to Azure
Goal of this blog There can be scenario where organization wants to migrate there existing
Mar
Send B2B data with Azure Logic Apps and Enterprise Integration Pack
After creating an integration account that has partners and agreements, we are ready to create
Mar