This page has 0 threads | Add post

Courseware

Fast-track SSIS course manual

Here are the chapters included in this course manual (we use exactly the same manuals for our classroom and online Integration Services courses).

Click here to download a sample of the first two chapters of this Fast-track SSIS manual, or review how to license our courseware.

Chapter 1 - INTRODUCING SSIS▼

Section	Title	Subsections	Page
1.1	Overview of SSIS	SSIS Packages and Solution Explorer Control Flow Data Flow	9

Chapter 2 - USING VISUAL STUDIO▼

Section	Title	Subsections	Page
2.1	Starting Visual Studio	Visual Studio and SQL Server Data Tools Running Visual Studio	11
2.2	Creating a Project		12
2.3	Visual Studio Windows	The Solution Explorer, Properties and SSIS Toolbox Windows Floating Windows Auto-hiding Windows	13

Chapter 3 - PACKAGES▼

Section	Title	Subsections	Page
3.1	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	15
3.2	The Package Tabs		18
3.3	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	19
3.4	Stopping Running a Package		21

Chapter 4 - TASKS▼

Section	Title	Subsections	Page
4.1	Working with Tasks	Adding a Task Renaming a Task Editing a Task Executing an Individual Task Deleting and Suspending (Disabling) Tasks	22
4.2	Working with Task Diagrams	Selecting Tasks Moving Tasks Auto-layout of Diagrams Zooming In and Out	25
4.3	Annotations		27
4.4	Grouping Tasks	Grouping Tasks Grouping using Sequence Containers	28

Chapter 5 - DATA FLOW▼

Section	Title	Subsections	Page
5.1	Introducing Data Flow	Sources, Transforms and Destinations This and Subsequent Chapters	30
5.2	Data Flow Tasks	Creating Data Flow Tasks Switching to Data Flow Tasks	31
5.3	Connections	Types of Connection Project-Level and Package-Level Connections Creating Connections in Advance Creating Connections as Part of Sources/Destinations	32
5.4	Creating Our Package		34
5.5	The Advanced Editor		35

Chapter 6 - WORKING WITH SQL SERVER▼

Section	Title	Subsections	Page
6.1	Creating a SQL Server Connection		36
6.2	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	37
6.3	Creating SQL Server Tables		39
6.4	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	40

Chapter 7 - WORKING WITH FLAT FILES▼

Section	Title	Subsections	Page
7.1	Examples for this Chapter		42
7.2	Existing Flat File - Source	Connecting to a Flat File Configuring Rows and Columns Creating the Flat File Source	43
7.3	Existing Flat File – Destination		45
7.4	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	45
7.5	Using Multiple Flat Files	Our Example Adding the MultiFlatFile Connector Using a MultiFlatFile Connector	50

Chapter 8 - WORKING WITH EXCEL▼

Section	Title	Subsections	Page
8.1	Excel Workbooks – Overview	The Example Used in this Chapter What Can Go Wrong	53
8.2	Importing/Exporting using Excel	Step 1 – Creating an Excel Connection Step 2 – Creating a Source or Destination Step 3 – Mapping Columns	54
8.3	The 32-Bit Problem	The Problem The Solution	56

Chapter 9 - DATA VIEWERS▼

Section	Title	Subsections	Page
9.1	Using Data Viewers		58
9.2	Creating Data Viewers	The Data Viewer Buffer	59

Chapter 10 - TRANSFORMS▼

Section	Title	Subsections	Page
10.1	Adding Transforms		60
10.2	Sorting / Getting Unique Rows		61
10.3	Sampling	The Sampling Transforms Incorporating Sampling Transforms into Data Flow	62
10.4	Combining and Splitting Data	Introduction to the Transforms An Example using Multicast and Union All Transforms Configuring Union All Transforms	64
10.5	Aggregating Data		66

Chapter 11 - DATA TYPES▼

Section	Title	Subsections	Page
11.1	The Data Types in SSIS	The Three Data Type Families in SSIS Data Types Listed in this Chapter	67
11.2	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	68

Chapter 12 - DATA CONVERSION TRANSFORMS▼

