Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 Table of Contents

Table of Contents

• Preface

• Chapter 1: SSIS Basics
• SQL Server Integration Services
• Objects Used in SSIS
• The SSIS Package
• The Control Flow Elements
• Data Flow Components
• Data Source Components
• Data Transformation
• Data Flow Destinations
• Connection Managers
• Variables
• Event Handlers
• Log Providers
• Debugging and Diagnostic Features
• Summary

• Chapter 2: Creating a BI Project for SSIS in Visual Studio 2005
• Business Intelligence using Microsoft Products
• Resources Used for Creating Projects
• Creating Your First BI Project for SSIS
• Launching VS 2005 and Creating a BI Project for Integration Services
• Business Intelligence Project Properties
• Overview of the Project Window
• Canvas for Package Design
• Control Flow
• Data Flow
• Event Handlers
• Package Explorer
• The Toolbox
• The Solution Explorer
• Getting Various Windows
• Server Explorer Window
• Bookmark Window
• Class View and Code Definition Windows
• Object Browser
• Error List Window
• Output Window
• Properties Window
• Tasks List Window
• Toolbox Window
• Find Results Window
• Other Windows
• Debug Windows
• BI Related Items in Tools/Options
• Property Pages and Folders of the Project
• Executing the Package and Saving the Project
• Hands-On Exercises
• Hands-On Exercise 1
• Hands-On Exercise 2
• Hands-On Exercise 3
• Summary

• Chapter 3: Sending Email with a SSIS Package
• Hands-On Exercise One: Sending an Email Using the SMTP Server
• Hands-On Exercise Two: How to Find Your ISP's SMTP Server?
• Summary

• Chapter 4: Transferring Data to a Text File
• Hands-On Exercise: Transferring Data to a Text File
• Step 1: Creating a BI Project and Adding a Data Flow Task
• Step 2: Adding Connection Manager for the DataReader
• Step 3: Configuring the Source
• Step 4: Adding a Flat File Destination and Establishing a Path from DataReader Source
• Step 5: Configuring the Flat File Destination Component
• Step 6: Build and Execute the Package
• Summary

• Chapter 5: Transferring Data to a Microsoft Excel File
• Hands-On Exercise: Transferring Data to an Excel File
• Step 1: Creating a BI Project and Adding a Data Flow Task
• Step 2: Configuring the DataReader's Connection Manager
• Step 3: Configuring the DataReader Source
• Step 4: Adding a Character Map Transformation
• Step 5: Adding an Excel Destination and Establishing a Path to It from the Character Map Data Flow Component
• Step 6: Configuring the Excel Destination Component
• Step 7: Testing the Package
• Summary

• Chapter 6: Data Transfer to an MS Access Database
• Hands-On Exercise: Transferring Data to an Access Database
• Step 1: Creating a BI Project and Adding a Data Flow Task
• Step 2: Configuring the DataReader's Connection Manager
• Step 3: Configuring the DataReader Source
• Step 4: Adding an OLE DB Destination and Establishing a Path from the DataReader Component
• Step 5: Configuring the OLE DB Destination Component
• Step 6: Incorporating a Data Viewer to Monitor Data Flow
• Summary

• Chapter 7: Data Transfer from a Text File Using the Bulk Insert Task
• Hands-On Exercise: Transferring Data from a Flat File to a SQL Server Database Table
• Step 1: Use / create a Flat Text File whose Contents Need to be Transferred
• Step 2: Create a Table with Columns that Can Accept the Contents of the File Created
• Step 3: Create a BI Project and Add a Bulk Insert Task
• Step 4: Configure the Bulk Insert Task
• Step 5: Build and Execute the Package
• What Happens if there Is an Error?
• Summary

• Chapter 8: Using a Conditional Split Data Transformation
• Hands-On Exercise: Splitting Data Retrieved from a SQL Server
• 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
• Step 2: Add a Conditional Split Transformation
• Step 3: Establish a Path to Connect DataReader Source with the Conditional Split Data Transformation
• Step 4: Configure the Conditional Split Data Transformation
• Step 5: Add Recordset Destination(s)
• Step 6: Configure the Recordset Destination(s)
• Step 7: Build, Execute the Package and Review
• Summary

• Chapter 9: Using an Aggregate Data Transformation
• Hands-On Exercise: Using Aggregate Data Flow Transformation
• 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
• Step 2: Add an Aggregate Data Transformation
• Step 3: Establish a Path to Connect DataReader Source with the Aggregate Data Transformation
• Step 4: Configure the Aggregate Data Flow Transformation
• Step 5: Add a Percentage Sampling Data Transformation
• Step 6: Establish a Path from Aggregate Data Transformation to the Percentage Sampling Data Transformation
• Step 7: Configure the Percentage Sampling Data Flow Item
• Step 8: Add a Recordset Destination Data Flow Component
• Step 9: Configure the Recordset Destination Data Flow Component
• Step 10: Build and Execute the Package, and Review Results
• Summary

• Chapter 10: Using a Data Conversion Data Flow Transformation
• Source and Destination for the Exercise

