Introductory Integration Services (SSIS)

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