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

No comments:

Post a Comment