Add and
Configure the DataReader Source to Pull Data from the Local SQL Server 136
Step 2: Add a Conditional Split Transformation 137
Step 3: Establish a Path to Connect DataReader Source with
the Conditional Split Data Transformation 137
Step 4: Configure the Conditional Split Data Transformation 137
Step 5: Add Recordset Destination(s) 140
Step 6: Configure the Recordset Destination(s) 141
Step 7: Build, Execute the Package and Review 145
Summary 145
Table of Contents
[ iv ]
Chapter 9: Using an Aggregate Data Transformation 147
Hands-On Exercise: Using Aggregate Data Flow Transformation 148
Step 1: Create a BI Project and Add a Data Flow Task. Add and
Configure the DataReader Source to Pull Data from the Local SQL Server 149
Step 2: Add an Aggregate Data Transformation 149
Step 3: Establish a Path to Connect DataReader Source with
the Aggregate Data Transformation 150
Step 4: Configure the Aggregate Data Flow Transformation 150
Step 5: Add a Percentage Sampling Data Transformation 153
Step 6: Establish a Path from Aggregate Data Transformation to the
Percentage Sampling Data Transformation 153
Step 7: Configure the Percentage Sampling Data Flow Item 154
Step 8: Add a Recordset Destination Data Flow Component 155
Step 9: Configure the Recordset Destination Data Flow Component 155
Step 10: Build and Execute the Package, and Review Results 156
Summary 158
Chapter 10: Using a Data Conversion Data Flow Transformation 159
Source and Destination for the Exercise 159
Hands-On Exercise: Transferring Data to an Excel File 162
Step 1: Creating a BI Project; Adding a Data Flow Task
and Configuring an Excel Source 162
Step 2: Adding a Data Conversion Data Flow Transformation 165
Step 3: Establishing a Path from Excel Source to
Data Conversion Data Flow Transformation 166
Step 4: Configuring the Data Conversion Data Transformation 167
Step 5: Adding and Configuring an OLE DB Data Destination 168
Adding an OLE DB Destination to Canvas 168
Establish a Path from Data Conversion Data Flow Transformation to OLE DB Data
Destination 168
Setting Up a Connection Manager 169
Displaying the Table to which the Data will be Inserted 170
Column Mappings 171
Handling Data Loading Errors 171
Step 6: Adding and Configuring a Recordset Destination
for Displaying Errors 172
Step 7: Building the Project and Testing the Package 173
Summary 174
Chapter 11: Creating a SSIS Package with an XML Task 175
Diff 175
Merge 176
Table of Contents
[ v ]
Validate 176
XPATH 176
XSLT 176
XML Documents Used in This Chapter 176
Documents Used for Diff XMLTask Type 177
Documents Used for XSLT XMLTask Type 178
Hands-On Exercise 1: XMLTask type Diff 179
Step 1: Create a BI Project and Add a Control Flow task, XMLTask 179
Step 2: Configuring XMLTask 179
Hands-On Exercise 2: XMLTask Type XSLT 184
Summary 186
Chapter 12: Creating a SSIS Package to Access Folders and Files 187
Hands-On Exercise: Copying a File from One Folder to Another and
Sending it using the Send Mail Task 188
Step 1: Creating a BI Project and Adding a
Control Flow Task ??” the File System Task 188
Step 2: Configuring The File System Task 189
Step 3: Adding a Send Mail Task to the Control Flow Page on the Canvas 192
Using Precedence Constraint to Send Mail 195
Step 4: Adding a Precedence Constraint 196
Step 5: Building and Executing the Package 197
Summary 197
Chapter 13: Package to Copy a Table from Oracle XE 199
Hands-On Exercise: Transferring a View from Oracle 10G XE to
an SQL Server 2005 Database 200
Oracle 10G XE Server 200
Starting and Stopping the Oracle 10G XE Server 200
Using the Object Browser 201
Step 1: Creating a BI Project and Adding a Data Flow Task 203
Step 2: Adding an OLE DB Source and Configuring it to
Connect to a Local Oracle 10G XE Server 204
Step 3: Configuring the OLE DB Source 205
Step 4: Adding a SQL Server Destination and Configuring
its Connection Manager 207
Step 5: Establishing a Path from the OLE DB Source to
the SQL Server Destination 209
Step 6: Configuring the SQL Server Destination Component 210
Step 7: Building and Executing the Package 212
Summary 212
Table of Contents
[ vi ]
Chapter 14: Web Service Task to Convert Miles to Kilometres 213
Hands-On Exercise: Creating and Testing a Package
that Uses a Web Service Task 214
Part One 214
Step 1: Create a Visual Studio 2005 Blank Solution 214
Step 2: Create a Web Service 215
Step 3: Create a WSDL File 217
Part Two 218
Step 1: Creating a BI project and Adding a Web Service Task 219
Step 2: Configuring the Web Service Task 220
Step 3: Building the BI project and Executing the Package 225
Summary 226
Chapter 15: Package that Transfers a Database
from One SQL Server to Another 227
Hands-On Exercise: Creating and Testing a Package
that Uses a Transfer Database Task 228
Step 1: Creating a Network Share on Computer 1 228
Step 2: Creating a BI Project and Adding a Transfer Database Task 229
Step 3: Configuring the Transfer Database Task 229
Establishing Connections 231
Configuring the Source Database Node 233
Configuring the Destination Database 235
Step 4: Building the BI Project and Executing the Package 236
Summary 238
Chapter 16: On Using Event Handlers in SSIS 239
Hands-On Exercise: Creating a Project with Two Packages 240
Step 1: Create a BI Project and Rename the Default Package 240
Step 2: Add and Configure the Package that has a Send Mail Task 241
Step 3: Add and Configure an Execute SQL Task to the
Renamed Default Package 242
Step 4: Add an Execute Package Task to the OnError Event
of the Renamed Default Package 244
Step 5: Add an Execute Process Task to the OnPostExecute
Event of the Renamed Default Package 246
Step 6: Build and Execute the Package with Event Handlers and Verify 248
Summary 249
Chapter 17: Package that Transfers a File Using an FTP Task 251
Hands-On Exercise: Creating a Package with a FTP Task 252
Step 1: Creat a BI Project and Rename the Default Package 253
Step 2: Build and Execute the FTP Task 257
Table of Contents
[ vii ]
Step 3: Disable the FTP Task, Add and Configure another
FTP Task to Download a File from an Internet Public Site 257
Step 4: Build and Execute the New FTP Task 261
Summary 261
Chapter 18: Package with an ActiveX Script Task 263
Hands-On Exercise: Creating a Package with ActiveX Script Tasks 264
Example One: Word Automation 265
Step 1: Creating a BI Project and Adding an ActiveX Script Task to the Package 265
Example Two: Finding the Number of Tables in a Database 268
Step 2: Adding Another ActiveX Script Task to the Project 268
Example Three: Navigating to the Internet Explorer Browser 271
Step 3: Adding another ActiveX Script Task to the Project 271
Summary 272
Chapter 19: Package with a Script Task 273
Overview of the Hands-On Exercises 273
Hands-On Exercise: Creating a Package with Script Tasks 274
Simple Calculation 274
Calculation using variables 278
Add an Imports Statement to Build a String 280
Retrieve Data from a Database Table in the SQL Server 2005 282
Combine the Last Two Examples in Displaying Data
and Copying to a File 284
Summary 286
Chapter 20: Package with Maintenance Plan Tasks 287
Backing Up a Database 289
Hands-On Exercise: Creating a Package with Maintenance Tasks 289
Summary 296
Index 297
Preface
SQL Server Integration Services, with the acronym SSIS, is a comprehensive ETL
tool that made its debut with SQL Server 2005.
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