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.
What you'll learn
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.
Table of contents
- 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
- 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
- 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
- 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
- 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
- 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
About the author
Stacia Misner Varga is an instructor, author, and principal consultant of Data Inspirations. Her career spans more than 30 years, with a focus on improving business practices through technology. Since 2000, she has provided consulting and education services for Microsoft’s data platform and authored or co-authored many books covering this topic. In addition, Stacia has been a frequent speaker over the years at technology conferences worldwide.