Course Code: 19272

Introductory Integration Services (SSIS)

Class Dates:
11/21/2022
Length:
3 Days
Cost:
$1495.00
Class Time:
Technology:
Server
Delivery:
Instructor-Led Training, Virtual Instructor-Led Training

Overview

  • Course Overview
  • his course is designed to help you understand all aspects of SQL Server Integration Services (SSIS), the ETL tool of choice for SQL Server users. We will use a variety of lab exercises and small projects to ensure you gain familiarity with how the design and execution of environments work, and prepare you to build your own real world applications using this SQL Server application. Students will learn real world uses for SSIS.

    WHAT YOU'LL LEARN: How to create an SSIS package to load to a database
    How to make dynamic packages that work and self-heal in the event of a failure
    How to use SSIS in your environment
  • Audience
  • This course is intended for database professionals who need to create and support a data warehousing solution. Primary responsibilities include:

    - Implementing a data warehouse
    - Developing SSIS packages for data extraction, transformation, and loading.

Prerequisites

  • At least 2 years' experience working with relational databases, including:
    - Designing a noramalized database
    - Creating tables and relationships
    - Querying with Transact-SQL
    - Some exposure to basic programming constructs (such as looping and branching)
    - An awareness of key business priorities such as revenue, profitability, and financial accouting

Course Details

  • Introduction SSIS
  • Overview of SSIS
  • SSIS Packages and Solution Explore
  • Control Flow
  • Data Flow
  • Using Visual Studio
  • 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
  • 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 - Working with Tasks
  • Adding a Task
  • Renaming a Task
  • Editing a Task
  • Executing an Individual Task
  • Deleting and Suspending (Disabling) Tasks
  • TASKS - Working with Task Diagrams Annotations
  • Selecting Tasks
  • Moving Tasks
  • Auto-layout of Diagrams
  • Zooming In and Out
  • Grouping Tasks, Grouping using Sequence Containers
  • 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
  • Working with SQL Server
  • The Advanced Editor
  • Creating a SQL Server Connection
  • Creating a SQL Server, 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 - Existing Flat File - Source
  • Connecting to a Flat File
  • Configuring Rows and Columns
  • Creating the Flat File Source
  • Existing Flat File – Destination
  • Working with Flat Files - 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
  • Excel Workbooks – Overview, The Example Used in this lesson ; What Can Go Wrong
  • Working with Excel - Importing/Exporting using Excel and Data Viewers
  • 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
  • Using Data Viewers
  • Creating Data Viewers
  • Adding Transforms, Sorting / Getting Unique Rows
  • 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
  • Data Types - Aggregating Data, The Data Types in SSIS & SSIS Data Types
  • The Three Data Type Families in SSIS
  • Data Types Listed in this Chapter
  • String Data Types, Integer Data Types
  • Boolean or Logical Data Types, Non-Integer Data Types
  • Date/time Data Types ; Time Data Types
  • Variables - 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
  • Using Variables - Overview of Variables & Working with Variables
  • Data Types of Variables
  • The Variables Window, Creating a Variable, Deleting Variables
  • Changing the Scope of Variables ; Choosing What to See
  • User and System Variables
  • 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
  • Overview of Derived Column Transforms
  • Our Example – Calculating Film Length Statistics ; Adding a Derived Column Transform
  • Simple Column Expressions
  • Derived Column Transforms - 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
  • 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
  • Our Example, Configuring Error-Handling
  • Tracking Error Rows
  • Conditional Splits - Creating a Conditional Split
  • Beginning a Conditional Split
  • The Operators Allowed, Spotting Errors (Red Text)
  • Directing Conditional Split Output
  • 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
  • File System Tasks & Precedence Constraints- Adding File System Tasks
  • Working with Files - Creating a File Connection
  • Working with Files
  • Working with Folders, Creating a Folder Connection ; Case Study: Creating a Folder
  • Simple Constraints
  • Expression Constraints, Automatically Annotating Expression Constraints
  • Combining Constraints
  • 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
  • Looping Over Files & Looping over ADO Rows- 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
  • 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 - 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
  • 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 - Overview of Deployment
  • Preparing to Deploy, Creating a Catalog ; Creating Folders
  • Deploying Projects and Packages, Starting Project Deployment ; Finishing Deployment ; Deploying Individual Packages
  • 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
  • Overview of Parameters, Our Example ; Variables and Parameters Needed
  • Creating Parameters, Creating Project-Level Parameters ; Creating Package-Level Parameters
  • Executing Deployed Pacages
  • 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
  • Parameters - Deploying Packages using Parameters
  • Deploying Package-Level Parameters
  • Deploying Project-Level Parameters
  • Running Deployed Packages with Parameters
  • Script Tasks
  • 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
  • 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, Converting Variables, Notes on Working with Specific Data Types
  • Using IF for Conditions
  • Programming
  • Using IF for Conditions
  • Operators
  • The SWITCH statement
  • Ternary and Coalesce Operators
  • Looping in C#
  • Breaking Out of Loops
  • Starting the Package
  • Script Components
  • Starting the Package
  • Creating your Script Component
  • Finishing the Package
  • Solutions Covered
  • Checking a Folder Exists
  • Scripting Files and Folders
  • Checking a File Exists
  • Sending Mail, Sending Mail in Script
  • Events, Overview of Events, The List of Events, Using Event-Handlers
  • Audit Transforms, Using Audit Transforms
  • Overview of Logging, Starting to Log
  • Logging, Configuring your Log, Catalog Logging