What is ssis 816 ? Turbocharge Data Integration Performance

SSIS 816, also referred to as SQL Server Integration Services Scale Out, is an important feature introduced in SQL Server 2016 that allows for significant performance improvements in data integration and transformation workloads. This guide will provide everything you need to know about SSIS-816.

Key Takeaways

• SSIS 816 distributes execution of SSIS packages across multiple machines to enhance performance and scalability.
• Key capabilities enabled by SSIS 816 include robust ETL functionality, extensibility, high volume data handling, monitoring and management.
• Upgrading to SSIS-816 offers benefits like improved performance, flexibility, scale-out abilities and more.
• Changes in SSIS816 over previous versions include Visual Studio Code support, scale-out features, custom logging, Azure Feature Pack and so on.
• The upgrade process involves uninstalling old versions, installing SSIS 2016, migrating packages and testing.

What is SSIS 816?

SQL Server Integration Services (SSIS) is a component of SQL Server used for data integration and workflow applications. It allows users to extract data from various sources, transform it to fit business needs, and load it into destination databases or data warehouses.

SSIS provides a rich set of tools and transformations to integrate data from different platforms and formats. It has been a popular ETL tool and widely used for data warehousing, ETL and reporting solutions.

Introducing SSIS Scale Out

To enhance the performance and scalability of SSIS, SQL Server 2016 introduced a new feature named SSIS Scale Out (SSIS-816).

The key idea behind SSIS Scale Out is to distribute execution of SSIS packages across multiple machines, especially when handling large data volumes. This allows for significant performance gains through parallel execution.

Some of the key capabilities enabled by SSIS Scale Out include:

• Robust SSIS functionality like wide range of built-in tasks and transformations
• Extensibility to create custom tasks and data flow components
• Scalability to handle large data volumes by distributing workload
• Tools for management, monitoring and deployment of packages
• Logging and auditing facilities for tracking execution

Why Upgrade to SSIS 2016/SSIS Scale Out?

Here are some of the key benefits organizations can realize by upgrading existing SSIS solutions to SSIS 2016 version with Scale Out:

• Much better performance via parallel execution
• Flexibility to scale out SSIS to use additional hardware during peak loads
• Powerful scale-out feature to distribute workload across multiple machines
• Reporting improvements with support for custom log providers
• Enhanced productivity with Visual Studio Code support for SSIS
• Additional capabilities with the new Azure Feature Pack

In summary, organizations using older SSIS versions can achieve faster processing times, scalability, and new features by upgrading to SSIS 2016.

Key Changes and Enhancements

SQL Server 2016 introduced several important changes and enhancements to SSIS. Some of the most notable ones include:

Visual Studio Code Support

SSIS now supports developing and managing packages from within Visual Studio Code, providing an efficient lightweight experience.

Scale-out Feature

The scale-out feature allows distributing package execution tasks across multiple computers for faster performance.

Custom Log Providers

Ability to build custom log providers for centralized logging. For example, log events to Azure Diagnostics.

Azure Feature Pack

New feature pack with components supporting Azure services like Blob Storage, Data Lake Store etc.

Catalog database improvements

Enhancements for configuration, management and monitoring of SSIS solutions.

Other changes

• 64-bit runtime support
• Auto-generation of unit tests through SSDT
• Productivity improvements in SSDT and SSMS tools

Upgrading to SSIS 2016

Upgrading existing SSIS solutions from earlier versions is relatively straightforward. Here are the key steps involved:

1. Uninstall old version

First uninstall existing older SQL Server version (like 2014, 2012 etc) to avoid conflicts.

2. Install SQL Server 2016

Download and run SQL Server 2016 installer. Select required components like SSMS, Visual Studio with SSDT.

3. Migrate packages

Based on project types, use SQL Server Migration Assistant, deploy packages to new catalog.

4. Configure scale-out

Enable and configure scale-out to improve performance by distributing load.

5. Test upgraded solution

Perform comprehensive testing across scenarios to verify successful upgrade.

6. Deploy to production

Finally deploy to production after thorough QA testing. Schedule package executions using agent jobs. Monitor dashboards.

SSIS – A Key Integration Platform

With powerful capabilities like scalability, extensibility and flexibility to work across hybrid environments, SSIS continues to be a strategic integration platform for modern data initiatives.

SSIS skills also enable data professionals to effectively develop solutions for needs ranging from basic ETL to complex EAI and data warehousing requirements.

Troubleshooting SSIS errors by learning fundamentals as well as using tools like debuggers also help build expertise as a SQL Server professional.

Upgrading to the latest 2016 version unlocks new benefits in performance, scale-out architecture and productivity. SSIS undoubtedly delivers robust, enterprise-grade functionality for diverse data integration needs.

Frequently Asked Questions

Here are some common questions about SSIS Scale Out:

What are the hardware and software requirements for SSIS Scale Out?

SSIS816 Scale Out requires SQL Server 2016 Standard or Enterprise edition, with at least 4 core machines. It can scale out to use many machines as needed.

Can I selectively scale out only some packages?

Yes, you can choose which packages take advantage of scale-out across multiple machines. Not all packages need to be scaled out.

Does scale-out provide fault tolerance if a worker machine fails?

Yes, if a worker machine fails others will continue running packages. Although some task failures could happen in affected packages.

What licensing model is applicable for Scale-Out?

Standard SQL Server 2016 licensing terms apply based on cores. Enterprise edition required for production usage.

How do I monitor and manage Scale-Out executions?

The SSIS Catalog and SSMS provide dashboards to monitor and manage Scale-Out jobs and tasks across worker machines.