SSIS Tutorial

No	Chapters	Topics	Inline Topics
1	SSIS Tutorial : Basics	Understanding SSIS
		BIDs understanding	Setting the Start-up Page
		Creating new Project
		BIDS Component	Solution Explorer
			The Properties Window
			The SSIS Toolbox
		BIDS Windows	Floating, Dockable or Tabbed Windows
			Docking Windows
			Auto-hiding Docked Windows
2	SSIS Tutorial : Package and tasks	SSIS Package – Basics Functions	Creating New Package
			Rename Package
			Edit Package
			Package Tabs
			Package’s XML Code
			Save a Package
			Close a Packages
		Control Flow Window Features	Zooming In and Out
			Annotating Packages
		Understanding Control Flow Tasks	Adding Control Flow Tasks
			Renaming and Editing Tasks
			Sequencing Tasks
			Selecting and Grouping Tasks
			Ungrouping Tasks
		Creating a Simple Scripting Task
		Running and Stopping Packages	Running a Package
			Stopping a Package
			Debugging a Package – Pros and Cons
			How to Run a Package in Debug Mode
3	SSIS Tutorial : Understanding Data Flow TasK	Sources, Transforms and Destinations	Our Example
		Creating a Project Connection
		Data Flow Tasks	Creating Data Flow Tasks
			Switching to the Data Flow Tab
		Creating the Source	Default Connections and Connection Scope
			Creating Sources
			Creating our OLEDB Source
			Specifying a SQL Command
			Choosing Output Columns
		Creating the Flat File Connection Manager	Step 1 – Ensure you have a Flat File Template
			Step 2 – Starting to Create a Connection to the Flat File
			Step 3 – Configuring the New Connection Manager (General)
			Step 4 – Configuring the Connection Manager’s Columns
			Step 5 – Choose Advanced Settings for the Connection Manager
		Creating and Configuring the Destination	Step 1 - Creating the Destination
			Step 2 - Connecting the Source to the Destination
			Step 3 – Assigning a Connection Manager to the Destination
			Step 4 – Mapping Columns
		Executing the Data Flow Package	Running in 32 bit Mode
4	SSIS Tutorial: Data Viewers	Overview of Data Viewers	Using Union All Transformations for Debugging
		Using Data Viewer
5	SSIS Tutorial : Data Transformations	Our Example
		Synchronous and Asynchronous Transformations	Fully and Partially Blocking Asynchronous Transformations
		Adding Transformations - Overview
		Sorting
		Aggregating Data	Advanced Options – Grouping by Text Fields
			Setting Keys
			Multiple Outputs
		Sampling	Row Sampling
			Percentage Sampling
		Combining Rows (Union All and Merge Transforms)	Example – Merging X Factor Contestants
			The Final Data Flow
			Configuring the Union All Transformation
			The Merge and Merge Join Transformations
6	SSIS Tutorials: Variables	Overview of Variables	Data Types of Variables
		Working with Variables	Displaying the Variables Window
			Creating a Variable
			Scope of Variables
			Choosing which Variables to See
			System Variables
			Showing Extra Variable Information and Properties
		The Row Count Transformation
		Referring to Variables in Script	Choosing which Variables you can use in Script
			Editing the Script
			Running the Package
7	SSIS Tutorials: Data Types and Data Conversion	SQL Server Data Types in SSIS
		SSIS Data Types	String and Binary Data Types
			Date and Time Data Types
			Floating Point Numbers
			Whole Numbers (Integers) and Boolean Data Types
		Example of the Need for Data Conversion	Showing Output Data Types using Advanced Editing
		Data Conversion Transformations	What the End Result Will Look Like
			Creating the Data Conversion Transformation
8	SSIS Tutorials: Strings in SSIS	Expressions Overview	Using the Expression Task
			Using the Expression Builder
		Expression Syntax - General	Operators
			Where’s my IF Function? The Conditional Operator
			Null Substitution – IsNull
			Referring to Variables
		Converting or Casting Data	The Need for Data Conversion
			Using Casting Operators
			Casting Numbers and Text – Additional Arguments Required
			Casting Times
		Date and Time Functions	The DatePart Function
			Adding Dates and Taking the Difference
		Working with Strings	Concatenating Text
			New Line and Other Special Characters
			String Functions in SSIS
9	SSIS Tutorial : Condtional Split and Derived column	Our Example
		Beginning the Example	The Control Flow Tasks
			Starting the Data Flow Task
		The Conditional Split Task
		The Derived Column Task
		Finishing Off the Package	Adding a Union All Task to Recombine Data
			Adding a Data Conversion Task to Change Data Types
10	SSIS Tutorials : Debugging		The Package We’ll Use
			The Variable whose Value we will Monitor
		Setting Breakpoints
		Debugging a Package with Breakpoints Set	Starting to Debug
			Showing the Debug Windows
			The Locals Window
			Watching Variable Values
			Using Quick Watch
			Using Breakpoints
