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.
This video is available with closed captions. To view closed captions, click CC on the video control bar.
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.
SQL Server Import and Export wizard offer 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 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.
First, the data provider for the data source and we have several choices.
We can select the .NET Framework Data Provider for SQL Server, the Microsoft OLE DB Provider for SQL Server or the SQL Server Native Client.
In this case we'll select the SQL 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.
Then, finally, we need to select the database that contains the data.
We're going to select the AdventureWorks database.
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 we'll need to specify the following.
First, the path and file name for the workbook in which to store the data.
We're going to browse to an existing workbook. Next we need to select the version of Excel that is used by the workbook.
In this case, we'll select the Microsoft Excel 97 version.
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 that the data's copied from.
We'll write a query that returns 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.
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 data destination that 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 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 we'll change the name of the worksheet in the workbook, and we're going to change the name of the worksheet 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 the data type of Name, Color, and ProductNumber columns to VARCHAR so that we can modify the column length to better fit the column data.
As you'll note, the SellStartDate data type is date time.
This data type indicates that the SellStartDate column contains both date data and 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 the previous wizard pages.
We'll click Finish to complete the 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're going to add a Data Viewer in the data flow so that we can 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, the 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 using an Excel connection manager.
In this package the Excel connection manager is titled DestinationConnectionExcel.
So, when you open the workbook, you find that there is a worksheet titled ProductTable and this worksheet contains the data from the Production.Product table; the Name, Color, ProductNumber, and SellStartDate columns.
As you'll notice, the SellStartDate column now only contains date data rather than containing date and time data.
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 by 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 website to view other Microsoft SQL Server videos.