Integration Services courseware and training manuals

We have introductory and advanced SSIS courseware on our website:

You can see details of how to license our Integration Services courseware (including volume requirements) here.


Integration Services courseware

Here is a list of all of our Integration Services manual chapters (you can download the ones marked):

Chapter Sections Subsections
Introducing SSIS (2 pages) Overview of SSIS SSIS Packages and Solution Explorer ; Control Flow ; Data Flow
Using Visual Studio (4 pages) Starting Visual Studio Visual Studio and SQL Server Data Tools ; Running Visual Studio
Creating a Project
Visual Studio Windows The Solution Explorer, Properties and SSIS Toolbox Windows ; Floating Windows ; Auto-hiding Windows
Packages (7 pages)

Download this chapter
Working with Packages Creating a Package ; Renaming Packages ; Opening or Editing Packages ; Viewing a Package’s XML ; Saving a Package ; Closing Individual Packages ; Closing Multiple Packages ; Copying Packages
The Package Tabs
Running a Package Executing a Single Package ; Making a Package the Start-up Object and Debugging a Project ; Monitoring Package Execution ; Using the Progress Window
Stopping Running a Package
Tasks (8 pages) Working with Tasks Adding a Task ; Renaming a Task ; Editing a Task ; Executing an Individual Task ; Deleting and Suspending (Disabling) Tasks
Working with Task Diagrams Selecting Tasks ; Moving Tasks ; Auto-layout of Diagrams ; Zooming In and Out
Annotations
Grouping Tasks Grouping Tasks ; Grouping using Sequence Containers
Data Flow (6 pages) Introducing Data Flow Sources, Transforms and Destinations ; This and Subsequent Chapters
Data Flow Tasks Creating Data Flow Tasks ; Switching to Data Flow Tasks
Connections Types of Connection ; Project-Level and Package-Level Connections ; Creating Connections in Advance ; Creating Connections as Part of Sources/Destinations
Creating Our Package
The Advanced Editor
Working with SQL Server (6 pages) Creating a SQL Server Connection
Creating a SQL Server Source/Destination Step 1 – Creating the SQL Server Source/Destination ; Step 2 – Choosing the Table or Query ; Step 3 – Mapping Columns
Creating SQL Server Tables
Execute SQL Tasks Step 1 - Adding an Execute SQL Task ; Step 2 – Assign a Connection ; Step 3 – Enter a SQL Statement ; Step 4 – Optionally, Parse your SQL Command
Working with Flat Files (11 pages) Examples for this Chapter
Existing Flat File - Source Connecting to a Flat File ; Configuring Rows and Columns ; Creating the Flat File Source
Existing Flat File – Destination
Creating New Flat Files Step 1 – Creating a Destination ; Step 2 – Starting a New Connection ; Step 3 – Choose a File Format ; Step 4 – Browsing to a Folder ; Step 5 – Choosing a File Name ; Step 6 – Configuring the New File (Rows) ; Step 7 – Configure the New File (Columns) ; Step 8 – Configuring Mappings ; Step 9 – Running your Package
Using Multiple Flat Files Our Example ; Adding the MultiFlatFile Connector ; Using a MultiFlatFile Connector
Working with Excel (5 pages) Excel Workbooks – Overview The Example Used in this Chapter ; What Can Go Wrong
Importing/Exporting using Excel Step 1 – Creating an Excel Connection ; Step 2 – Creating a Source or Destination ; Step 3 – Mapping Columns
The 32-Bit Problem The Problem ; The Solution
Data Viewers (2 pages) Using Data Viewers
Creating Data Viewers The Data Viewer Buffer
Transforms (7 pages) Adding Transforms
Sorting / Getting Unique Rows
Sampling The Sampling Transforms ; Incorporating Sampling Transforms into Data Flow
Combining and Splitting Data Introduction to the Transforms ; An Example using Multicast and Union All Transforms ; Configuring Union All Transforms
Aggregating Data
Data Types (3 pages) The Data Types in SSIS The Three Data Type Families in SSIS ; Data Types Listed in this Chapter
SSIS Data Types String Data Types ; Integer Data Types ; Boolean or Logical Data Types ; Non-Integer Data Types ; Date/time Data Types ; Time Data Types
Data Conversion Transforms (4 pages) Overview of Data Conversion
Creating Data Conversion Transforms Our Example: Importing Excel Unicode Data to Varchar Columns ; Adding a Data Conversion Transform ; Configuring a Data Conversion Transform ; Mapping Data Conversion Transform Columns ; The Complete Package
Variables (4 pages) Overview of Variables Data Types of Variables
Working with Variables The Variables Window ; Creating a Variable ; Deleting Variables ; Changing the Scope of Variables ; Choosing What to See
User and System Variables
Using Variables (5 pages) Example for this Chapter Creating the Variables Needed
Creating Row Count Transforms Adding a Row Count Transform
Creating Expression Tasks
Debugging Setting and Removing Breakpoints ; Debugging using Breakpoints
Derived Column Transforms (8 pages) Overview of Derived Column Transforms Our Example – Calculating Film Length Statistics ; Adding a Derived Column Transform
Simple Column Expressions
Concatenating, Casting and Conditions Simple Concatenation Won’t Work ; Casting ; The Ternary or Conditional Operator ; The Double = Sign When Testing Conditions ; The Relational Operators Allowed
More Examples and Syntax Example of a Mathematical Function ; Dealing with Nulls ; Example of a String Function
SSIS Functions (5 pages) Working with Strings of Text Functions to Find and Replace Text ; Getting Extracts from a String of Text ; Other Text Functions ; New Line and Other Special Characters
Date Functions The DATEPART Function ; Using DATEADD to Add Dates ; Using DATEDIFF to Take the Difference between Two Dates
Casting Data
Error-Handling (3 pages) Our Example
Configuring Error-Handling
Tracking Error Rows
Conditional Splits (4 pages) Our Example
Creating a Conditional Split Beginning a Conditional Split ; The Operators Allowed ; Typing Conditions ; Spotting Errors (Red Text)
Directing Conditional Split Output
Lookup Transforms (12 pages) Our Example
Creating a Lookup Transform Step 1 – Creating the Transform ; Step 2 – Choose the Lookup Table ; Step 3 – Redirect Non-Matching Rows ; Step 4 – Choosing a Cache Mode ; Step 5 – Choosing Columns to Output ; Step 6 – Choosing Columns to Capture
Dealing with Matched Rows
Dealing with Unmatched Rows Strategy 1 – Store Unmatched Rows for Inspection ; Strategy 2 – Add Unmatched Shop Names as Unknown ; Strategy 3 – Add Unmatched Rows to the Lookup Table
Caching Lookup Tables Benefits of using Cache Transforms ; Our Example – Looking Up Property Types ; Filling the Cache ; Using the Cache
File System Tasks (4 pages) Adding File System Tasks
Working with Files Creating a File Connection
Working with Folders Creating a Folder Connection ; Case Study: Creating a Folder
Precedence Constraints (4 pages) Simple Constraints
Expression Constraints Automatically Annotating Expression Constraints
Combining Constraints
Looping Over Files (11 pages) Our Example
Setting up the Package Emptying the SQL Server Table ; Creating a String Variable to Hold Each File
Looping Over Files Step 1 – Adding the Foreach Loop Container Task ; Step 2 – Configuring the Loop Task ; Step 3 – Capturing the File Path in a Variable ; Step 4 – Checking the Package Works
Omitting Certain Files
The Data Import Task The Steps to Follow ; Step 1 – Create an Excel Connection ; Step 2 – Make this Excel Connection Dynamic ; Step 3 – Initialise the File Path Variable Value ; Step 4 – Configure and Finish the Data Flow Task
Running the Package
Looping over ADO Rows (12 pages) How ADO Enumerators Work
Generating Multiple Files from a Single Table The Details of this Example ; Summary of the Steps to Follow ; Step 1 – Creating the Variables ; Step 2 – Create an Execute SQL Task to get Unique Shop Names ; Step 3 – Store the Results Set in the Object Variable ; Step 4 – Create the ADO Enumerator Loop ; Step 5 – Configure the ADO Enumerator Loop ; Step 6 – Add a Fixed Data Flow Task ; Step 7 – Make the Flat File Connection Dynamic ; Step 8 – Run your Package
Importing a Workbook’s Worksheets Creating the Variable ; Create the Outline Package ; The Foreach Loop Type – Creating a Connection ; Choosing to Return the Workbook’s Worksheet Names ; Creating the Data Flow Task (Source) ; Adding a Data Flow Destination ; Running the Package
SQL Parameters (5 pages) Passing Parameters by Position Our Example ; Creating the Variables ; Creating the Loop over Files ; Creating the Execute SQL Task using Parameters ; The Parameter Mapping Choices ; Running the Package
Passing Parameters by Name Creating the Stored Procedure ; Changing the Execute SQL Task
Merge Joins (5 pages) What Merge Joins Do
How Merge Joins Work
Creating the Package Step 1 – Loading the Data ; Step 2 – Sorting the Data ; Step 3 – Merging the Data ; Step 4 – Picking out the Differences ; Step 5 – Finishing the Package
Deployment (4 pages)

