SQL University – SSIS Design and Development

Welcome back to SQL University! This week I am going to step *way* outside my comfort zone and talk about SSIS. I am not embarrassed to say that I have little to no practical experience as an SSIS developer, I simply have not had the need to play with SSIS to the extent that I would like. It’s like surfing; I know how it works, I’ve seen other people doing it, but I have rarely tried it myself.

And since “those who can, do. Those that can’t, teach” fits real nicely here, I thought I could help to guide you along your way today. You’re welcome.

tempdb

First step, as always, is awareness of what SSIS is for. I tend to think of SSIS in a simple way: it is a tool meant to help extract, transform, and load data. Now, how that tool is deployed and used will depend on your shop, but the idea that it is to help you build an ETL process is fairly standard. But SSIS can also perform other tasks, as we shall see in a minute.

One key point of awareness to make here is that the SSIS service exists outside of the SQL Server service. That means you could(!) install only the SSIS service on a dedicated application or batch server, but ideally you would install both the SSIS service along with the database engine. This would allow for you to better utilize your server resources. It would also reduce your network resources, because if you did install SSIS by itself you would still need to connect back to a database engine (most likely) somewhere, at some time.

msdb

OK, enough talk, let’s play. Time for you to get down and dirty with an SSIS package. Worried? Well, you shouldn’t be. Let’s start with something that should be familiar to you by now: maintenance plans.

In SQL 2005 and above, maintenance plans are nothing more than SSIS packages. Go ahead and create one now by going into SSMS, expand the ‘management’ folder and right-click on ‘Maintenance Plans’. I’ll wait.

Are you done yet? Did you select the ‘Shrink Database Task’? If you did then shame on you. Go here and read this and then you can come back to class.

Building a maintenance plan will give you a certain amount of familiarity with SSIS. It will not make you an expert by any means, but it is a good first step because you are designing a process that should be quite familiar to you. I always find it is easiest to learn something new when you can apply it in a way to something that is familiar. In fact, that is how I built my first database and website: to use for my Fantasy Football league.

model

Now that you have spent some time exploring the SSIS package that is a maintenance plan, it is time to branch out. Let’s explore a bit more about what SSIS has to offer. Open up the Business Intelligence Development Studio and create a new SSIS project. Explore the toolbox, specifically the control flow items. Many of them will be familiar to you if you have used DTS in the past. My suggestion here is to think of a specific data set you want to move from one server to another and try to use SSIS to configure that process. After a little while you should become more and more familiar with using SSIS in general.

But what about SSIS package deployments? Well, you have two options for deploying packages. You can either deploy to an instance of SQL Server or you can deploy to a flat file. Which method is right for you? Well, that will depend upon your requirements as well as your environment. I typically like to have SSIS packages deployed as flat files, it is easier for people to work with the files themselves when they need to make changes as opposed to trying to query the msdb to see if certain flags are set. Ideally you will use package configuration files for everything, but if you start getting into this level of detail then you would be a…

master

If you get to this level of SSIS development then you will find yourself rubbing elbows with the likes of Brian Knight and Andy Leonard (blog | twitter). And chances are you will have gone full-BI and are using SSIS alongside with SSAS and SSRS, or will be very soon.

resourcedb

Here is the first place for you to go (it’s actually the only place you need to go):

Knight’s 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services