HOW TO: Transfer Data to Excel by Using SQL Server Data Transformation Services (319951)

SUMMARY

SQL Server Data Transformation Services (DTS) is a powerful tool that you can use to easily transfer data between OLE DB data sources, transforming that data in the process (if you chose to do so). This article describes how to use the DTS Import/Export Wizard to export data from Microsoft SQL Server or from another data source to a Microsoft Excel worksheet. Many of the same considerations apply if you are configuring your own Transform Data task in DTS Designer.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

• Microsoft SQL Server 2000 or Microsoft SQL Server 7.0 installed on a compatible Microsoft Windows operating system.
• Microsoft Jet 4.0 and its related files as installed by MDAC version 2.1 or 2.5 or by another product.

This article assumes that you have at least basic familiarity with the following topics:

• SQL Server
• Data Transformation Services
• Excel worksheets

back to the top

Selecting a Destination File

1. Start the DTS Import/Export Wizard, and then select a data source on the Choose a Data Source tab. After you select a data source, the focus changes to the Choose a Destination tab.
2. In the Destination list, click Microsoft Excel 97-2000 as the destination database type. Use this same type for Microsoft Excel 2002 (Microsoft Office XP).
3. In the File name box, click the ellipsis to locate an existing Excel workbook file. This file must not be open in Excel while you are completing the wizard. If you have Excel installed on your computer, you can create a new Excel file at this point without leaving the wizard. To do so, right-click the Select file text, point to New, and then click Microsoft Excel Worksheet.

back to the top

Selecting a Destination Table

1. With the Select Source Table(s) and View(s) tab in focus, in the Source column, select the table and view (or multiple tables and views) that you want to export to Excel.
2. By default, the wizard fills in a destination table with the same name as the source table in the Destination column.
   
   NOTE: This creates both a worksheet and a named range with the same name in the destination workbook; however, DTS uses the named range in most circumstances.
   
   You can also select an existing worksheet or named range (the names that are followed by a $, such as Sheet1$, are worksheet names).
3. In the Transform column, click the ellipsis to open an additional dialog box in which you click one of the following options:
   • Create the destination table
     
     This is the only available option if the destination table does not yet exist. If the table already exists, this option is not available, in which case, there is an additional option to drop and re-create the table.
   • Delete and replace the existing destination rows
     
     If you try to use this option with Excel, it fails (therefore you cannot use it).
   • Append the new rows to the existing rows
4. In the remaining steps in the wizard, you can save and run the DTS package. To export new or changed data on a regular basis, save and optionally schedule the package before you quit the wizard.

back to the top

Troubleshooting

Selecting the Excel File

• Do not have the Excel workbook open while you are completing the DTS wizard.

Selecting the Excel Table

• If you select the drop and re-create the destination table option, the drop command fails the first time that you run the package because the table does not exist; however, the export succeeds.
• If you select create the destination table without the drop and re-create option, the create command fails on subsequent executions because the table already exists; however, the export succeeds.
• If you execute a CREATE TABLE statement against Excel, such as the statement that the wizard generates, this creates both a worksheet and a named range with the same name; however, DTS works with the named ranges unless you specify otherwise. To view these named ranges in Excel: On the Insert menu, click Name, and then click Define.
• You cannot delete and replace existing rows in the Transform dialog box, because you cannot delete Excel worksheet rows through OLE DB.
• If you manually blank out the exported data in the destination worksheet, export the data again to have the new data appended below the blank rows because the driver is looking at the saved definition of the named range and it is expanding it for the new rows. If you delete all the rows of old data in the worksheet, this behavior does not occur because deleting the rows changes the saved definition of the named range. However, it is preferable to use the drop and re-create option to replace the existing data.

back to the top