Article ID: 319951
Article Last Modified on 12/12/2003
APPLIES TO
- Microsoft SQL Server 2000 64-bit Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q319951
IN THIS TASK
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
Selecting a Destination File
- 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.
- 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).
- 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.
Selecting a Destination Table
- 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.
- 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).
- 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
- 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
- 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
- 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.
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.
REFERENCES
For additional information about issues that you experience when you use Excel as a database, click the article number below to view the article in the Microsoft Knowledge Base:
257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
For additional information about known issues that you experience when using Excel with DTS, click the article numbers below to view the articles in the Microsoft Knowledge Base:
236605 PRB: DTS Wizard May Not Detect Excel Column Type for Mixed Data
281517 PRB: Transfer of Data from Jet 4.0LEDB Source Fails with Buffer Overflow Error
207446 BUG: Cannot Import Excel 97 Spreadsheet with 256 or More Columns
For additional information about how to use the SQL Server DTS feature, see the following SQL Server Books Online articles:
"Complex Transformation Sample from SQL Server to Excel"
"Creating a DTS Package with the DTS Import/Export Wizard"
"DTS Driver Support for Heterogeneous Data Types"
back to the top
Keywords: kbhowto kbhowtomaster KB319951
