One of the awesome features rolled out in SQL Server 7.0 was its Data Transformation Services. Today, that set of features is called SQL Server Integration Services (SSIS). Both names provide a good clue about what this component does: It copies data from one place to another, integrating information between systems while reformatting the data at the same time. Here are some Microsoft SSIS basics.
Let’s take a basic scenario: You have a database in which customer orders are logged. Periodically throughout the day, you need to extract certain orders and send their information off to vendors, who will fulfill those orders and send the ordered products to customers on your behalf. Sadly, every vendor has a different format in which it needs to see the data. That’s where SSIS comes in.
Microsoft SSIS tutorial: Merging systems
In another example, you might have acquired a company that has its own data systems. For a while, your two companies will continue operating independently, using their own systems, but you’ll want to start bringing all of the data into a single system for the eventual merger. Both systems are completely different: For example, yours uses abbreviations like CA and NV for U.S. states, while the other company uses full names. It has a “Customer Name” field; you break it down into “First Name” and “Last Name.” Sound like a lot of manual work to integrate the two? Nope -- just call SSIS.
SSIS uses a graphical, drag-and-drop workspace to make this kind of integration easy. If you’ve ever created a flowchart in Microsoft Visio, then SSIS will be familiar. You’ll usually start by dragging in an object that represents a data source. You’ll then add in various “transformation” steps, each of which handles some task for turning the original data into what the destination needs. This can include looking up values, such as changing state names into abbreviations; manipulating data, such as splitting full names into first and last; and changing character sets. Everything usually terminates in another data connection, which represents your destination system. Figure 1 shows what a simple SSIS package might look like.
SSIS packages can start simple, but they can get very complex by including branching logic, parallel execution and more. When completed, they’re wrapped up into what’s called a package, and that package can be executed on its own and scheduled for regular execution through SQL Server Agent. You can move from the simple drag-and-drop workspace into true programming, writing custom tasks to handle difficult or complicated transformations.
Data sources can include actual databases like Oracle, SQL Server and Access, or even file-based data like CSV files and Excel spreadsheets. SSIS is complex enough that entire books have been written about it -- hop on your favorite online bookstore and punch “SSIS” into its search field to get a list.
SSIS tends to evolve under the radar; that is, each new SQL Server release adds new functionality and capabilities, but it doesn’t always get a lot of marketing hype. If you get a book on Microsoft SSIS basics, it’s important that your book matches the version of SQL Server you’re using. You should also dig deep into “what’s new” lists and articles, since even SQL Server service packs can add important new capabilities to SSIS.
What used to require complex custom programming can often be done as easily as building a Visio diagram. That’s not to say it’s something anyone can do -- Visio diagrams can get complicated, after all -- but with a little practice you can make SSIS do some heavy lifting for your data integration needs.
ABOUT THE AUTHOR
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 books on IT and a speaker at technical conferences worldwide. He can be reached through his website at www.ConcentratedTech.com.
Miss a tip? Check out the archive page for Don's series, "SQL Server for the Reluctant DBA.”