Get started Bring yourself up to speed with our introductory content.

The SQL Server Import and Export Wizard how-to guide

Basit Farooq provides a step-by-step explanation for using the SQL Server Import and Export Wizard to transfer data between SQL Server databases and Microsoft Excel worksheets.

SQL Server 2014 provides a variety of methods to transfer data between SQL Server databases and Microsoft Excel documents. One method is the SQL Server Import and Export Wizard, and this article provides a step-by-step guide for using the wizard.

The SQL Server Import and Export Wizard

The SQL Server Import and Export Wizard is based in SQL Server Integration Services (SSIS). You can use SSIS to build extraction, transformation and load (ETL) packages and to quickly create packages for moving data between Microsoft Excel worksheets and SQL Server databases.

Launch SQL Server Import and Export Wizard by one of the following methods:

  • Method 1: On the Start menu, roll the cursor over All Programs, scroll down to Microsoft SQL Server and then click Import and Export Data.
  • Method 2: In SQL Server Data Tools (SSDT), right-click the SSIS Packages folder and then click SSIS Import and Export Wizard.
  • Method 3: In SQL Server Data Tools, go to the Project menu and click SSIS Import and Export Wizard.
  • Method 4: In SQL Server 2014 Management Studio, connect to the Database Engine server type, expand Databases, right-click a database, point to Tasks and then click Import Data or Export data.

Exporting data from a SQL Server database to a Microsoft Excel worksheet

To export data, start the SQL Server Import and Export Wizard. Then, click the Next button to bypass the Welcome Screen. On the Choose a Data Source page (Figure 1), configure the following:

  • Data Source: Choose SQL Server Native Client 11.0 from the drop-down menu.
  • Server Name: Type the name of the SQL Server instance that contains the source data.
  • Authentication: Choose authentication mode for the data source connection.
  • Database: Choose the database that contains the source data.
Choose a Data Source (Import)
Figure 1

Click Next to go to the Choose a Destination page (Figure 2). On this page, configure the following:

  • Destination: Choose Microsoft Excel from the drop-down menu.
  • Excel File Path: Type in the Microsoft Excel worksheet operating system path.
  • Excel Version: Select the version of the Microsoft Excel worksheet.
Choose a Destination (Import)
Figure 2

Click Next to proceed to Select Table Copy or Query page (Figure 3). You have two options on this page. You can either select to copy tables and views or to copy the results of a query from the data source. We will be copying all the data from the HumanResources.Department table of the AdventureWorks2012 database to our Excel worksheet. We specified this destination on the Choose a Destination page. Click the circle next to the words Copy data from one or more tables or views. Click the Next button to continue.

Specify Table Copy or Query (Import)
Figure 3

On the Select Source Tables and Views page (Figure 4), select the HumanResources.Department table and then click Next button.

Select Source Tables and Views (Import)
Figure 4

Click the Next button to go to Review Data Type Mapping page (Figure 5). This is where you'll see how different data types are mapped between the source and the destination and how any conversions issues will be handled.

Review Data Type Mapping (Import)
Figure 5

Click the Next button to proceed to the Save and Run Package page, where you can indicate whether you want to save the SSIS package or run it immediately. You can set the package protection level when you save the package. If the protection level uses a password, provide the password here. For more information about package protection levels, see Access Control for Sensitive Data in Packages.

Select the Run immediately option and click the Next button. This takes you to the Complete the Wizard page (Figure 6). Here you can view the choices you made.

Complete the Wizard (Import)
Figure 6

Click Finish to run the package.

The execution was successful
Figure 7

Importing data into a SQL Server database from a Microsoft Excel worksheet

Start the SQL Server Import and Export Wizard to import data from an Excel worksheet to a SQL Server database. Click Next to bypass the welcome screen. On the Choose a Data Source page (Figure 8), configure the following:

  • Data Source: Choose Microsoft Excel from the drop-down menu.
  • Excel File Path: Specify the path of the Excel file from which you are importing data.
  • Excel Version: Choose the Excel version where you created the Excel.
Choose a Data Source (Export)
Figure 8

Click Next to go to the Choose a Destination page (Figure 9), and configure the following:

  • Data Source: Choose SQL Server Native Client 11.0 from the drop-down menu.
  • Server Name: Type the name of the destination database's SQL Server instance.
  • Authentication: Choose the appropriate authentication mode for the data destination connection.
  • Database: Choose which database to copy the data into.
Choose a Destination (Export)
Figure 9

Click Next to proceed to the Select Table Copy or Query page. On this page, choose the Copy data from one or more tables or views option. Click the Next button to continue. In this case we'll be copying the data from the Regions worksheet.

On the Select Source Tables and Views page (Figure 10), select the Regions worksheet and then click the Next button.

Select Source Tables and Views (Export)
Figure 10

Click the Next button to go to the Save and Run Package page. Here, select the Run immediately option and click the Next button. This takes you to the Complete the Wizard page (Figure 11) where you can view the choices you made.

Complete the Wizard (Export)
Figure 11

Click Finish to run the package.

Next Steps

Learn more about SSIS' part in Microsoft's Enterprise Integration Management

Find out how to use the SSIS-based Microsoft SQL Server Data Profiling Tool

Here are some tips for creating parameters and undoing operations in SSIS

This was last published in September 2014

Essential Guide

A guide to using Excel as financial accounting software

Join the conversation

6 comments

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

Thank you
Cancel
Thanks!
Cancel
Good info for somebody just starting out.
Cancel
Hi, how to import an Excel data which contains date field but its datatype in DB is string. So when I import excel data into DB, date is not stored instead a random is being stored.

Can you please help?
Cancel
I'm using datarails, much easier.

Cancel
Thanks guy. 

At first, I wrote a form to get datatable of one datasource. And transfer that table to another datasource. But it's contrainted by data size. Apparently I can transfer only 1 milions rows at a time. But now I found this. Really convenient. Thank you
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close