Tuesday, March 4, 2008

New to SSIS? Start Here!

I must admit that I've been known to say some negative things about the SQL Server Integration Services documentation from time to time. But one part of the SSIS content in SQL Server Books Online is the "Creating a Simple ETL Package" tutorial. It is probably the single most valuable beginner's resource for working with SSIS, and yet so many people who need this information have never heard of it.

So what is it?

This five-part hands on tutorial provides an overview of the tools in Visual Studio for SSIS and the capabilities of the SSIS platform. It uses the AdventureWorks sample databases and a set of text files that are included with the Books Online install, and walks users through five lessons, each of which builds on the ones before:
  1. Creating the Project and Basic Package: Build an SSIS package that extracts data from a text file and loads it into a SQL Server table, complete with explanations not only of what to do but why you're doing it.
  2. Adding Looping: Update the package to loop through all of the flat files in a folder and load their data into the table.
  3. Adding Package Configurations: Add a package configuration (a very important technique to learn!) so that the path to the folder where the text files are stored can be updated outside of the package.
  4. Adding Logging: Update the package to log execution information to a text file.
  5. Adding Error Flow Redirection: Update the package to redirect "bad data" to a text file for later analysis.

Sounds great, doesn't it? While these techniques are covering only a small portion of what SSIS can do, they do cover a representative subset and give users much-needed familiarity and comfort with the SSIS tools in Visual Studio.

And best of all, while the text files used in the lessons are included in your local SQL Server installation, you can find the tutorial instructions on MSDN here: http://msdn2.microsoft.com/en-us/library/ms169917.aspx

Take an hour or two and go through these tutorials - you won't regret it!

1 comment:

mattes said...

Hi Matthew

I tend to agree with your opinion. The tutorial is a good starting point to get an idea of the basic capabalities of SSIS.

Unfortunately, I don't get the Lesson 5 'Adding Error Flow Redirection' to work properly. The error never gets populated with the 'BAD' rows from the example. Any help appreciated! Thanks.