Section	Title	Subsections	Page
12.1	Overview of Data Conversion		70
12.2	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	71

Chapter 13 - VARIABLES▼

Section	Title	Subsections	Page
13.1	Overview of Variables	bles	74
13.2	Working with Variables	The Variables Window Creating a Variable Deleting Variables Changing the Scope of Variables Choosing What to See	75
13.3	User and System Variables		77

Chapter 14 - USING VARIABLES▼

Section	Title	Subsections	Page
14.1	Example for this Chapter	Creating the Variables Needed	78
14.2	Creating Row Count Transforms	Adding a Row Count Transform	79
14.3	Creating Expression Tasks		80
14.4	Debugging	Setting and Removing Breakpoints Debugging using Breakpoints	81

Chapter 15 - DERIVED COLUMN TRANSFORMS▼

Section	Title	Subsections	Page
15.1	Overview of Derived Column Transforms	Our Example – Calculating Film Length Statistics Adding a Derived Column Transform	83
15.2	Simple Column Expressions		84
15.3	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	84
15.4	More Examples and Syntax	Example of a Mathematical Function Dealing with Nulls Example of a String Function	89

Chapter 16 - SSIS FUNCTIONS▼

Section	Title	Subsections	Page
16.1	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	91
16.2	Date Functions	The DATEPART Function Using DATEADD to Add Dates Using DATEDIFF to Take the Difference between Two Dates	93
16.3	Casting Data		95

Chapter 17 - ERROR-HANDLING▼

Section	Title	Page
17.1	Our Example	96
17.2	Configuring Error-Handling	97
17.3	Tracking Error Rows	98

Chapter 18 - CONDITIONAL SPLITS▼

Section	Title	Subsections	Page
18.1	Our Example		99
18.2	Creating a Conditional Split	Beginning a Conditional Split The Operators Allowed Typing Conditions Spotting Errors (Red Text)	100
18.3	Directing Conditional Split Output		102

Chapter 19 - LOOKUP TRANSFORMS▼

Section	Title	Subsections	Page
19.1	Our Example		103
19.2	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	104
19.3	Dealing with Matched Rows		108
19.4	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	109
19.5	Caching Lookup Tables	Benefits of using Cache Transforms Our Example – Looking Up Property Types Filling the Cache Using the Cache	112

Chapter 20 - FILE SYSTEM TASKS▼

Section	Title	Subsections	Page
20.1	Adding File System Tasks		115
20.2	Working with Files	Creating a File Connection	116
20.3	Working with Folders	Creating a Folder Connection Case Study: Creating a Folder	117

Chapter 21 - PRECEDENCE CONSTRAINTS▼

Section	Title	Subsections	Page
21.1	Simple Constraints		119
21.2	Expression Constraints	Automatically Annotating Expression Constraints	120
21.3	Combining Constraints		122

Chapter 22 - LOOPING OVER FILES▼

Section	Title	Subsections	Page
22.1	Our Example		123
22.2	Setting up the Package	Emptying the SQL Server Table Creating a String Variable to Hold Each File	124
22.3	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	125
22.4	Omitting Certain Files		128
22.5	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	129
22.6	Running the Package		133

Chapter 23 - LOOPING OVER ADO ROWS▼

Section	Title	Subsections	Page
23.1	How ADO Enumerators Work		134
23.2	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	135
23.3	Importing a Workbook’s Worksheets	The Underlying Principle 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	141

Chapter 24 - SQL PARAMETERS▼

Section	Title	Subsections	Page
24.1	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	146
24.2	Passing Parameters by Name	Creating the Stored Procedure Changing the Execute SQL Task	150

Chapter 25 - MERGE JOINS▼

Section	Title	Subsections	Page
25.1	What Merge Joins Do		151
25.2	How Merge Joins Work		152
25.3	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	153

Chapter 26 - DEPLOYMENT▼

Section	Title	Subsections	Page
26.1	Overview of Deployment		156
26.2	Preparing to Deploy	Creating a Catalog Creating Folders	157
26.3	Deploying Projects and Packages	Starting Project Deployment Finishing Deployment Deploying Individual Packages	158

