Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

VIII.4.3. Using SQL Server Integration Services

Now that you're up to speed on some of the capabilities offered by SQL Server Integration Services, the balance of the chapter walks you through a practical integration example. In this scenario, you load a Microsoft Excel spreadsheet containing new customer and payment transaction records into a database. What makes this exercise a little more challenging than a basic import operation is that the records found in the spreadsheet need to be split and then placed into two different tables. The tables' structures are defined here:

CREATE TABLE [dbo].[students]
(
   [student_id] [int] NOT NULL,
   [last_name] [varchar](50) NOT NULL,
   [first_name] [varchar](50) NOT NULL,
   [credit_limit] [money] NOT NULL
)

CREATE TABLE [dbo].[payments]
(
   [payment_id] [int] NOT NULL,
   [student_id] [int] NOT NULL,
   [payment_date] [datetime] NOT NULL,
   [payment_amount] [money] NOT NULL
)

After the integration project is created, the next step is to deploy it to the server where it was created.

This example is extremely simple; in reality, you face complex requirements for data formatting, transformation, exception handling, publishing to multiple servers, and so on. As described in the previous section, SQL Server Integration Services is designed to support these types of real-world constraints gracefully.

To make this section more readable, the instructions are broken into several step groups. In keeping with the philosophy followed throughout the book, you use SQL ...