Import and Export Data with the SQL Server Import and Export Wizard

 

Updated: January 20, 2017

SQL Server Import and Export Wizard is a simple way to copy data from a source to a destination. This overview describes the data sources that the wizard can use as sources and destinations, and the permissions you need to run the wizard.

This topic provides only an overview of the wizard. Here are some other basic tasks.

  • Start the wizard. If you're ready to run the wizard and just want to know how to start it, see Start the SQL Server Import and Export Wizard.
  • Learn about the steps in the wizard. If you're looking for info about one of the steps in the wizard, select the page you want from the list here - Steps in the SQL Server Import and Export Wizard. There's a separate page of documentation for each page of the wizard. Or, tap the F1 key from any page or dialog box of the wizard to see documentation for the current step.
  • Learn how to connect to data sources and destinations. If you're looking for info about how to connect to your data, select the page you want from the list here - Connect to data sources with the SQL Server Import and Export Wizard. There's a separate page of documentation for each commonly used data source.

Get the wizard. If you want to run the wizard, but you don't have Microsoft SQL Server installed on your computer, you can install the SQL Server Import and Export Wizard by installing SQL Server Data Tools (SSDT). For more info, see Download SQL Server Data Tools (SSDT).

The SQL Server Import and Export Wizard can copy data to and from the data sources listed in the following table. To connect to some of these data sources, you may have to download and install additional files.

System_CAPS_ICON_tip.jpg Tip

For info about how to connect to your data, select the page you want from the list here - Connect to data sources with the SQL Server Import and Export Wizard. There's a separate page of documentation for each commonly used data source.

Data sourceDo you have to download additional files?
Enterprise databases
SQL Server, Oracle, and others.
The SQL Server Data Tools (SSDT) install the files that you need to connect to SQL Server. But SSDT doesn't install all the files that you need to connect to other enterprise databases such as Oracle, IBM DB2, or Informix.

If you already have the client software installed for your enterprise database system, then you typically have what you need to make a connection. If you don't have the client software installed, ask the database administrator how to install a licensed copy.
Open source databases
MySql, PostgreSQL, SQLite, and others.
You have to download additional files to connect to these data sources.

- For MySql, see MySQL Connectors.
- For PostgreSQL, see psqlODBC - PostgreSQL ODBC driver, or select from other providers and drivers available online.
- For SQLite, select from among several providers and drivers available online.
Text files (flat files).No additional files required.
Microsoft Excel and Microsoft Access filesMicrosoft Office doesn't install all the files that you need to connect to Excel and Access files as data sources. Get the following download - Microsoft Access 2016 Runtime.
Azure data sources
Currently only Azure Blob Storage.
The SQL Server Data Tools don't install the files that you need to connect to Azure Blob Storage as a data source. Get the following download - Microsoft SQL Server 2016 Integration Services Feature Pack for Azure.
Any other data source for which a connector is availableYou typically have to download additional files to connect to the following types of data sources.

- Any source for which an ODBC driver is available. Make an ODBC (Open Database Connectivity) connection by selecting the .Net Framework Provider for Odbc on the Choose a Data Source or Choose a Destination page of the wizard, and then providing a connection string or an existing DSN (Data Source Name) that references the ODBC driver.
- Any source for which a .Net Framework Data Provider is available.
- Any source for which an OLE DB Provider is available.

Third-party components that provide source and destination capabilities for less commonly used data sources are sometimes marketed as add-on products for SQL Server Integration Services (SSIS).

To run the SQL Server Import and Export Wizard successfully, you have to have at least the following permissions. If you already work with your data source and destination, you probably already have the permissions that you need.

You need permissions to do these thingsIf you're connecting to SQL Server, you need these specific permissions
Connect to the source and destination databases or file shares.Server and database login rights.
Export or read data from the source database or file.SELECT permissions on the source tables and views.
Import or write data to the destination database or file.INSERT permissions on the destination tables.
Create the destination database or file, if applicable.CREATE DATABASE or CREATE TABLE permissions.
Save the SSIS package created by the wizard, if applicable.If you want to save the package to SQL Server, permissions sufficient to save the package to the msdb database.

The wizard uses SQL Server Integration Services (SSIS) to copy data. SSIS is a tool for extracting, transforming, and loading data (ETL). The pages of the wizard use some of the language of SSIS.

In SSIS, the basic unit is the package. The wizard creates an SSIS package in memory as you move through the pages of the wizard and specify options.

At the end of the wizard, if you have SQL Server Standard Edition or higher installed, you can optionally save the SSIS package. Later you can reuse the package and extend it by using SSIS Designer to add tasks, transformations, and event-driven logic. The SQL Server Import and Export Wizard is the simplest way to create a basic Integration Services package that copies data from a source to a destination.

For more info about SSIS, see SQL Server Integration Services.

System_CAPS_ICON_tip.jpg Tip

Tap the F1 key from any page or dialog box of the wizard to see documentation for the current page.

Start the wizard. For more info, see Start the SQL Server Import and Export Wizard.

Get started with this simple example of the Import and Export Wizard
Data Type Mapping in the SQL Server Import and Export Wizard

Community Additions

ADD
Show: