Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 (Paperback)

by Jayaram Krishnaswamy (Author)

Editorial Reviews

Product 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
• Available as an eBook to download immediately

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.

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

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

About the Author
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 Netherlands; a Visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in Sao Jose dos Campos in Sao 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 / 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 Plainsboro, New Jersey, USA.

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

Product Details

• Paperback: 320 pages
• Publisher: Packt Publishing (December 30, 2007)
• Language: English
• ISBN-10: 1847193315
• ISBN-13: 978-1847193315
• Product Dimensions: 9.2 x 7.5 x 0.8 inches
• Shipping Weight: 1.2 pounds (View shipping rates and policies)
• Average Customer Review: 3.5 out of 5 stars See all reviews (14 customer reviews)
• Amazon.com Sales Rank: #705,649 in Books (See Bestsellers in Books)

Customer Reviews

Most Helpful Customer Reviews

15 of 16 people found the following review helpful:

1.0 out of 5 stars Not a Great SSIS Beginner's Book: Review by Tod McKenna of blog.todmeansfox.com, February 23, 2008

By	Tod McKenna "blog.todmeansfox.com" (Gent, Belgium) - See all my reviews (REAL NAME)

SSIS is a very good data integration tool. It is easy to get started with, but takes some time and experience to master. Although I have some heavy beefs about the 2005 version (metadata management, validation issues, proprietary expression builder, no C# for the Script Task, etc...), all-in-all, I do find SSIS to be good to work with.

I was excited to get this book in the mail, as I know how difficult it is to find a good beginners guide for SSIS. I'm no longer a newbie, but I once was and I found that the good books were almost too advanced and too comprehensive to get some simple tasks done and to get a good understanding of the tool and involved concepts.

Unfortunately, I found this book difficult to read. I also found that it contained many technical inaccuracies. The author (a non-native English speaker I assume) struggles to get good, succinct points across. I am not saying that a total beginner could not get into this book and get a few packages developed, but for Data Warehousing professionals, DBAs, and developers, this is not the book to keep in your reference library.

For example, on page 123, the author writes:

"The Bulk Insert Task cannot be used with any of the data flow transformation components, and in this respect is not as versatile as a data flow task. This task has been provided for backward compatibility."

Hmmn... First of all, it would be nice to know why the Bulk Insert Task cannot be used with any of the transforms (short answer: The Bulk Insert Task is a Control Flow item). It would also be very helpful to the reader to have an idea why you would select a Bulk Insert over a Data Flow. Lastly, the Bulk Insert Task is *not* provided for backward compatibility!

I do not want to knock the author too much for sentence and paragraph structure. I am currently learning Dutch and wouldn't dream of being able to develop a technical book in my non-native tongue. I am assuming, of course, that English is not the author's native language. so this next example might be more geared towards the editors who I think have a responsibility to help ESL authors with these sorts of things. One example, which is indicative of the entire text, is on page 33:

"The default package is empty when created with the name, Package."

Does this mean if I change the name from "Package" to "FoxPro", it won't be empty anymore? Of course, it is easy to understand what the author means here. But it is sentences like this that really make the reading quite difficult and choppy.

As a BI\DW professional, I was terribly disappointed with how the author sets the beginner up for diving into SSIS. For example, the definitions for common terms such as Business Intelligence, Data Warehousing, data mining, data lineage, metadata management, data conformity, data profiling, data quality, dimensions, ETL, OLAP, ROLAP, MOLAP, etc. were either inadequate or missing entirely. I would want beginners under my wing to know a little more about why they are using the tool and not so much a step-by-step cookbook on how to utilize some of the tasks. To me, that is what the help file is for!

Other issues I have:

(1) There are too many screenshots and no figure numbers to refer to them.

(2) Inconsistent naming: Data Flow page, canvass, or view? Task, item, or component? Etc..

(3) In the sections in the beginning of the book, the author attempts to define the SSIS lexicon. The definitions presented in almost every case are inadequate (check OLEDB Command and SCD Task for example). The user would be better off opening BIDS and reading the tooltips of each item!

(4) Using Visual Studio 2005? How about call it what Microsoft does: Business Intelligence Development Studio, or "BIDS". To be fair, the BIDS and VS shells are exactly the same. The major difference is that if you install BIDS, you have a different set of packages available (SSAS, SSIS, and SSRS) and not C# and ASP.NET (for example). Also, in all the literature (to my knowledge), MS refers to BIDS and not VS. This could be a point of confusion for a beginner.

(5) There was virtually no discussion on best practices. There were several opportunities for the author to introduce some best-practice, good advice for the beginner.

(6) The book's index is totally inadequate. I can't believe that there are no terms for letters G, H, I, J, K, L, N, Q, R, U, V, Y or Z! I can understand Q not having anything of significance, but how do I look up the Lookup Task? Row Sampling? Union All?

(7) How about more discussion on error handling, logging, and auditing? These are absolutely critical items that need to be discussed -- especially for the beginner!

(8) What about the SCD Task?

Seriously, I could go on, but I think you know how I feel. I would be happy to share with you additional thoughts as well.

5 of 5 people found the following review helpful:

4.0 out of 5 stars For noobs only, June 7, 2008

By	Richard J. Dudley (Butler, PA USA) - See all my reviews (REAL NAME)

If you can create an SSIS package, and understand most of what the different toolbox object are, this is not the book for you. If you don't even know ho to create an SSIS package, and need to do so quickly, buy this book. In a couple hours, you'll be proficient in the basics of SSIS.

Packt asked me to review this book, and their timing was perfect--I was ready to start an SSIS project but had no experience. I was a heavy DTS user, and the difference between DTS and SSIS blew my mind. The first two chapters are a reference of the different flows, transformations, sources and destinations available, and the rest of the book is essentially a workbook, full of how-to examples for very common tasks. Even if what you need to do isn't covered,

I did hope there would be a section on the new "fuzzy" objects, but there was none. This book also does not discuss way to organize your solutions and projects, but this also was left to more advanced books.

I've loaned this book to several coworkers, and all have found it useful. We all came up to a basic understanding of SSIS very quickly, and have a good enough understanding to pick up more advanced examples.

3 of 3 people found the following review helpful:

5.0 out of 5 stars Excellent Book for New SSIS Users!, September 16, 2008

By	Anke "Anke Jorrit" (Ohio United States) - See all my reviews

I am new to SQL and SSIS and have been on a mission to master SSIS in SQL 2005. I have purchased about a dozen books, but have yet been able to use most of the books because they are too advanced (even though most say they are for new users).

On my last round of book purchases I picked up Jayaram's "Beginners Guide to SQL...", this is BY FAR the BEST book I have purchased on SSIS! I have actually been able to walk through each one of the examples in the book to gain better understanding of how SQL SSIS works. The books has a lot of excellent screenprints (they do not have figure numbers, but they follow they text and I wasn't confused without the numbers).

The writing style of the book was really straight forward and to the point step-by-step directions, so that you can follow along with a sample database.

Please note in the title of this book it says 'for beginners', so some of the more advanced topics may not be in this book, but it definitely sets you up to have a basic concept and then you can move on. This book allowed me to be able to open the other books I have purchased (and gave me a clue what they were saying).

I would recommend purchasing this book if you are new to SSIS and would like to have a textbook style reference that you can refer back to see how to setup the different SSIS concepts.

-SB