Advanced Integration Services - Part 2

This course teaches you how to develop, deploy, and secure Integration Services packages that manage specific types of data warehouse extract, transform, and load operations.
Course info
Rating
(95)
Level
Intermediate
Updated
Mar 17, 2015
Duration
4h 44m
Table of contents
Managing Incremental Loads
Azure and Integration Services
Deployment - Part 1
Deployment - Part 2
Security
Special Design Scenarios
Description
Course info
Rating
(95)
Level
Intermediate
Updated
Mar 17, 2015
Duration
4h 44m
Description

This course explains how to apply Integration Services features to build packages that support the extract, transform, and load operations of a data warehouse. It shows strategies for managing high-volume, slowly changing dimensions and for handling text and XML files. It also explains how to adapt packages to work with Azure resources. In addition, this course reviews how to deploy packages to production and how to secure and execute deployed packages. This course was written for SQL Server 2014 Integration Services, but most principles apply to SQL Server 2005 and later.

About the author
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
Getting Started with Power BI
Beginner
3h 14m
Jun 23, 2016
More courses by Stacia Misner Varga
Section Introduction Transcripts
Section Introduction Transcripts

Azure and Integration Services
Hi I'm Stacia Misner and Welcome to Azure and Integration Service. In this module, I'll explain how to use Integration Services to move data into, or out of, the cloud. Specifically, in Microsoft Azure. In this module, I've assume that you've already made the decision to incorporate the cloud into your data infrastructure. But if you're still not sure whether it's the right solution for your needs, on the resources page at the end of this module I've included links to other Pluralsight courses that provide more background about working with Microsoft Azure and that should give you deeper insight into the benefits that it provides. The goal of this module is to help you use Integration Services to support a cloud-based or hybrid environment consisting of cloud and on-premises components. We start with a review of the different ways that we can architect a solution that involves both Cloud Components and Integration Services. Then we review the strategies to consider for running Extract, Transform, and Load workloads. Last, we take a look at other aspects of managing Integration Services when the cloud is involved.

Deployment - Part 1
Hi, I'm Stacia Misner. Welcome to Deployment Part 1. In this module I'll explain how to deploy your Integration Services packages by using the Package Deployment Model. This model is also known as the Legacy Model because it applies to every version of Integration Services, beginning with SQL Server 2005 and continuing to the latest release, which at the time of this recording is SQL Server 2014. This functionality may be deprecated in future releases. As we review the deployment process, using the Legacy Model, we'll learn about the various deployment target options that we have. A Deployment Target is the location in which we store our Integration Services Projects and we'll learn how this choice of location affects package security, management, and execution. When we work with the Legacy Deployment Model, we can manually deploy packages or, we can take advantage of the built-in deployment utility. In this module, we'll explore how to work with this utility and the options for deployment that it offers. One of the ways that Integration Services supports flexibility is to allow us to use variables and expressions to change package behavior based on conditions at execution time. When we deploy packages, we have yet another option. We can use package configurations to define alternate values for properties at the package container or task level or, for connection managers. We can save these configurations to control package behavior, as we'll learn how to do in this module. Last, we'll learn about the different ways that we can manage and monitor the execution of deployed packages.

Deployment - Part 2
Hi, this is Stacia Misner. This is Deployment, Part 2. p In this module, I'll explain how to deploy your Integration Services Packages by using the Project Deployment Model. This model applies only to recent versions of Integration Services beginning with SQL Server 2012 and continuing to the latest release, which is currently SQL Server 2014. As we learned in the previous module, we use the term Deployment to describe the process we use to move packages from Development into a new environment, such as Testing or Production. In this module, we'll learn about the techniques and tools that we use when working with the Project Deployment Model. First, we need to learn about the SSIS Catalog which was first introduced in SQL Server 2012 to support deployed packages that are grouped together as part of a project. Then we'll review the different options we have for deploying our packages to the catalog. In addition, we'll see how to use Environments to override specific package properties when we run a package. And we'll end the module with a review of how to work with Package Execution when we have a Project Deployment Model.

Security
Hi, I'm Stacia Misner. In this module, I'll explain how to secure packages, as well as the server operations and management associated with executing those packages. Specifically, in this module we'll review settings within each package to control who can control a package for editing and whether they can see sensitive information. We'll also cover Security Settings at the server level for packages that are deployed using the Package Deployment Model so that we can control who can see packages available on the server, who can execute the packages and who can monitor package execution. These security settings apply to any version of SQL Server running Integration Services, from SQL Server 2005, to SQL Server 2014. We'll review the same security setting for packages deployed using the Project Deployment Model. However, these security setting apply only to versions beginning with SQL Server 2012 and later. Last, we'll take a look at the security settings that we need to successfully run packages on a scheduled basis, regardless of whether we're using a package or project deployment model.

Special Design Scenarios
Hi, I'm Stacia Misner and welcome to the second module of the Advanced Integration Services Part 2 course, Special Design Scenarios. In this module, I'll explain how to work with text files that contain data in header and footer rows that we need to process, because we'll need to process them differently than data rows. Now, Integration Services includes a task in the Control Flow and a Data Source in the data flow that we can use to process XML documents, but the way we use them isn't really intuitive; therefore, I'll review how to use these components to process XML documents.