The SQL Server Import and Export Wizard how-to guide

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.

sSQLServer SQLServerImportExportWiz 090314 mobile The SQL Server Import and Export Wizard how to guide 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.

sSQLServer SQLServerChooseDest 090314 mobile The SQL Server Import and Export Wizard how to guide 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.

sSQLServer SQLServerTableCopyQuery 090314 mobile The SQL Server Import and Export Wizard how to guide Figure 3

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

sSQLServer SQLServerTablesViews 090314 mobile The SQL Server Import and Export Wizard how to guide 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.

sSQLServer SQLServerDataTypeMapping 090314 mobile The SQL Server Import and Export Wizard how to guide 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.

sSQLServer SQLServerCompleteWiz 090314 mobile The SQL Server Import and Export Wizard how to guide Figure 6

Click Finish to run the package.

sSQLServer SQLServerExecution 090314 mobile The SQL Server Import and Export Wizard how to guide 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.

sSQLServer SQLServerDataSource 090314 mobile The SQL Server Import and Export Wizard how to guide 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.

sSQLServer SQLChooseDest 090314 mobile The SQL Server Import and Export Wizard how to guide 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.

sSQLServer SQLServerSourceTablesViews 090314 mobile The SQL Server Import and Export Wizard how to guide 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.

sSQLServer SQLServerCompletePackage 090314 mobile The SQL Server Import and Export Wizard how to guide 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

Dig deeper on Microsoft SQL Server Tools and Utilities

This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

Microsoft SQLServer news and trends