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:

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


About ssimagine

My name is Matthew Roche, and I am a Senior Program Manager with the SQL Server product group at Microsoft. I work on Master Data Services and Data Quality Services, and have previously worked on SQL Server Integration Services. Although I work for Microsoft and will be posting on technical topics, I want to stress that this is a personal blog, and any opinions posted here are mine and mine alone. I built my career around SQL Server and Microsoft technologies for well over a decade before I joined Microsoft as an employee, and I plan on using this blog to share my personal experience and opinions. They may well be shaped by my experience on the SQL Server team, but they’re still mine, and not that of Microsoft, disclaimer, disclaimer, etc., etc..
This entry was posted in Beginners, BI, SQL Server, SSIS. Bookmark the permalink.

One Response to New to SSIS? Start Here!

  1. mattes says:

    Hi MatthewI 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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s