Exporting SQL Server Data to Excel (SQL Server Video)

Video Summary

This video demonstrates how to use the SQL Server Import and Export Wizard to create a package that exports data from a SQL Server database to a Microsoft Excel spreadsheet.

Video Transcript

Hello, my name is Carla Sabotta. I write documentation for the Microsoft SQL Server Integration Services product.

In this video, I’m going to show you how to use the SQL Server Import and Export Wizard to create a package that exports data from a SQL Server database to a Microsoft Excel spreadsheet. You’ll learn how to export data from a SQL Server database to an Excel spreadsheet by using the SQL Server Import and Export Wizard. The SQL Server Import and Export Wizard offers the simplest method to create a Integration Services package that copies data from a source to a destination.

You can start the wizard from the Start menu, from SQL Server Management Studio, from Business Intelligence Development Studio, or at the command prompt.

In this demonstration, we’ll start the wizard from Business Intelligence Development Studio.

The first step is to choose and configure the source of the data that you want to copy, on the Choose a Data Source page of the wizard

In this demonstration, we’ll select a SQL Server data source and specify the following:

• The data provider for the data source. We have several to choose from (SQL Server Native Client, .Net Framework Data Provider for SQL Server, Microsoft OLE DB Provider for SQL Server) and we’ll select the SQL Server Native Client provider.

• The name of the server that contains the data—local server.

• The database that contains the data—AdventureWorks.

The next step is to choose and configure the destination of the data that you want to copy, on the Choose a Destination page of the wizard.

In this demonstration, we’ll select a Microsoft Excel destination and specify the following:

• Specify the path and file name for an existing workbook in which to store the data.

• Select the version of Excel that is used by the workbook—Microsoft Excel 97-2003.

Now, we need to specify how to copy the data, on the Specify Table Copy or Query page of the wizard.

In this demonstration, we’ll write a query to specify which table and table columns the data is copied from.

The query will return data from the Name, Color, ProductNumber, and SellStartDate columns in the Production.Product table.

You can click the Parse button to check the syntax of the SQL statement.

In the Select Source Tables and Views page of the wizard, the data source and data destination we’ve selected are listed.

In this demonstration, the data source refers to the SQL statement we entered earlier, to return data from selected columns in a table, in a SQL Server database. The data destination refers to a worksheet contained in the Excel workbook that we selected earlier.

We have the option of changing the name of the destination, which we’ll change to ProductTable.

We can view how the source columns are mapped to the destination columns, and modify column metadata, by clicking Edit Mappings.

We’re going to change data type of the Name, Color, and ProductNumber columns to VarChar, so we can modify the column length to better fit the column data.

Notice that the data type for the SellStartDate column is Datetime. This indicates that the column in the Production.Product table contains date and time data.

Finally, we review the options we’ve chosen, on the Complete the Wizard page.

If you want to modify any of the options for this session of the wizard, you click the Back button to return to previous wizard pages. We’ll click Finish to complete wizard.

The package that the SQL Server Import and Export Wizard created now appears in the Solution Explorer, under the SSIS packages node. The package is titled "Package1.dtsx."

We’ll add a data viewer to view the data that is exported from the SQL Server database table to the Excel worksheet.

Now, we’re ready to run the package.

As you can see, data from the Name, Color, ProductNumber, and SellStartDate columns in the Production.Product table are exported to the Excel worksheet. The Excel Destination connects to the Excel workbook that contains the worksheet, by using an Excel Connection Manager.

Now, we’ll open the Excel file to view the exported data.

You’ll notice that the SellStartDate column now only contains date data rather than the date/time data contained in the Production.Product table. This is because the Excel driver interprets date/time columns as date columns.

This video demonstrated how to create a package to export data from a SQL Server database to a Microsoft Excel spreadsheet using the SQL Server Import and Export Wizard.

Thank you for watching this video. We hope that you have found this of value, and will return to the Web site to view other Microsoft SQL Server Videos.