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
Level
Intermediate
Updated
Mar 17, 2015
Duration
4h 43m
Table of contents
Managing Incremental Loads
Introduction
2m
T-SQL MERGE Statement: Type 1 SCD
5m
T-SQL MERGE Statement: Type 2 SCD
4m
Dimension Load Pattern with MERGE
2m
Demo: Slowly Changing Dimension Handling with MERGE
11m
Change Data Capture (CDC)
4m
CDC in Integration Services
8m
Demo: Slowly Changing Dimension Handling with CDC
14m
Custom Component
1m
Summary
1m
Azure and Integration Services
Introduction
1m
Architecture Options
1m
Virtual Machines and SQL Databases
4m
Demo: Creating an Azure Virtual Machine
3m
Demo: Creating a SQL Database
3m
Hybrid: On-premises and Azure
1m
Demo: Creating a Virtual Network
8m
Demo: Joining an Azure Virtual Machine to an On-Premises Network
2m
ETL Strategies
1m
Data Connections
0m
Demo: Working with Data Connections in the Cloud
5m
ETL Design Patterns for the Cloud
2m
Demo: Modifying the ETL Design Pattern for the Cloud
11m
Data Subsets
1m
Demo: Extracting a Subset of Data
5m
File Compression
1m
Demo: Compressing Files for ETL
7m
Additional Considerations for Cloud Integration
1m
Planning for Failure
2m
Demo: Implementing Retry Logic
12m
Sharding
1m
Scaling Processes
3m
Summary
3m
Deployment - Part 1
Introduction
2m
Deployment Target Options
3m
Deployment Utility
3m
Demo: Preparing for Deployment
1m
Demo: Package Installation Wizard Deployment to MSDB
1m
Demo: Package Installation Wizard Deployment to File System
3m
Other Deployment Tools
1m
Demo: Import into MSDB
2m
Demo: Use DTUTIL to Import into MSDB from File System in Bulk
2m
Package Configuration
4m
Demo: Working with Configurations
5m
Package Execution
1m
Execute Package Utility
1m
Demo: Using the Execute Package Utility
3m
DTExec Utility
1m
Demo: Using the DTEXEC Utility
1m
SQL Server Management Studio
1m
Demo: Running a Package in Management Studio
1m
SQL Server Agent Job
0m
Demo: Scheduling a Package as a SQL Server Agent Job
1m
Summary
2m
Deployment - Part 2
Introduction
1m
SSIS Catalog
2m
Demo: Creating the SSIS Catalog
3m
Deployment Options Overview
1m
SSDT + Wizard Deployment
1m
Demo: Deploying a Project from SSDT
2m
ISPAC + Wizard Deployment
1m
Demo: Deploying a Project from an ISPAC File
2m
ISPAC + T-SQL Deployment
1m
Demo: Using T-SQL to Deploy a Project from an ISPAC File
2m
Demo: Restoring a Prior Project Version
1m
Environments
2m
Demo: Configuring Environments
2m
Package Execution Overview
1m
Package Execution with User Interface
1m
Demo: Executing a Package with a User Interface
7m
Package Execution with T-SQL
2m
Demo: Executing a Package with T-SQL
3m
Package Execution with DTExec
2m
Demo: Executing a Package with DTExec
1m
Package Execution with SQL Server Agent
0m
Demo: Creating a SQL Server Agent Job for Package Execution
1m
Data Taps in Package Execution
1m
Demo: Working with Data Taps
3m
Summary
2m
Security
Introduction
1m
Package Security Settings
2m
Package Protection Level: DontSaveSensitive
1m
Demo: Configuring and Testing DontSaveSensitive
2m
Package Protection Level: EncryptSensitiveWithUserKey
1m
Demo: Configuring and Testing EncryptSensitiveWithUserKey
2m
Package Protection Level: EncryptSensitiveWithPassword
1m
Package Protection Level: EncryptAllWithPassword
1m
Demo: Configuring and Testing EncryptAllWithPassword
2m
Package Protection Level: EncryptAllWithUserKey
1m
Package Protection Level: ServerStorage
1m
Digital Signature
1m
Demo: Signing and Verifying a Package in Visual Studio
2m
Demo: Verifying a Package in DTEXECUI
1m
Demo: Configuring the Registry to Verify Signatures
1m
Security for Package Deployment Model
1m
Demo:Configuring Server Storage for the Package Deployment Model
3m
Security for Project Deployment Model
3m
Demo: Configuring Server Storage for the Project Deployment Model
6m
Security for Scheduled Packages
1m
Demo: Configuring a Proxy Account for Scheduled Package Execution
2m
Summary
2m
Special Design Scenarios
Introduction
1m
Working with Headers and Footers in Text Files
1m
Adding Header and Footer Rows: No Data in Header
2m
Demo: Adding Header and Footer Rows with No Data in Header
4m
Adding Header and Footer Rows: Data in Header
1m
Demo: Adding Header and Footer Rows with Data in Header
2m
Eliminating Header and Footer Rows
3m
Demo: Eliminating Header and Footer Rows
4m
XML Processing
1m
Importing XML Data
2m
Demo: Importing XML Data
3m
Validating XML Data
1m
Demo: Validating XML Data
1m
Transforming XML Data
2m
Demo: Transforming XML Data
3m
Summary
3m
Description
Course info
Rating
Level
Intermediate
Updated
Mar 17, 2015
Duration
4h 43m
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
More courses by Stacia Misner Varga