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.
Read the full Table of Contents for Beginners Guide to SQL Server Integration Services Using Visual Studio 2005
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.
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.
