SQL Server 2008 Integration Services TutorialDownloads Required:
Microsoft says that SQL Server Integration Services (SSIS) “is a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing.” A simpler way to think of SSIS is that it's the solution for automating data movements. SSIS provides a way to build packages made up of tasks that can move data around from place to place and alter it on the way. There are visual designers (hosted within Business Intelligence Development Studio) to help you build these packages as well as an API for programming SSIS objects from other applications.
In this chapter, you'll see how to build and use SSIS packages. First, though, we'll look at a simpler facet of SSIS: The SQL Server Import and Export Wizard.
Though SSIS is almost infinitely customizable, Microsoft has produced a simple wizard to handle some of the most common ETL tasks: importing data to or exporting data from a SQL Server database. The Import and Export Wizard protects you from the complexity of SSIS while allowing you to move data between any of these data sources:
You can launch the Import and Export wizard from the Tasks entry on the shortcut menu of any database in the Object Explorer window of SQL Server Management Studio.
To import some data using the Import and Export Wizard, follow these steps:
The Import and Export Wizard is easy to use, but it only taps a small part of the functionality of SSIS. To really appreciate the full power of SSIS, you'll need to use BIDS to build an SSIS package. A package is a collection of SSIS objects including:
You'll see how to build each of these components of a package in later sections of the chapter, but first, let's fire up BIDS and create a new SSIS package.
To create a new SSIS package, follow these steps:
Figure 16-3 shows the new, empty package.
Figure 16-3: Empty SSIS package
SSIS uses connection managers to integrate different data sources into packages. SSIS includes a wide variety of different connection managers that allow you to move data around from place to place. Table 16-1 lists the available connection managers.
Table 16-1: Available Connection Managers
To create a Connection Manager, you right-click anywhere in the Connection Managers area of a package in BIDS and choose the appropriate shortcut from the shortcut menu. Each Connection Manager has its own custom configuration dialog box with specific options that you need to fill out.
To add some connection managers to your package, follow these steps:
Figure 16-4: Defining a Flat File Connection Manager
Figure 16-5 shows the SSIS package with the three Connection Managers defined.
Figure 16-5: An SSIS package with two Connection Managers
The Control Flow tab of the Package Designer is where you tell SSIS what the package will do. You create your control flow by dragging and dropping items from the toolbox to the surface, and then dragging and dropping connections between the objects. The objects you can drop here break up into four different groups:
Table 16-2: SSIS control flow tasks
Table 16-3: SSIS maintenance plan tasks
Table 16-4: SSIS containers
To add control flows to the package you've been building, follow these steps:
Figure 16-6 shows the completed set of control flows.
Figure 16-6: Adding control flows
As it stands, this package uses the file system task to copy the file specified by the DepartmentList connection to the file specified by the DepartmentListBackup connection, overwriting any target file that already exists. It then executes the data flow task. In the next section, you'll see how to configure the data flow task.
The Data Flow tab of the Package Designer is where you specify the details of any Data Flow tasks that you've added on the Control Flow tab. Data Flows are made up of various objects that you drag and drop from the Toolbox:
Table 16-5: Data flow sources
Table 16-6: Data Flow Transformations
Table 16-7: Data Flow Destinations
To customize the data flow task in the package you're building, follow these steps:
Figure 16-7: Setting up the OLE DB Source
Figure 16-8: Setting up the Character Map Transformation
Figure 16-10 shows the completed set of data flows.
Figure 16-10: Adding data flows
The data flows in this package take a table from the Chapter16 database, transform one of the columns in that table to all uppercase characters, and then write that transformed column out to a flat file.
SSIS packages also support a complete event system. You can attach event handlers to a variety of events for the package itself or for the individual tasks within a package. Events within a package "bubble up." That is, suppose an error occurs within a task inside of a package. If you've defined an OnError event handler for the task, then that event handler is called. Otherwise, an OnError event handler for the package itself is called. If no event handler is defined for the package either, the event is ignored.
Event handlers are defined on the Event Handlers tab of the Package Designer. When you create an event handler, you handle the event by building an entire secondary SSIS package, and you have access to the full complement of data flows, control flows, and event handlers to deal with the original event.
To add an event handler to the package we've been building, follow these steps:
This event handler will be called when the Data Flow Task finishes executing, and will insert one new row into the tracking table when it is called.
Now that you've created an entire SSIS package, you're probably ready to run it and see what it does. But first, let's look at the options for saving SSIS packages. When you work in BIDS, your SSIS package is saved as an XML file (with the extension dtsx) directly in the normal Windows file system. But that's not the only option. Packages can also be saved in the msdb database in SQL Server itself, or in a special area of the file system called the Package Store.
Storing SSIS packages in the Package Store or the msdb database makes it easier to access and manage them from SQL Server's administrative and command-line tools without needing to have any knowledge of the physical layout of the server's hard drive.
Saving Packages to Alternate Locations
To save a package to the msdb database or the Package Store, you use the File > Save Package As menu item within BIDS.
To store copies of the package you've developed, follow these steps.
Running a Package
You can run the final package from either BIDS or SQL Server Management Studio. When you're developing a package, it's convenient to run it directly from BIDS. When the package has been deployed to a production server (and saved to the msdb database or the Package Store) you'll probably want to run it from SQL Server Management Studio.
Running a Package from BIDS
With the package open in BIDS, you can run it using the standard Visual Studio tools for running a project. Choose any of these options:
To run the package that you have loaded in BIDS, follow these steps:
Figure 16-11: Executing a package in the debugger
Running a Package from SQL Server Management Studio
To run a package from SQL Server Management Studio, you need to connect Object Browser to SSIS.
SSIS 2008 Tutorial: Exercises
One common use of SSIS is in data warehousing - collecting data from a variety of different sources into a single database that can be used for unified reporting. In this exercise you'll use SSIS to perform a simple data warehousing task.
Use SSIS to create a text file, c:\EmployeeDept.txt, containing the last names, department names, start and end dates of the AdventureWorks2008 employees. Retrieve the last names from the Person.Person table and the department start and end dates from the HumanResources.EmployeeDepartmentHistory table in the AdventureWorks2008 database, and the department names from the Chapter16 database.
You can use the Merge Join data flow transformation to join data from two sources. One tip: the inputs to this transformation need to be sorted on the joining column.
Solutions to Exercises
Figure 16-14: Data flows to merge two sources
Figure 16-15: Setting up a Merge Join