NET communities section. Anand has won several prizes at Community-Credit.com
and has been featured as "All Time" contributor at the site. He is one of the founders
of Trivandrum Microsoft Usergroup. He regularly blogs under the banner "I type
what I feel" at http://msmvps.com/blogs/anandn and maintains a personal website
at www.visualanand.net.
Table of Contents
Preface 1
Chapter 1: SSIS Basics 7
SQL Server Integration Services 7
Objects Used in SSIS 9
The SSIS Package 10
The Control Flow Elements 10
Data Flow Components 12
Data Source Components 12
Data Transformation 14
Data Flow Destinations 21
Connection Managers 22
Variables 24
Event Handlers 24
Log Providers 25
Debugging and Diagnostic Features 26
Summary 26
Chapter 2: Creating a BI Project for SSIS in Visual Studio 2005 27
Business Intelligence using Microsoft Products 27
Resources Used for Creating Projects 28
Creating Your First BI Project for SSIS 28
Launching VS 2005 and Creating a BI Project for Integration Services 28
Business Intelligence Project Properties 32
Overview of the Project Window 33
Canvas for Package Design 37
Control Flow 38
Data Flow 39
Event Handlers 41
Package Explorer 43
The Toolbox 44
Table of Contents
[ ii ]
The Solution Explorer 48
Getting Various Windows 53
Server Explorer Window 54
Bookmark Window 55
Class View and Code Definition Windows 56
Object Browser 56
Error List Window 58
Output Window 58
Properties Window 58
Tasks List Window 60
Toolbox Window 60
Find Results Window 60
Other Windows 63
Debug Windows 65
BI Related Items in Tools/Options 66
Property Pages and Folders of the Project 67
Executing the Package and Saving the Project 68
Hands-On Exercises 69
Hands-On Exercise 1 69
Hands-On Exercise 2 69
Hands-On Exercise 3 72
Summary 73
Chapter 3: Sending Email with a SSIS Package 75
Hands-On Exercise One: Sending an Email Using the SMTP Server 75
Hands-On Exercise Two: How to Find Your ISP's SMTP Server? 84
Summary 85
Chapter 4: Transferring Data to a Text File 87
Hands-On Exercise: Transferring Data to a Text File 87
Step 1: Creating a BI Project and Adding a Data Flow Task 88
Step 2: Adding Connection Manager for the DataReader 88
Step 3: Configuring the Source 90
Step 4: Adding a Flat File Destination and Establishing a Path from
DataReader Source 93
Step 5: Configuring the Flat File Destination Component 94
Step 6: Build and Execute the Package 97
Summary 98
Chapter 5: Transferring Data to a Microsoft Excel File 99
Hands-On Exercise: Transferring Data to an Excel File 99
Step 1: Creating a BI Project and Adding a Data Flow Task 100
Step 2: Configuring the DataReader's Connection Manager 100
Step 3: Configuring the DataReader Source 101
Step 4: Adding a Character Map Transformation 102
Table of Contents
[ iii ]
Step 5: Adding an Excel Destination and Establishing a
Path to It from the Character Map Data Flow Component 106
Step 6: Configuring the Excel Destination Component 107
Step 7: Testing the Package 110
Summary 110
Chapter 6: Data Transfer to an MS Access Database 111
Hands-On Exercise: Transferring Data to an Access Database 111
Step 1: Creating a BI Project and Adding a Data Flow Task 112
Step 2: Configuring the DataReader's Connection Manager 112
Step 3: Configuring the DataReader Source 112
Step 4: Adding an OLE DB Destination and Establishing a
Path from the DataReader Component 113
Step 5: Configuring the OLE DB Destination Component 114
Step 6: Incorporating a Data Viewer to Monitor Data Flow 119
Summary 122
Chapter 7: Data Transfer from a Text File Using the
Bulk Insert Task 123
Hands-On Exercise: Transferring Data from a
Flat File to a SQL Server Database Table 123
Step 1: Use / create a Flat Text File whose Contents
Need to be Transferred 124
Step 2: Create a Table with Columns that Can Accept the
Contents of the File Created 124
Step 3: Create a BI Project and Add a Bulk Insert Task 126
Step 4: Configure the Bulk Insert Task 126
Step 5: Build and Execute the Package 131
What Happens if there Is an Error? 132
Summary 133
Chapter 8: Using a Conditional Split Data Transformation 135
Hands-On Exercise: Splitting Data Retrieved from a SQL Server 135
Step 1: Create a BI Project and Add a Data Flow Task.
Pages:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25