Download this chapter
Overview of Deployment
Preparing to Deploy Creating a Catalog ; Creating Folders
Deploying Projects and Packages Starting Project Deployment ; Finishing Deployment ; Deploying Individual Packages
Executing Deployed Packages (6 pages) Executing Reports from the SSMS Menu
Viewing Reports Viewing Package Reports ; Viewing Catalog Reports
Executing Packages from SQL
Scheduling Packages Step 1 - Run SQL Server Agent ; Step 2 – Add a Job ; Step 3 – Adding Steps to the Job ; Step 4 – Choosing a Schedule ; Step 5 – Test your Job
Parameters (7 pages) Overview of Parameters Our Example ; Variables and Parameters Needed
Creating Parameters Creating Project-Level Parameters ; Creating Package-Level Parameters
Setting up the Example The Basic Control Flow Tasks ; Starting to Configure the Loop over Files ; Making the Loop Folder Dynamic ; The Data Flow Task Excel Source ; Finishing the Data Flow Task ; Running the Package
Deploying Packages using Parameters Deploying Package-Level Parameters ; Deploying Project-Level Parameters
Running Deployed Packages with Parameters

Advanced SSIS courseware

Here is a list of all of our Advanced SSIS manual chapters (you can download the ones marked):

Chapter Sections Subsections
Script Tasks (12 pages) Overview
Adding a Script Task Step 1 – Creating the Script Task ; Step 2 – Choose a Language ; Step 3 – Choosing the Start Program Name ; Step 4 – Editing your Script ; Step 5 – Understanding (and Tidying Up) the Code Generated ; Step 6 – Writing your Program ; Step 7 – Correcting any Errors ; Step 8 – Saving and Running your Code
Passing Variables to Script Tasks Passing the Variables to your Script ; Referencing Variables in Script ; A Short-Cut for Experienced Programmers ; The Most Common Mistake with Variables
Debugging Script Setting and Removing Breakpoints ; Removing All Breakpoints ; Stepping through Code ; Displaying Variable Values
Programming (Variables) (11 pages) Why Use Variables in Script?
Declaring Variables Declaring Variables ; Creating Nullable Variables ; Using Modified Hungarian Notation ; Default Values for Variables ; Problems with Declaring Variables within Clauses
Setting Values in Variables Declaring Integer Variables and Adding/Subtracting ; Accumulating Text in String Variables
Variable Data Types Mapping C# Data Types to the CLR Runtime ; A Lazy Person’s Data Types ; Logical Values ; Integers ; Decimal (Floating Point) Numbers ; Strings and Text ; Dates and Times ; Objects
Converting Variables Conversion Using Convert.To ; ToString() – Special Case for String Conversions ; Casting Data Types
Notes on Working with Specific Data Types Working with Characters ; Working with Strings ; Escape Characters ; Verbatim Strings ; Splitting Strings ; Formatting Dates ; Working with Numbers – Possible Operations
Programming (conditions) (5 pages) Using IF for Conditions
Operators
The SWITCH statement The Syntax of SWITCH ; Limitations of SWITCH
Ternary and Coalesce Operators The Conditional Operator ; The Null Coalesce Operator
Programming (Loops) (3 pages) Looping in C# Looping a Given Number of Times ; Looping While a Condition is True (While/Do)
Breaking Out of Loops
Script Components (10 pages)