11	SSIS Tutorials : LooK up Transformation
		Starting the Package	Creating a Variable to Hold the Null Id
			Control Flow for the Package
			Starting the Data Flow
		Creating the Lookup Transformation	Step 1 – Choosing the Lookup Table
			Step 2 - Redirecting the Non-Matching Rows
			Step 3 – Choosing a Cache Mode
			Step 4 – Matching Columns
			Step 5 – Sending the Matching and Unmatched Data Out
		Dealing with the Unmatched Records
		Dealing with the Matched Records
		Finishing the Package
		Cache Connection Managers/Cache Transforms	Adding a Cache Transform to Create a Cache File
			Cache Connection Managers
			Creating the Cache Connection Manager
			Finishing Configuration of the Cache Transform
			Using the Cache File in the Package
12	SSIS Tutorial : Basic container	The Task Host and Sequence Containers	Task Host Container
			Sequence Containers
		For Loops	Outline of the Solution
			Variables Used
			Creating the For Loop Task
			Creating the Script Task
			Carrying Variables through to the Script
			Writing the Script Itself
13	SSIS Tutorial : Woringk with files
		Creating the Necessary Variables	The Need to Initialise the File Name Variable
		Creating Foreach Loops	Step 1 – Create the Container
			Step 2 – Choose the Variable to Store Each File name
			Step 3 – Configure the Loop over Files
		Using the File Name Stored	Storing the File Name in a Table
		The File System Task	Step 1 – Creating the File Connection Manager
			Step 2 – Choosing what the File Task should do
			Step 3 – Specifying the Source
			Step 4 – Specifying the Destination
		Setting the Precedence Expression
14	SSIS Tutorial : Other for each loop	The Types of Loop
		Looping Over Items
		Looping Over Rows (ADO)	Step 1 - Creating the Variables
			Step 2 - Creating the Results Set
			Step 3 – Configuring the Foreach Loop
			Step 4 – the Script Task
		Looping Over Schema Components	Step 1 – Creating the Variables Needed
			Step 2 – Creating the Connection
			Step 2 - Creating the Foreach Loop
			Step 3 – the Script to Write Out the User-Defined Tables
		Looping Over XML Nodes
15	SSIS Tutorial : Scripting	Visual Studio Tools for Applications
		Choosing a Programming Language
		Editing Script	Changing the Main Routine Name
			Dealing with Compilation Errors
		Returning Results
		Passing Variables to Script	Our Example
			Accessing Variables in Script
			Method One for Passing Variables
			Method Two for Passing Variables
		Debugging Scripts	Setting and Removing Breakpoints
			Stepping Through Code
			Inspecting Values
16	SSIS Tutorial : Precedence Container
		Creating the Constraints	Using Sequence Containers to Group Tasks
			Creating Precedence Constraints
		Combining Expressions and Constraints
		Multiple Precedence Constraints
17	SSIS Tutorial : Events	Overview of Events	Seeing Events on the Progress Tab
			Our Example
		The List of Events
		Setting Event Handlers	Creating Event Handlers
			Editing and Deleting Event-Handlers
			The Event Handlers for our Example
18	SSIS Tutorial: Error
		Error and Truncation Settings
19	SSIS Tutorial : logging	What Logging Involves
		Logging to Text Files	Step 1 – Create a File Connection
			Step 2 – Configuring Logging for a Project
			Step 3 – Choosing a Logging Provider
			Step 4 – Assigning a File Connection to the Log
			Step 5 – Choosing What to Record
			Step 6 – Choosing which Containers/Tasks to Log
		Other Logging Providers	Logging to SQL Server Table
			The Other Providers
		Catalog Logging
		Audit Transformations	Adding and Configuring the Audit Transformation
			Adding Columns to the Destination Table
20	SSIS Tutorial : Parameters	Overview of Parameters	Our Example
		Creating Parameters	Creating Project Parameters
			Creating Package Parameters
		Referring to Parameters
		Completing the Rest of the Package
21	SSIS Tutoral : Deploying Projects	Deployment Methods
		Preparing to Deploy	Creating a Catalog
			Creating a Folder
		Deploying a Project	Step 1 – Starting Deployment
			Step 2 – Choosing your Destination
			Step 3 – Completing the Wizard
		Executing Packages	Step 1 – Starting to Execute a Package
			Step 2 - Showing an Overview Report
		Executing Packages within SQL
		SSISDB Database and Reports	Running Reports
		Creating Environments for Parameters	The Need for Enviroments
			Creating Environments
			Referencing Environments
			Matching Environments to Parameters
			Executing a Package for an Environment
		Getting Started	Ensuring SQL Server Agent is Running
			Ensuring you have a Deployed Package
		Scheduling Jobs	Adding a Job
			Choosing to Add Steps
			Configuring Steps
			Choosing the Schedule
22	SSIS Tutorial : Scheduling Packages	Testing Jobs
		Viewing the History of Jobs
		Proxy Servers	Step 1 - Creating a Credential
			Step 2 - Creating a Proxy
			Step 3 - Assigning the Proxy to a Job
23	SSIS Tutorial : Package Level Deplyoment	Deploying Packages in Earlier Versions of SSIS	Where Your Packages End Up
			Switching an SSIS 2012 Project to Package-Level Deployment
			Switching Back to Project-Level Deployment
		Deploying Packages	Step 1 - Choosing to Create a Deployment Utility
			Step 2 - Building the Project
			Step 3 - Copying the Build Folder to the Destination Server
			Step 4 - Creating a Folder for the Installed Packages
			Step 5 - Running the Package-Installation Wizard
		Working with Deployed Packages	Running a Deployed Package
			Deleting Packages