You are previewing SQL Server Integration Services Using Visual Studio 2005.
O'Reilly logo
SQL Server Integration Services Using Visual Studio 2005

Book Description

An ideal book 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.

  • 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

  • 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.

    Table of Contents

    1. Copyright
    2. Credits
    3. About the Author
    4. Acknowledgements
    5. About the Reviewers
    6. Preface
    7. SSIS Basics
      1. SQL Server Integration Services
      2. Objects Used in SSIS
      3. Summary
    8. Creating a BI Project for SSIS in Visual Studio 2005
      1. Business Intelligence using Microsoft Products
      2. Resources Used for Creating Projects
      3. Creating Your First BI Project for SSIS
      4. Hands-On Exercises
      5. Summary
    9. Sending Email with a SSIS Package
      1. Hands-On Exercise One: Sending an Email Using the SMTP Server
      2. Hands-On Exercise Two: How to Find Your ISP's SMTP Server?
      3. Summary
    10. Transferring Data to a Text File
      1. Hands-On Exercise: Transferring Data to a Text File
      2. Summary
    11. Transferring Data to a Microsoft Excel File
      1. Hands-On Exercise: Transferring Data to an Excel File
      2. Summary
    12. Data Transfer to an MS Access Database
      1. Hands-On Exercise: Transferring Data to an Access Database
      2. Summary
    13. Data Transfer from a Text File Using the Bulk Insert Task
      1. Hands-On Exercise: Transferring Data from a Flat File to a SQL Server Database Table
      2. Summary
    14. Using a Conditional Split Data Transformation
      1. Hands-On Exercise: Splitting Data Retrieved from a SQL Server
      2. Summary
    15. Using an Aggregate Data Transformation
      1. Hands-On Exercise: Using Aggregate Data Flow Transformation
      2. Summary
    16. Using a Data Conversion Data Flow Transformation
      1. Hands-On Exercise: Transferring Data to an Excel File
      2. Summary
    17. Creating a SSIS Package with an XML Task
      1. Diff
      2. Merge
      3. Validate
      4. XPATH
      5. XSLT
      6. XML Documents Used in This Chapter
      7. Hands-On Exercise 1: XMLTask type Diff
      8. Hands-On Exercise 2: XMLTask Type XSLT
      9. Summary
    18. Creating a SSIS Package to Access Folders and Files
      1. Hands-On Exercise: Copying a File from One Folder to Another and Sending it using the Send Mail Task
      2. Using Precedence Constraint to Send Mail
      3. Summary
    19. Package to Copy a Table from Oracle XE
      1. Hands-On Exercise: Transferring a View from Oracle 10G XE to an SQL Server 2005 Database
      2. Summary
    20. Web Service Task to Convert Miles to Kilometres
      1. Hands-On Exercise: Creating and Testing a Package that Uses a Web Service Task
      2. Summary
    21. Package that Transfers a Database from One SQL Server to Another
      1. Hands-On Exercise: Creating and Testing a Package that Uses a Transfer Database Task
      2. Summary
    22. On Using Event Handlers in SSIS
      1. Hands-On Exercise: Creating a Project with Two Packages
      2. Summary
    23. Package that Transfers a File Using an FTP Task
      1. Hands-On Exercise: Creating a Package with a FTP Task
      2. Summary
    24. Package with an ActiveX Script Task
      1. Hands-On Exercise: Creating a Package with ActiveX Script Tasks
      2. Example Three: Navigating to the Internet Explorer Browser
      3. Summary
    25. Package with a Script Task
      1. Overview of the Hands-On Exercises
      2. Hands-On Exercise: Creating a Package with Script Tasks
      3. Summary
    26. Package with Maintenance Plan Tasks
      1. Summary
    27. Index