Beginners Guide to SQL Server Integration Services Using Visual Studio 2005


Beginners Guide to SQL Server Integration Services Using Visual Studio 2005
eBook: $23.99
Formats: PDF, PacktLib, ePub and Mobi formats
$14.39
save 40%!
Print + free eBook + free PacktLib access to the book: $63.98    Print cover: $39.99
$20.00
save 69%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Overview
Table of Contents
Author
Reviews
Support
Sample Chapters

 

  • Environment set up for Visual Studio 2005 with respect to SSIS and multiple tasking
  • Connect to Microsoft Access, Text Files, Excel Spread Sheets
  • Transform data from a source going to a destination
  • Use the scripting support that the IDE provides and event handling
  • Learn about tasks such as web service and XML, which are new in SQL Server 2005
  • Read: Chapter 13 [PDF 2.9 MB] | Table of Contents
  • Available as an eBook to download immediately

 

Book Details

Language : English
Paperback : 320 pages [ 235mm x 191mm ]
Release Date : December 2007
ISBN : 1847193315
ISBN 13 : 9781847193315
Author(s) : Jayaram Krishnaswamy
Topics and Technologies : All Books, Enterprise Products and Platforms, Microsoft Servers, Enterprise, Microsoft, Microsoft SQL Server

Back to BOOK PAGE

Table of Contents

Preface
Chapter 1: SSIS Basics
Chapter 2: Creating a BI Project for SSIS in Visual Studio 2005
Chapter 3: Sending Email with a SSIS Package
Chapter 4: Transferring Data to a Text File
Chapter 5: Transferring Data to a Microsoft Excel File
Chapter 6: Data Transfer to an MS Access Database
Chapter 7: Data Transfer from a Text File Using the Bulk Insert Task
Chapter 8: Using a Conditional Split Data Transformation
Chapter 9: Using an Aggregate Data Transformation
Chapter 10: Using a Data Conversion Data Flow Transformation
Chapter 11: Creating a SSIS Package with an XML Task
Chapter 12: Creating a SSIS Package to Access Folders and Files
Chapter 13: Package to Copy a Table from Oracle XE
Chapter 14: Web Service Task to Convert Miles to Kilometres
Chapter 15: Package that Transfers a Database from One SQL Server to Another
Chapter 16: On Using Event Handlers in SSIS
Chapter 17: Package that Transfers a File Using an FTP Task
Chapter 18: Package with an ActiveX Script Task
Chapter 19: Package with a Script Task
Chapter 20: Package with Maintenance Plan Tasks
Index
  • 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 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
  • 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
  • Summary
Back to BOOK PAGE

Jayaram Krishnaswamy

Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.

He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.

He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.

Contact Jayaram Krishnaswamy


Submit Errata

Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.


Errata

- 2 submitted: last submission 24 Dec 2013

Errata type: Others | Page number: 16 | Errata date: 16 May 2011

There is a subject and verb agreement error in the second sentence of the paragraph concerning "Aggregate" functions. The sentence should reads "This transformation can either provide a value...".

 

Errata type: Technical (Download files) | Page number 177

 

The 1st line is: Copy the XML documents (webstudentsOK.xml and webstudentsOK2.xml, and
webstudents.xsl) and save them in any folder of your choice.

The three support files that need to be downloaded are uploaded on Please click this link

 

 

Sample chapters

You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

Frequently bought together

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 +    Getting Started with Talend Open Studio for Data Integration =
50% Off
the second eBook
Price for both: £21.95

Buy both these recommended eBooks together and get 50% off the cheapest eBook.

What you will learn from this book

After reading this book you will be able to:

  • Understand the Visual Studio 2005’s IDE as related to SSIS
  • Work with single and multiple tasks; working with more than 20 different tasks.
  • Connect to Microsoft Access, Flat Files, Excel Spread Sheets and third party vendor product
  • Link data source and data destination to a task
  • Send emails and ftp files
  • Transform data from a source going to a destination
  • Use the scripting support that the IDE provides
  • Monitor the data as it is being transported also new in SQL 2005 Server
  • Learn tasks such as web service and XML, which are new in SQL Server 2005
  • Understand how to use and handle events

Chapter-by-Chapter

In Chapter 1, you will learn the various roles played by SSIS in the enterprise business. You will also learn about the various objects used in SSIS, most importantly the Package object; the Control Flow Elements; the Data Flow Components; the Event Handlers and other miscellaneous features of the VS 2005 IDE.

In Chapter 2, you will learn how to create your first business intelligence project for SSIS using the Visual Studio 2005 IDE. You will be introduced to the many windows that are used by SSIS specifically and a few other windows of the VS 2005 IDE used for projects and solutions. The hands-on at the end of the chapter helps you to examine on your own the key features of the IDE as well as how you may retrieve a VS 2005 created package using the SQL Server 2005 Management Studio.

In Chapter 3 you will learn how to send a mail by creating the Send Mail Task using the SMTP server that you can access. If you are not sure about your SMTP Server you may find that Hands-on Exercise 2 give you a better understanding of how to access the SMTP server.

In Chapter 4 you will learn how to create a SSIS package that transfers a table on your SQL Server 2005 to a flat file on your C: drive. You will also learn how to work with the Connection Managers that is so essential for data transformation tasks,

