Professional SQL Server™ 2005 Programming

19.1. Understanding the Problem

The problems being addressed by Integration Services exist in at least some form in a large percentage of systems — how to get data into or out of our system from or to foreign data sources. It can be things like importing data from the old system into the new, or a list of available items from a vendor — or who knows what. The common thread in all of it, however, is that we need to get data that doesn't necessarily fit our tables into them anyway.

What we need, is a tool that will let us Extract, Transform, and Load data into our database — a tool that does this is usually referred to simply as an "ETL" tool. Just how complex of a problem this kind of tool can handle varies, but SQL Server Integration Services — or SSIS — can handle nearly every kind of situation you may have.

This may bring about the question "Well, why doesn't everybody use it then, since it's built in?" The answer is one of how intuitive it is in a cross-platform environment. There are third-party packages out there that are much more seamless and have fancier UI environments. These are really meant to allow unsophisticated users move data around relatively easily — they are also outrageously expensive. Under the old DTS product, I actually had customers that were Oracle or other DBMS oriented, but purchased a full license for SQL Server just to make use of DTS — I'm sure that SSIS will be much the same (it's very, very nice!).