Chapter 6: Designing and Managing SSIS Packages

Microsoft Exam Objectives Covered in this Chapter:

• Design and manage SQL Server Integration Services (SSIS) packages.

  • Construct complex SSIS packages.

  • Design security for accessing packages.

  • Restart failed packages.

  • Troubleshoot or debug packages.

  • Deploy and move packages.

  • Schedule package execution.

  • Move packages to different servers.

SQL Server Integration Services (SSIS) is bundled with SQL Server 2005 and is designed to facilitate moving data-often large amounts of it-from place to place quickly and with a minimum of effort required from the developer. You might hear SSIS referred to as an ETL (extract, transform, and load) tool, and that’s a good starting description. Because SSIS has a good workflow engine and typically requires little or no coding to develop solutions, it can also be used to automate many types of routine tasks that wouldn’t typically be considered ETL operations. SSIS has replaced Data Transformation Services (DTS), which was bundled with SQL Server 7/2000. SSIS is “fully supported” in the Enterprise and Developer Editions. The Workgroup Edition offers some “limited” support (it includes the Import/Export Wizard, the SSIS command prompt tools, the Package Designer, the Execute SQL task, the OLE DB source, and destination adapters; SSIS is not installed as a service). The Standard Edition does not support some data mining and Analysis Services–specific tasks and transforms. For a detailed list of the features supported in each edition, see SQL Server 2005 Books Online.

As you look at the objectives this chapter covers, by far the hardest task is learning how to construct complex SSIS packages. In fact, building a complex SSIS package requires a good understanding of a lot of bits and pieces, most of which we’ll cover in this chapter. It’s unlikely that you’ll actually have to build a package as part of your exam; more likely, you’ll have to choose from some options that would achieve the stated goal. In this chapter, we’ve included a basic tutorial to show you the highlights of building SSIS packages, but the truth is you need to spend some additional time working with as many of the tasks and transforms as you can so that you can really appreciate the different ways you might solve a challenge using SSIS.

The other objectives are straightforward and are topics that for the most part are easy to explain (and understand!). That’s not to say you should just read this chapter, though; there is no substitute for walking through as many parts of SSIS as you can-it can really pay off at exam time.