Professional SQL Server™ 2005 Integration Services

8.1. Excel

Excel is the favorite "database" software of many people without database expertise. We were amazed when an acquaintance of one of the authors confessed that she used Excel to do everything, even write letters. SQL Server Integration Services has Data Flow source and destination components made just for Excel. You can be sure that these components will be used in many SSIS packages, because data is often imported from Excel files into a SQL Server database or exported into Excel for many high-level tasks such as sales forecasting. Because Excel is so easy to work with, it is common to find inconsistencies in the data. For example, while possible to implement, it is less likely for an Excel workbook to have lookup lists or data type enforcement in place. It's often possible for the person entering data to type a note in a cell where a date should go. Of course, cleansing the data is part of the ETL process, but it may be even more of a challenge when importing from Excel.

In this section, you'll look at both exporting to and importing from Excel as the AdventureWorks staff performs their annual inventory.

8.1.1. Exporting to Excel

The easiest way to export data from SQL 2005 to an Excel file is to use the SQL Server Import and Export Wizard. Since using the wizard is covered in Chapter 2, you will use BI DS to create SSIS packages to import and export Excel data. The first example shows how to create a package that exports a worksheet the AdventureWorks inventory staff ...