You’ve probably heard that SQL Server 2016 Service Pack 1 includes a new version of SQL Server Integration Services. SSIS version 816, as some are calling it, has some cool new features that are worth checking out. In this post, we’ll give you the lowdown on what’s changed in SSIS 816 so you can decide if it’s worth upgrading. We’ll cover the new scale-out features for high performance ETL, the new connectors for non-relational data sources, and other improvements. If you regularly develop SSIS packages for SQL Server, you’ll want to keep reading to find out what SSIS 816 means for your projects. We’ll tell you what you need to know to get up to speed on the latest version of this key data integration tool.
What Is SSIS 816?
SSIS 816 refers to SQL Server Integration Services 2016, the latest version of Microsoft’s enterprise data integration and data transformation platform. Released in late 2016, SSIS 2016 includes several improvements over the previous version, SSIS 2014, to help developers build robust data integration solutions.
Scalability and Performance
SSIS 2016 leverages SQL Server 2016 to provide significant performance and scalability improvements. It allows packages to run on multiple nodes in a SQL Server AlwaysOn availability group, scaling out packages across multiple servers. It also includes a scale-out architecture, allowing packages to run on multiple nodes in a SQL Server cluster.
Data Flow Task Enhancements
The data flow engine in SSIS 2016 includes performance optimization for fuzzy grouping and lookup transformations. It also provides enhanced data profiling capabilities to help understand your data as you build your data flow. The OLE DB source now supports a fast load option for high performance data loads.
Deployment Enhancements
SSIS 2016 also provides enhancements to make it easier to deploy your packages. The project deployment model now supports deploying parameters, environments, and environment variables to SQL Server. You can also deploy packages directly from Visual Studio to SQL Server without having to build a deployment utility.
Additional Features
SSIS 2016 also includes other useful features like configuration of data taps to monitor data as it moves through the data flow, custom logging levels for packages, and the ability to restart packages from the point of failure. The improved UX also makes SSIS 2016 the most user-friendly version yet.
With significant performance and scalability improvements, enhanced data flow capabilities, simplified deployment, and additional useful features, SSIS 2016 is a compelling upgrade for any organization using a previous version of SSIS. The latest and greatest, SSIS 2016 has all the capabilities you need to build robust data integration solutions.
Key Features and Capabilities of SSIS 816
Robust Data Integration Platform
SSIS 816 is a powerful data integration platform that allows you to connect to virtually any data source, perform complex transformations and load that data into one or more destinations. Whether you need to consolidate data from multiple sources, migrate data to a new system, or just keep databases in sync, SSIS 816 has you covered.
Intuitive and Visual Designer
SSIS 816 features an intuitive drag-and-drop visual designer that makes building complex data integration solutions easy. Simply drag data sources and destinations onto the designer surface, then add and configure data flows, transformations, and workflows to build your solution. No coding required! The visual tools help streamline development so you can build solutions faster.
Scalable and High Performance
SSIS 816 is built for high performance and scalability. It can handle huge data volumes and complex workflows with ease. Advanced optimizations like partitioned loads, incremental loads, and parallel data flows allow you to achieve maximum throughput. SSIS 816 also has enterprise-level features like configuration files, logging, and role-based security to support large-scale enterprise data integration needs.
Broad Connectivity
Connect to any data source with over 90 built-in native connectors in SSIS 816, including databases, file formats, cloud services, and applications. For any unsupported data source, you can build your own custom connector using the extensible framework. This broad connectivity allows you to integrate data from across your organization, no matter where it resides.
With a robust platform, intuitive designer, high performance, and broad connectivity, SSIS 816 has all the capabilities you need to build and deploy sophisticated data integration solutions. SSIS 816 helps you unlock the value of your data through fast, flexible and scalable data integration.
Top Benefits of Upgrading to SSIS 816
If you’re still using an older version of SQL Server Integration Services (SSIS), upgrading to the latest SSIS 816 release comes with some major perks. Here are a few of the top benefits you’ll gain by making the switch:
Improved performance
SSIS 816 includes significant performance enhancements that will make your packages run faster. It introduces a new “scale out” feature that allows you to distribute package execution across multiple computers. This parallel processing can drastically reduce the time it takes to run complex workflows.
Enhanced security
Data security is more important than ever, and SSIS 816 has you covered. It includes new features like Always Encrypted, which encrypts sensitive data at rest and in motion, and row-level security which enables you to control access to rows in a database table based on characteristics of the user executing a query.
Modern data connectors
Connecting to data sources is a huge part of an ETL tool’s job, and SSIS 816 provides seamless connectivity to all the latest data platforms and services. It includes built-in connectors for big data sources like Hadoop, Azure Data Lake Store, and Spark. It also connects to popular cloud data services like Azure SQL Database, Azure Synapse Analytics, Azure Cosmos DB, and more.
Improved management
Managing a complex SSIS environment requires robust tools, and the SSIS 816 release delivers. The new SSIS catalog database provides a centralized repository for storing and managing your packages, parameters, environments, and operational history. The SSIS Scale Out Master feature allows you to administer distributed package execution across multiple machines from a single point of control.
Upgrading to the latest and greatest version of SQL Server Integration Services will provide huge benefits through improved performance, enhanced security, modern data connectors, and simplified management. If you’re still using an older version of SSIS, make the switch to SSIS 816 and unlock its full potential.
Step-by-Step Guide to Installing and Configuring SSIS 816
Gather the prerequisites
Before you start, make sure you have a system with Windows Server 2016 that meets the minimum hardware requirements for SQL Server 2016. You’ll also need the installation media for SQL Server 2016 Integration Services (SSIS).
Install SQL Server 2016
Run the SQL Server 2016 setup wizard and select “Integration Services” to install SSIS. Choose either a default or custom installation. The default installation will get you up and running quickly, while a custom install lets you specify options like the installation path.
Launch SQL Server Data Tools
After installation completes, open SQL Server Data Tools (SSDT) which provides an environment for building SSIS packages. In SSDT, create a new Integration Services project. This will contain the packages you build and deploy.
Build your first package
Drag and drop data flow components like sources, transformations, and destinations onto the design surface to construct your data flows. Then connect them together, set their properties, and you’ll have built your first SSIS package!
Deploy and schedule your package
Once your package is ready, you can deploy it to the SSIS catalog, a database in SQL Server used to store and run SSIS packages. In the catalog, you can set parameters, configure environments, and schedule your package to run on a recurring basis.
Additional configuration
Some other useful tasks include: setting package configurations to update properties without editing packages directly; using parameters to make packages more flexible; creating package templates to standardize development; and using container components like For Loops to simplify complex package control flows.
SSIS 816 offers a robust set of tools for building enterprise-level data integration solutions. Follow these steps to get started, then explore all it has to offer! With practice, you’ll be creating complex data flows and deployment configurations in no time.
SSIS 816 FAQs: Common Questions About the Latest Release
What’s new in SSIS 816?
The latest release of SQL Server Integration Services (SSIS) brings some exciting new features. SSIS 816 introduces data integration in the cloud with Azure SQL Database and Azure SQL Data Warehouse. You can now run on-premises SSIS packages directly in Azure and monitor them from the Azure portal. The new Azure-enabled SSIS feature allows lifting and shifting existing SSIS workloads to the cloud.
Do I need to rewrite my packages to use SSIS 816?
The great news is SSIS 816 is backward compatible, so you do not need to rewrite your existing packages to benefit from the new release. Your SSIS 2012-2017 packages will continue to work as-is. However, to take advantage of new features like Azure integration, you will need to deploy your packages to SSIS 816. You can easily upgrade your project deployment model to SSIS 816 without changing the package code.
How do I get started with SSIS 816?
To get started with SSIS 816, first ensure you have installed the latest version of SQL Server Management Studio (SSMS). Then, open SQL Server Data Tools (SSDT) which is included in SSMS. Create a new SSIS project, select “Integration Services Project” and choose “SSIS 816” as the project deployment model. Your new SSIS 816 project will contain an empty package to get started.
Do I need Azure to use SSIS 816?
No, SSIS 816 can be used for on-premises data integration without Azure. However, to unlock features like running packages in Azure SQL Database, monitoring in the Azure portal, and hybrid data integration across on-premises and cloud, you will need an Azure subscription. SSIS 816 gives you the flexibility to choose where and how you want to run your data integration workloads.
What are the system requirements for SSIS 816?
SSIS 816 requires Windows Server 2016 or later and SQL Server 2017 or SQL Server 2019. For development, you can use Visual Studio 2017 or later with the latest version of SSDT. 4 GB of RAM and 2 GB of disk space are recommended. An Azure subscription is required for certain features like running packages in Azure SQL Database.
Conclusion
So there you have it – the key things to know about SSIS 816. While it may seem daunting at first, taking the time to learn about the new features and capabilities will make you a rockstar SSIS developer. Approach it with an open and curious mindset. Learn from others who have gone before you. And remember – it’s not about the destination, it’s about the journey. This is just another step forward in your data transformation skills evolution. Embrace the change, learn all you can, and soon you’ll be leveraging SSIS 816 like a pro. The future is bright, so soak it up!