Chapter 27 - EXECUTING DEPLOYED PACKAGES▼

Section	Title	Subsections	Page
27.1	Executing Reports from the SSMS Menu		160
27.2	Viewing Reports	Viewing Package Reports Viewing Catalog Reports	161
27.3	Executing Packages from SQL		162
27.4	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	163

Chapter 28 - PARAMETERS▼

Section	Title	Subsections	Page
28.1	Overview of Parameters	Our Example Variables and Parameters Needed	166
28.2	Creating Parameters	Creating Project-Level Parameters Creating Package-Level Parameters	167
28.3	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	168
28.4	Deploying Packages using Parameters	Deploying Package-Level Parameters Deploying Project-Level Parameters	171
28.5	Running Deployed Packages with Parameters		172

Chapter 29 - SCRIPT TASKS▼

Section	Title	Subsections	Page
29.1	Overview		173
29.2	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	174
29.3	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	179
29.4	Debugging Script	Setting and Removing Breakpoints Removing All Breakpoints Stepping through Code Displaying Variable Values	182

Chapter 30 - PROGRAMMING (VARIABLES)▼

Section	Title	Subsections	Page
30.1	Why Use Variables in Script?		185
30.2	Declaring Variables	Declaring Variables Creating Nullable Variables Using Modified Hungarian Notation Default Values for Variables Problems with Declaring Variables within Clauses	185
30.3	Setting Values in Variables	Declaring Integer Variables and Adding/Subtracting Accumulating Text in String Variables	187
30.4	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	188
30.5	Converting Variables	Conversion Using Convert.To ToString() – Special Case for String Conversions Casting Data Types	191
30.6	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	193

Chapter 31 - CONDITIONS▼

Section	Title	Subsections	Page
31.1	Using IF for Conditions		196
31.2	Operators		196
31.3	The SWITCH statement	The Syntax of SWITCH Limitations of SWITCH	197
31.4	Ternary and Coalesce Operators	The Conditional Operator The Null Coalesce Operator	199

Chapter 32 - LOOPS▼

Section	Title	Subsections	Page
32.1	Looping in C#	Looping a Given Number of Times Looping While a Condition is True (While/Do)	201
32.2	Breaking Out of Loops		202

Chapter 33 - SCRIPT COMPONENTS▼

Section	Title	Subsections	Page
33.1	Our Example		204
33.2	Starting the Package	Removing Old Rows Creating a Flat File Data Connection Creating a Data Flow to Import Data	205
33.3	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	207
33.4	Finishing the Package	The Conditional Split Task The Valid Data Destination The Invalid Data Destination	212

Chapter 34 - SCRIPTING FILES AND FOLDERS▼

Section	Title	Subsections	Page
34.1	Solutions Covered		214
34.2	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	215
34.3	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	218

Chapter 35 - SENDING MAIL▼

Section	Title	Subsections	Page
35.1	The Send Mail Task		222
35.2	Sending Mail in Script	Creating and Passing Variable Values Referencing the Mail Namespace Writing your Script	223

Chapter 36 - EVENTS▼

Section	Title	Subsections	Page
36.1	Overview of Events	Seeing Events on the Progress Tab Our Example	225
36.2	The List of Events		226
36.3	Using Event-Handlers	Creating (and Deleting) Event-Handlers The Event-Handlers for our Example	227

Chapter 37 - AUDIT TRANSFORMS▼

Section	Title	Subsections	Page
37.1	Using Audit Transforms	What Audit Transforms Do Adding Audit Transforms Choosing Columns to Include Running an Audit Transform	229

Chapter 38 - LOGGING▼

Section	Title	Subsections	Page
38.1	Overview of Logging	Showing the SYSSSISLOG Table in SQL Server A Typical Text Log	231
38.2	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	232
38.3	Configuring your Log	Choosing which Tasks to Log Choosing which Events to Log	234
38.4	Catalog Logging		235

If you book a place on one of our courses you will automatically get access to the relevant courseware manual online, and also have the option to receive a paper copy of it.

This page has 0 threads

Add a new post

Choose software

SSIS course manuals