How to Export SQL Server Data to Excel

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.

---

Closed Caption:

my name is Carlos sobota i write
documentation for the microsoft sequel
server integration services product in
this video I'm going to show you how to
use the sequel server import and export
wizard to create a package that exports
data from a sequel server database to a
Microsoft Excel spreadsheet you learn
how to export data from a sequel server
database to an Excel spreadsheet by
using the sequel server import and
export wizard equals server import and
export wizard offers the simplest method
to create an integration services
package that copies data from a source
to a destination you can start the
wizard from the start menu from sequel
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 will select a
sequel server data source and specify
the following first the data provider
for the data source and we have several
choices we can select the dotnet
framework data provider for sequel
server the microsoft oledb provider for
sequel server or the sequel server
native client in this case we'll select
the sequel server Native Client next we
need to specify the name of the server
that contains the database
in this case we're going to select the
local server and then finally we need to
select the database that contains the
data and we're going to select the
adventureworks database
the next step is to choose configure the
destination of the data that you want to
copy on the choose a destination page of
The Wizard
in this demonstration will select
Microsoft Excel destination
and need to and we'll need to specify
the following first the path and
filename for the workbook in which to
store the data
we're going to browse to an existing
workbook next week is just to select the
version of Excel that is used by the
workbook in this case we'll select the
microsoft still 97 version now we need
to specify how the data how to copy the
data on the specified table copy or
query page of The Wizard
in this demonstration will write a query
to specify which table and table columns
that the data is copied from will write
a query that returns data from the name
color product number and cell start date
columns in the production . product
table
you can click the parse button to check
the syntax of the sequel statement and
as you can see the syntax of our
statement is valid in the select source
tables and views page of The Wizard the
data source and the day edition
destination that we've selected are
listed in this demonstration the data
source refers to the sequel statement we
entered earlier to return data from
selected columns in a table in a sequel
server database the 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 in this case will
change the name of the worksheet in the
workbook and we're going to change the
name of the worksheet to product table
we can view how the source columns are
mapped to the destination columns and
modify call metadata by clicking edit
mappings we're going to change the data
type of the name color and product
number columns to arch are so that we
can modify the column length to better
fit the column data
and as you'll note the cells start date
data type is datetime this data type
indicates that the cells start date
column contains both eight data and time
column time data click OK to save our
settings and then click Next
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 will click finish to
complete the wizard the package that the
sequel server import and export was are
created
now appears in the solution Explorer
under the ssis packages node and the
packages titled package one . d tsx
we're going to add a data viewer in the
data flow so that we can view the data
that is exported from the sequel server
database table to the excel worksheet
now we're ready to run the package
and as you can see the data from the
name color product number and cell start
a columns in the production . product
table are exported to the excel
worksheet the excel destination contains
connects to the Excel workbook that
contains the worksheet using an Excel
connection manager in this package the
excel connection manager is titled
destination connection excel so when you
open the workbook you find that there is
a worksheet titled product table and in
this worksheet contains the data from
the production . product table
the name color product number and cell
start date columns and as you'll notice
the cells start date column now only
contains date data rather than
containing date and time data and this
is because the excel driver interprets
eight-time columns as date columns this
video demonstrated how to create a
package to export data from a sequel
server database to a mount Microsoft
Excel spreadsheet by using the sequel
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 website to view
other Microsoft sequel server videos