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:
- 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.
- Adding Looping: Update the package to loop through all of the flat files in a folder and load their data into the table.
- 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.
- Adding Logging: Update the package to log execution information to a text file.
- 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!