Exporting Data Using the DTS ImportExport Wizard

Exporting Data Using the DTS Import/Export Wizard

DTS can export data from SQL Server to a variety of data stores as easily as it can import data into SQL Server from a variety of data stores. Although exporting data using the DTS Import/Export Wizard is frequently thought of as exporting data from a SQL Server database into another data format, from the perspective of DTS, exporting is simply copying data from one data source to another data source. In this section, you will export data from a SQL Server database to an Excel worksheet (which is the same as importing to an Excel worksheet from a SQL Server database). You could just as easily export data from an Excel worksheet to a text file, or from a text file to an Access database. The data source and the data destination can be any data structure to which DTS can connect. Neither the data source nor the data destination need be a SQL Server database.

Copy data from a SQL Server database to an Excel worksheet

In the following procedure, you will learn how to export data to an Excel worksheet from a SQL Server database. Business executives are familiar with Excel and often use it to work with summarized data, such as weekly or monthly reports .

1. To launch the DTS Import/Export Wizard from within SQL Server Enterprise Manager, right-click the (local) (Windows NT) server icon, point to All Tasks, and then click Export Data.

   The wizard that appears when you click Export Data is the same wizard that appears when you click Import Data ”there are two menu items, but only one wizard.

2. Click Next to select a data source from which you will copy data.

   In this procedure, you will use the SBS_OLTP database on your local server as your data source.

3. Verify that Microsoft OLE DB Provider For SQL Server appears as the data source, ensure that Windows Authentication is selected, and ensure that (local) appears in the Server list. In the Database list, select SBS_OLTP, and then click Next to select a data destination.

   In this procedure, you will use an Excel worksheet as your data destination. Notice that the list of data destinations is the same as the list of data sources.

4. In the Destination list, select Microsoft Excel 97-2000.

   The page changes to reflect the information required to connect to or create an Excel worksheet.

5. In the File Name box, type C:\Microsoft Press\SQL DTS SBS\Ch1\WorkingFolder\UnshippedOrders.xls and then click Next to specify what DTS will copy from the data source to the data destination.

   In this procedure, you will use a query to consolidate and filter data as it is being copied from the SBS_OLTP database to an Excel worksheet.

6. Click Use A Query To Specify The Data To Transfer, and then click Next to specify the query DTS will use.

   In this procedure, you will use the same query that you used in the Copy data between SQL Server databases using a SQL query procedure. However, you will paste this query from a saved script file rather than use the Query Builder to create the query.

7. Click Browse, navigate to C:\Microsoft Press\SQL DTS SBS\Ch1\ChapterFiles in the Look In list, and then double-click UnshippedOrders.sql.

   The saved query is pasted into the Query Statement box.

   

8. Click Next to modify the schema or the data as it is copied to the data destination.

9. Click the ellipsis in the Transform column, verify the Create Destination Table option button is selected, select the Drop And Recreate Destination Table check box, and then click OK.

10. Click Next to choose whether to execute the package immediately and to save, schedule, or replicate the package.

11. Click Save DTS Package, click Structured Storage File, and then click Next to provide the details for the save options.

12. Type SQL_DTS_SBS_1.5 in the Name box, type DTS Step By Step Chapter 1 Package 5 in the Description box, type mypassword in the Owner Password box, and type the following in the File Name box:

    C:\Microsoft Press\SQL DTS SBS\Ch1\WorkingFolder\SQL_DTS_SBS_1.5.dts

    Click Next to review the selections that you made in the wizard.

13. Click Finish to save and execute the package.

14. Click OK to acknowledge that the package copied one table successfully from Microsoft SQL Server to Microsoft Excel 97-2000.

    Notice that the wizard saved the package, attempted to drop the Results table (this step failed because the Results table does not yet exist), created the Results table, and then copied the data from the Results table into an Excel worksheet. Again, since there are no orders with a RequiredDate greater than or equal to now, the results show that zero rows of data were copied.

15. Click Done.

You have successfully exported data to an Excel worksheet from a SQL Server database using a SQL query. You have learned that it is as simple to export data to an OLE DB or ODBC data destination as it is to import data from an OLE DB or ODBC data source.