Download this chapter
Our Example
Starting the Package Removing Old Rows ; Creating a Flat File Data Connection ; Creating a Data Flow to Import Data
Creating your Script Component Step 1 – Add the Component ; Step 2 – Set the Columns Feeding into the Script Component ; Step 3 – Set the Columns Coming out of the Script Component ; Step 4 – Starting to Create Script ; Step 5 – Writing the Script Itself
Finishing the Package The Conditional Split Task ; The Valid Data Destination ; The Invalid Data Destination
Scripting Files and Folders (8 pages) Solutions Covered
Checking a Folder Exists Step 1 – Create the Variables ; Step 2 – Create the Script Task ; Step 3 – Tidy your Code and Reference System.IO ; Step 4 – Get a Reference to the Folder ; Step 5 – Creating the Expression Constraints
Checking a File Exists Step 1 – Create Two Versions of the Source Workbook ; Step 2 – Create the Variables that you will Need ; Step 3 – Create a Static Package ; Step 4 – Create a Script Task ; Step 5 – Write your Script ; Step 6 – Create Two Expression Constraints
Sending Mail (3 pages) The Send Mail Task
Sending Mail in Script Creating and Passing Variable Values ; Referencing the Mail Namespace ; Writing your Script
Events (4 pages) Overview of Events Seeing Events on the Progress Tab ; Our Example
The List of Events
Using Event-Handlers Creating (and Deleting) Event-Handlers ; The Event-Handlers for our Example
Audit Transforms (2 pages) Using Audit Transforms What Audit Transforms Do ; Adding Audit Transforms ; Choosing Columns to Include ; Running an Audit Transform
Logging (5 pages) Overview of Logging Showing the SYSSSISLOG Table in SQL Server ; A Typical Text Log
Starting to Log Beginning a Log ; Choosing a Logging Provider ; Specific Notes on Creating a SQL Server Log ; Specific Notes on Creating a Text File Log
Configuring your Log Choosing which Tasks to Log ; Choosing which Events to Log
Catalog Logging

Notes:

  1. Since Wise Owl print courseware double-sided to save trees, the number of physical pages in each chapter is roughly half of the number of sides quoted.
  2. Downloads are provided as examples only, and should not be used or distributed in any way or form without the prior written permission of Wise Owl.
This page has 0 threads Add post