In Chapter 5 you will learn how to create a SSIS package that transfers a table on your SQL Server 2005 database to an Excel spread sheet. You will also learn how to connect to an Excel data destination.

In Chapter 6 you will create a package that transfers data from a table on your SQL Server 2005 to an MS Access Database. You will learn how to work with the Data Viewer that monitors the data flow.
 
In Chapter 7 you will create a package that uses the Bulk Insert Task to transfer data in a text file on your computer to a table in the SQL Server 2005. You will also learn about creating a table on your SQL Server 2005 using the Management Console.

In Chapter 8 you will create a package that conditionally splits the data from a SQL Server 2005 query and send them to multiple destinations. You will also learn to work with Recordset Destination, an in-memory ADO Recordset Object and use it to display the results of data splitting. Additionally you will learn about using Variables in an SSIS Package.

In Chapter 9 you will create a package that can aggregate data from a database using the Aggregate data Transformation. You will extract data from a SQL Server 2005 and load it in to a Recordset Object to review the aggregated results.

In Chapter 10 you will create a package that converts the data extracted from an Excel spread sheet source before loading it into a MS Access Database. You will also learn how to direct the data that is not accepted by the destination to another destination using the errors in data transformation.

In Chapter 11 you will create a package that shows you how to work with the XML Task. You will learn how to find differences between two XML files as well as applying XSLT (Transformation) to convert an XML file to an HTML file.

In Chapter 12 you will learn how to work with the various options of a File System Object and how to use a precedence constraint that orders the tasks before executing the Package. You will learn how to copy a file from one location on your computer to another.

In Chapter 13 you will learn how to copy a table on an Oracle 10G XE database to a database on the SQL Server 2005. You will also learn how to install an Oracle 10G XE server and work with its database objects.

In Chapter 14 you will create a package that returns the value returned by accessing a web service task. You will also learn how to create a web service task in Visual studio 2005.

In Chapter 15 you will create a package that uses a Transfer Database task from one SQL Serer to another SQL Server (a different version). You will also learn how to access a SQL Server database on a network node.

In Chapter 16 you will create a package that uses event handlers and you will be learning about OnError and on OnPostExecute events. You will also learn about the ExecuteSQL Task as well as the Execute Process Task.

In Chapter 17 you will create a package for transferring files using the File Transfer task. You will learn to work with both a local FTP site as well as remote FTP site.

In Chapter 18 you will create a package using the ActiveX Script Task. You will have access to a fully commented code to help you along. You will be using this task to work with a word document; SQL Server Management Objects; and an internet browser object.

In Chapter 19 you will learn about the Script Task by creating packages that you use to interact with your file system as well your SQL Server 2005 for a variety tasks such as from making a simple calculation to retrieving data sets and loading into text files.

In Chapter 20 you will create a package that uses a Management Plan Task to backup a database on your SQL Server 2005. You will also get a general understanding of the Management Plan tasks.

In Detail

Sql Server Integration Services (SSIS) is a comprehensive ETL tool that made its debut with SQL Server 2005. It is a tool tightly integrated with the Visual Studio 2005 having all the functionalities that its forerunner DTS (Data Transformation Services) had in SQL Server 2000. This does not mean that it is just an improvement over DTS but a product which is totally different with a vastly improved interface; an extensible architecture; an enlarged tool set; ease of integration with other SQL Server Tools such as Analysis Services; capable of supporting connectivity with third party databases and bringing into a central location many database management tasks.

The Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 provides you with the basic knowledge that you should have before you move on to more advanced ETL [Extraction, Transformation, and Loading]. The book will also provide you with a comprehensive description of the many designer windows that you may encounter while working with the designer.

This guide provides you with the blocks to build a complete package. It describes each block using examples as well as detailing the nuts and bolts that hold the blocks in place. The majority of SSIS tasks are covered in this book, which are described in full in the summary of Table of Contents. You will start building packages right from Chapter 2 and continue on to Chapter 20 gathering and building upon your knowledge in each step.

 

 

This guide provides a detailed understanding of how tasks are designed by way of an example as well as describing the nuts and bolts that are used in configuring them. The majority of SSIS tasks are covered in this book and they are described fully in the summary of table contents section

Approach

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 provides you with the basic knowledge that you should have before you move on to more advanced ETL (Extraction, Transformation, and Loading). In each chapter you will get a little background of the SSIS task that you are going to create. You will then step through the numbered steps, creating, configuring, executing, and reviewing the results. The book has minimal coding (only two chapters) and exclusively uses the Visual Studio 2005 Designer.

Who this book is for

This book is written for beginners in the developer track who are looking to get an exposure to SQL Server Integration Services; DBA's who are testing water with the Visual Studio IDE but without a wide programming experience; SQL 2000 Data Transformation Services users who are trying to move into SQL Server 2005 Integration Services; Microsoft programming professionals in Small Businesses who wear multiple hats (jack of all) - developer, programmer, and dba with a little bit of experience in each of these.

It is expected that you know how to manipulate window objects, like clicking; dragging and dropping; using contextual help, tabbing, etc. It is also expected that you are exposed to SQL Server database basics and that you understand connecting to a database server; querying the database; reviewing objects; displaying and reviewing properties of objects, etc.  Very little coding skill is assumed except that you can logically follow a fully commented code.

This book is ideal for trainers who may want to teach an introductory course in SQL Server Integration Services or, to those who want to study and learn SSIS in a little over two weeks.

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software