• Hands-On Exercise: Transferring Data to an Excel File
• Step 1: Creating a BI Project; Adding a Data Flow Task and Configuring an Excel Source
• Step 2: Adding a Data Conversion Data Flow Transformation
• Step 3: Establishing a Path from Excel Source to Data Conversion Data Flow Transformation
• Step 4: Configuring the Data Conversion Data Transformation
• Step 5: Adding and Configuring an OLE DB Data Destination
• Adding an OLE DB Destination to Canvas
• Establish a Path from Data Conversion Data Flow Transformation to OLE DB Data Destination
• Setting Up a Connection Manager
• Displaying the Table to which the Data will be Inserted
• Column Mappings
• Handling Data Loading Errors
• Step 6: Adding and Configuring a Recordset Destination for Displaying Errors
• Step 7: Building the Project and Testing the Package

• Summary

• Chapter 11: Creating a SSIS Package with an XML Task
• Diff
• Merge
• Validate
• XPATH
• XSLT
• XML Documents Used in This Chapter
• Documents Used for Diff XMLTask Type
• Documents Used for XSLT XMLTask Type
• Hands-On Exercise 1: XMLTask type Diff
• Step 1: Create a BI Project and Add a Control Flow task, XMLTask
• Step 2: Configuring XMLTask
• Hands-On Exercise 2: XMLTask Type XSLT
• Summary

• Chapter 12: Creating a SSIS Package to Access Folders and Files
• Hands-On Exercise: Copying a File from One Folder to Another and Sending it using the Send Mail Task
• Step 1: Creating a BI Project and Adding a Control Flow Task — the File System Task
• Step 2: Configuring The File System Task
• Step 3: Adding a Send Mail Task to the Control Flow Page on the Canvas
• Using Precedence Constraint to Send Mail
• Step 4: Adding a Precedence Constraint
• Step 5: Building and Executing the Package
• Summary

• Chapter 13: Package to Copy a Table from Oracle XE
• Hands-On Exercise: Transferring a View from Oracle 10G XE to an SQL Server 2005 Database
• Oracle 10G XE Server
• Starting and Stopping the Oracle 10G XE Server
• Using the Object Browser
• Step 1: Creating a BI Project and Adding a Data Flow Task
• Step 2: Adding an OLE DB Source and Configuring it to Connect to a Local Oracle 10G XE Server
• Step 3: Configuring the OLE DB Source
• Step 4: Adding a SQL Server Destination and Configuring its Connection Manager
• Step 5: Establishing a Path from the OLE DB Source to the SQL Server Destination
• Step 6: Configuring the SQL Server Destination Component
• Step 7: Building and Executing the Package
• Summary

• Chapter 14: Web Service Task to Convert Miles to Kilometres
• Hands-On Exercise: Creating and Testing a Package that Uses a Web Service Task
• Part One
• Step 1: Create a Visual Studio 2005 Blank Solution
• Step 2: Create a Web Service
• Step 3: Create a WSDL File
• Part Two
• Step 1: Creating a BI project and Adding a Web Service Task
• Step 2: Configuring the Web Service Task
• Step 3: Building the BI project and Executing the Package
• Summary

• Chapter 15: Package that Transfers a Database from One SQL Server to Another
• Hands-On Exercise: Creating and Testing a Package that Uses a Transfer Database Task
• Step 1: Creating a Network Share on Computer 1
• Step 2: Creating a BI Project and Adding a Transfer Database Task
• Step 3: Configuring the Transfer Database Task
• Establishing Connections
• Configuring the Source Database Node
• Configuring the Destination Database
• Step 4: Building the BI Project and Executing the Package
• Summary

• Chapter 16: On Using Event Handlers in SSIS
• Hands-On Exercise: Creating a Project with Two Packages
• Step 1: Create a BI Project and Rename the Default Package
• Step 2: Add and Configure the Package that has a Send Mail Task
• Step 3: Add and Configure an Execute SQL Task to the Renamed Default Package
• Step 4: Add an Execute Package Task to the OnError Event of the Renamed Default Package
• Step 5: Add an Execute Process Task to the OnPostExecute Event of the Renamed Default Package
• Step 6: Build and Execute the Package with Event Handlers and Verify
• Summary

• Chapter 17: Package that Transfers a File Using an FTP Task
• Hands-On Exercise: Creating a Package with a FTP Task
• Step 1: Creat a BI Project and Rename the Default Package
• Step 2: Build and Execute the FTP Task
• Step 3: Disable the FTP Task, Add and Configure another FTP Task to Download a File from an Internet Public Site
• Step 4: Build and Execute the New FTP Task
• Summary

• Chapter 18: Package with an ActiveX Script Task
• Hands-On Exercise: Creating a Package with ActiveX Script Tasks
• Example One: Word Automation
• Step 1: Creating a BI Project and Adding an ActiveX Script Task to the Package
• Example Two: Finding the Number of Tables in a Database
• Step 2: Adding Another ActiveX Script Task to the Project
• Example Three: Navigating to the Internet Explorer Browser
• Step 3: Adding another ActiveX Script Task to the Project
• Summary

• Chapter 19: Package with a Script Task
• Overview of the Hands-On Exercises
• Hands-On Exercise: Creating a Package with Script Tasks
• Simple Calculation
• Calculation using variables
• Add an Imports Statement to Build a String
• Retrieve Data from a Database Table in the SQL Server 2005
• Combine the Last Two Examples in Displaying Data and Copying to a File
• Summary

• Chapter 20: Package with Maintenance Plan Tasks
• Backing Up a Database
• Hands-On Exercise: Creating a Package with Maintenance Tasks

• Summary

• Index