What's New in SQL Server 2016 Integration Services
Course info
Course info
Description
The SQL Server 2016 version of Integration Services includes many new features. In this course, What's New in SQL Server 2016 Integration Services, you'll explore all of those latest features in great detail. First, you'll learn how to use new control flow tasks and data flow components in Integration Services packages. Next, you'll discover how to configure Integration Services, set up packages to use SQL Server high availability and data encryption features, and to perform data integration with the Azure platform. Finally, you'll dive into learning about the new deployment and logging options. By the end of this course, you'll have a clear understanding of what's new in Integration Services and how to start using the features that matter most to you.
About the author
Stacia Misner Varga is a consultant, instructor, author, mentor, BI Partner for SQLSkills, and principal of Data Inspirations specializing in Microsoft business intelligence technologies for over 10 years. She is a frequent speaker at the PASS Summit, IT/Dev Connections conferences, and various SQL Saturday and Microsoft-related events worldwide.
More from the author
Section Introduction Transcripts
Course Overview
Hi. I'm Stacia Varga, and welcome to my course, What's New in SQL Server 2016 Integration Services? I'm a business intelligence consultant and instructor at Data Inspirations, a company I started back in 2006 to help people understand what their data is telling them. There's actually quite a lot of new things in this particular release, probably the most new things that I've seen in integration services since it first was released in SQL Server 2005, so in this course we're going to go down every nook and cranny to see what's new, see how it works, and explore a few implications about these new features that might not be so obvious on the surface. So what is new in this release? Well, there's several features that affect package development, features that are supposed to make it easier for you to build your packages more efficiently and faster. Also in this release, support has been added for high availability and data encryption features that exist in SQL Server 2016, and we'll look at what's included in the Azure Feature Pack for SQL Server 2016. Then last, we'll wrap up this course by exploring the features that help us deploy and manage packages, things like performing incremental deployments, and new ways to capture logging information about package execution. Now you can watch this course from beginning to end, which I hope you do, or you can just dive in wherever you need to and learn exactly what you need about the features that matter most to you.
Exploring Options for the Package Development Workflow
Hi. I'm Stacia Varga. There are a lot of new things in Integration Services that we'll review together throughout this course. You can't do anything with course. You can't do anything with Integration Services until you have packages, so that's where we're starting. In this module I'm going to lead you through an exploration of the new features that are specific to package development. We'll start by reviewing some new workflow features that might change how you approach the development of multiple packages, and then we'll drill into new control flow components that provide new capabilities for connecting to a broader array of services, and for performing specific tasks, and then we'll explore new data flow components. These components will allow you to move data to or from new types of data sources and destinations.
Exploring New and Updated Control Flow Components
Hi. I'm Stacia Varga. In this module we look at the changes and additions to control flow components that you can use in SQL Server 2016 Integration Services. If you work with Excel workbooks as a source or a destination you'll find the Excel Connection Manager has been update to accommodate more recent file versions. Also, if you work with tabular models in Analysis Services you'll find that several components have been updated, so that you can more clearly differentiate between working with tabular and multi-dimensional models, and if you use the XML task you should know about a validation property that you can use. Technically it's not new, but it was never documented before SQL Server 2016, and if you have Hadoop in your environment you now have a Connection Manager in Integration Services specifically for connecting to Hadoop, and there are some Hadoop related tasks that are now natively available in Integration Services.
Using New Data Flow Components
Hi. I'm Stacia Varga. The data flow side of Integration Services also has some new components that we can investigate in SQL Server 2016. There are some updated connectors available for download that we're going to take a quick look at, and we also have some source and destinations specific to HDFS. Another new built-in source has been added for use with OData sources, and you can also set up data feed publishing, which essentially exposes data that's been extracted and transformed by a package, and makes it a SQL Server view that any application or user can query. Then there's the Balanced Data Distributor, which is an easy way to parallelize your data flow, and there's a new property for the Data Flow Task that helps you manage buffer sizing.
Understanding High Availability and Data Encryption in Integration Services
Hi. I'm Stacia Varga, and as part of this, What's New in SQL Server 2016 Integration Services course we're going to take a look at two database engine features that we can take advantage of for managing high availability and for protecting our data with encryption. Specifically, I'm going to show you what it means and what it looks like to use the SQL Server Always On Availability Group to provide high availability for your Integration Services Catalog, and I'm going to show you how to read from or write data to SQL Server tables that use the Always Encrypted feature, and we're going to review the implications of working with encrypted data both during development and when putting packages into production when they need access to encrypted data.
Using Integration Services with Azure
Hi. I'm Stacia Varga, and welcome to another module in which we're exploring the latest and greatest features in SQL Server 2016 Integration Services. This time we're going to see what that means when we want to use Integration Services with Azure. There are a log of new features in this release that support Azure. We'll start by exploring the Feature Pack for Azure that you can download and install to add a variety of components to support moving data into our out of Azure resources. We'll also explore how to use Integration Services in the control flow for big data processing in Azure, and we'll look at ways that we can interact with Azure in the data flow.
Managing and Deploying Packages
Hi. I'm Stacia Varga. In this module we end the What's New in Integration Services course with a look at features that affect how we manage and deploy packages. The group of features that fall into this category include Data Flow Diagnostics. If you've been frustrated by the steps that you have to go through to figure out why a column in the data flow is flagged as an error you're really going to like this new feature. In this module we'll also take a look at the new Incremental Deployment option that allows you to send individual packages to the Integration Services Catalog instead of the entire project, and we'll close up this module and this course by reviewing the changes to logging that give you more control over what you see in a log and even who can see the log.