SSIS: Exporting 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.

Read the video transcript: http://msdn.microsoft.com/en-us/libra...

---

Closed Caption:

my name is Carlo 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 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 a data provider for the
datasource and we have several choices
we can select the dotnet framework data
provider for sequel server $YEAR
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 Emily 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 we 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 in
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 that
destination that we've selected are
listed in this demonstration the
datasource 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
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 .
DTS x 128 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
eight 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 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 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