I’ve been getting requests to cover SSIS, so here we go. Over the next few months I’m going to give you a course that will help you go from knowing what SSIS stands for (SQL Server Integration Services) to being proficient. You’ll learn how to work in SSIS. I’ll teach you what many of the tools in the toolbox panel do, as well as when you might want to use them. This course will serve as a basis for classes that I plan to offer this year.

If you’re looking for SSIS training today, I’d suggest you check in with Andy Leonard.  He’s been a resource to me through his blog posts and his sessions at SQL Saturday.

On with the show.

What is SSIS?

SSIS was introduced with SQL Server 2005 to expand upon the older Data Transformation Services (DTS) found in SQL 2000 and earlier versions of SQL Server.  At it’s most basic SSIS is a solution for creating workflows that deal with your database server.  This will probably lead you to ask:

What’s a Work flow?

Well, a work flow is a set of steps you take to accomplish a specific goal.  The most common one you’re going to solve with SSIS is getting data from one format into another.  This is known as ETL (Extraction, Transformation, and Load).  ETL is the business term for moving data from one location to another, and making any changes to the data to fit it into the destination.

• Extraction gets information out of a source.
• Transformation changes the data in some way (usually to make it compatible with the destination format).
• Load puts the information into the destination.

There are other types of work flows in business, but if you’re considering SSIS for the job, it’s pretty much going to be some variation of an ETL work flow.

The goal of your SSIS package should be to automate this work flow so you set it up once, then forget it.

I want to give you a little aside here.  When you set it and forget it, setting up error capturing and handling become super important!  You want to forget the process, but you want it to still call you when there’s a problem.  Never forget the error handling!

Enough Vocabulary, Let’s Dig In!

That’s the spirit!  There’s two ways I’m going to teach, you can use to create an SSIS package:

1. The Import and Export wizard.  It is the easiest way to set up a Extraction or a Load process, It’s pretty limited on the transformation step.  Plus, like I said before, it’s going to gloss over error handling.  I’m only showing you this, so you can create your first SSIS Package, test it, then immediately open it up in the SSIS Designer, and rip it apart to figure out how it works.
2. The SSIS Designer. In your start menu, you should see “SQL Server Business Intelligence Development Studio.” I will refer to this as BIDS going forward.  It’s a slimmer version of Visual Studio that’s optimized so you can build SSIS Packages, without having too many unnecessary options in the interface.

You can use other languages to program-matically create SSIS packages. This course is not going to cover that.  Honestly, that’s over my head at this point.  Most of the time it’s a one shot load this data into the server or get this data out of the server.  I have a formula I use to create my packages, but I haven’t upgraded this method to a program I use to build my packages.  Though that would be pretty cool!

In my next article, I’m going to walk you through the Import and Export Wizard.  We’ll create a simple package to read a text file into your development server, and then we’ll open that package up in BIDS, and start expanding on it.

If you have any questions about SSIS, or anything SQL Server related, please let me know.  I’m here to help you learn all about SQL!