Table of Contents
SQL Server Integration Services (SSIS) continues to be one of the most powerful and versatile ETL (Extract, Transform, Load) tools for moving and transforming data. With the release of SQL Server 2016, Microsoft has added some great new features and enhancements to SSIS that improve performance, scalability, and ease of use.
In this post, we’ll explore one of the new out-of-the-box SSIS packages – SSIS Package 816 – and how it can help you get the most out of SSIS 2016.
What is SSIS Package 816?
SSIS Package 816 is one of the new built-in packages included in SQL Server 2016. It’s designed to help SSIS developers improve the performance and scalability of their packages by providing templates and examples for best practices and advanced concepts.
Some of the key things SSIS Package 816 demonstrates:
Advanced-Data Flow Components
The data flow in SSIS Package 816 utilizes some of the more advanced transformations like the Slowly Changing Dimension, data conversion, and Term Lookup/Extraction components. It shows how to properly configure them for optimal performance.
Read also: Creepy Facts About the Statue of Liberty
Dynamic Connection Managers
The package uses expressions and configurations to set connection manager properties dynamically at runtime. This avoids hardcoding server or database names in the package.
Configured Error Handling
Robust error handling is implemented using SSIS events and containers. This includes structured error handling with multiple levels for specific errors.
Transactions and Checkpoints
The package uses transactions at the appropriate points to roll back on failures. Checkpoints are also implemented to restart the package from the point of failure.
Parameterized Packages
Package configurations and parameters allow the same package to be reused for different environments and parameters.
Logging and Notifications
Comprehensive logging at package, container, and task levels is configured. The logs are written to SQL Server tables for reporting. Email notifications alert on failures and completions.
By leveraging these best practices in SSIS Package 816, you can improve the quality and robustness of your packages.
Using SSIS Package 816
SSIS Package 816 is installed by default when you install SQL Server 2016. It can be found in the MSDB database within the SQL Server Integration Services Project folder.
To open and run the package:
- Open SQL Server Data Tools (SSDT)
- Connect to the MSDB database where SSIS Package 816 is located
- Navigate to the Integration Services Project folder
- Open the Package project
- Double-click on Package. dtsx to open the package
Once opened, you can examine the components, configurations, and settings used throughout the package. The package can also be executed to see it in action.
Customize the Package
While SSIS Package 816 provides a great template, you will need to customize it for your requirements. Here are some key things to modify for your packages:
- Connections – Update server and database names in the connection managers or set to dynamic configurations
- Data Flow – Swap out data sources, transformations, and destinations to match your needs
- Parameters – Add any package parameters required for your environment
- Configurations – Adjust configurations for error thresholds, notifications, and logging
By starting with the framework in SSIS Package 816, you can accelerate your development while ensuring you build on SSIS best practices.
Key SSIS 2016 Features Demonstrated
Beyond being just a collection of best practices, SSIS Package 816 also serves to demonstrate some of the key new capabilities added in SSIS 2016:
Performance Improvements
The data flow and transformations utilize a new high-performance engine to maximize throughput. For example, the OLE DB destination has been completely rewritten to be much faster than prior versions.
Scale-Out for Big Data
For big data ETL, you can now scale out SSIS packages across multiple servers or containers. SSIS Package 816 shows an example with execution partitions and workloads distributed using Azure SQL Data Warehouse PolyBase.
New Transformations
Components like Slowly Changing Dimension, Data Quality Services, and Term Lookup/Extraction provide more transformation options without needing custom development.
Azure Support
SSIS 2016 improves Azure support with connections to Blob Storage, Data Lake Store, and Data Factory integration. SSIS Package 816 demonstrates pulling data from Azure and loading to Azure SQL Data Warehouse.
Deployment/Catalog Enhancements
The SSIS catalog (SSISDB) now enables standardized deployment across environments. It also centralizes execution, logging, and alerts.
By examining and running SSIS Package 816, you can get exposed to all these great new features and see them in action.
Key Takeaways
Here are some of the main takeaways to help you get the most value out of SSIS Package 816:
- Provides a template for best practices like error handling, transactions, logging, and notifications that you can implement in your packages
- Demonstrates advanced concepts like dynamic configurations, parameterized packages, and distributed execution to improve flexibility and performance
- Serves as an example for key SSIS 2016 features including scale-out, Azure support, new transformations, high-performance data flow, and catalog deployment
- Accelerate your learning of SSIS 2016 and development of robust ETL processes by using Package 816 as a guide
Overall, SSIS Package 816 is a great tool for boosting your knowledge of SSIS 2016 and quickly implementing solid ETL processes based on proven patterns and techniques. Be sure to leverage it as an example, template, and learning tool as you develop your own SSIS packages.
