Import and Export Data with the SQL Server Import and Export Wizard
Updated: November 30, 2016
This article lists the data sources that you can copy data to and from by using the SQL Server Import and Export Wizard, and any files that are required for a data source. The article also lists the required permissions to run the wizard and explains how the wizard maps data types.
SQL Server Import and Export Wizard is a simple way to copy data from a source to a destination. The wizard can also create the destination database or file and the destination tables for you.
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.
A common use of the wizard is to export data to Excel. Here is a four-minute video from YouTube that explains how to do that, with a query written in SQL Server 2012. Thank you to the author for her clear, simple instructions: Using the SQL Server Import and Export Wizard to Export to Excel.
If you have to copy more than one database, or database objects other than tables and views, use the Copy Database Wizard instead of the Import and Export Wizard. For more info, see Use the Copy Database Wizard.
The SQL Server Import and Export Wizard can copy data to and from the following data sources. To use some of these data sources, you may have to download and install additional files.
|Data source||Do I have to download additional files?|
SQL Server, Oracle, and others.
|SQL Server installs the files that you need to connect to SQL Server and Oracle, but doesn't install the files that you need to connect to other enterprise databases such as 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 and third-party products such as Npgsql - .NET Data Provider for PostgreSQL.
For SQLite, select from among several open source providers and drivers available online.
|Text files (flat files).||No additional files required.|
|Microsoft Excel and Microsoft Access files||Microsoft 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.
|SQL Server doesn'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 available.||You 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 an OLE DB Provider is available.
Any source for which a .Net Framework Data Provider is available.
Other data sources for which third-party components provide source and destination capabilities. Typically these third-party products are marketed as add-on products for SQL Server Integration Services (SSIS).
If your data source requires a connection string, you can find examples on this third-party site - The Connection Strings Reference.
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 have the permissions that you need.
|You need permissions to do these things||If you're using SQL Server, you need these 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.
In the SQL Server Import and Export Wizard, you can set the name, the data type, and the data type properties of columns in new destination tables and files, but you can't transform column values in other ways. As a result, the built-in mapping of data types from source to destination is important.
The wizard uses mapping files that are installed by SQL Server Integration Services to map data types from one database system or version to another. For example, it can map from SQL Server data types to Oracle data types. By default, the mapping files in XML format are installed in the following folders.
- C:\Program Files\Microsoft SQL Server\130\DTS\MappingFiles (for 64-bit)
- C:\Program Files (x86)\Microsoft SQL Server\130\DTS\MappingFiles (for 32-bit).
If you edit an existing mapping file, or add a new mapping file to the folder, you have to close and reopen the SQL Server Import and Export Wizard or SQL Server Data Tools (SSDT) to load the new or changed mapping file.
If your business requires different mappings between data types, you can update the mapping files to change the mappings used by the wizard. For example, if you want the SQL Server nchar data type to map to the DB2 GRAPHIC data type instead of the DB2 VARGRAPHIC data type when you transfer data from SQL Server to DB2, you can change the nchar mapping in the SqlClientToIBMDB2.xml mapping file to use GRAPHIC instead of VARGRAPHIC.
Integration Services installs mappings between many commonly used combinations of source and destination. You can also add new mapping files to the MappingFiles directory to support additional sources and destinations. The new mapping files must conform to the published XSD schema and must map between a unique combination of source and destination. The schema for mapping files, DataTypeMapping.xsd, is published here.
Here's a portion of the XML mapping file that maps from SQL Server data types (or, more specifically, from the data types used by the .Net Framework Data Provider for SQL Server) to Oracle data types. As one example, you can see that a SQL Server int data type maps to an Oracle INTEGER data type.
<dtm:DataTypeMappings xmlns:dtm="http://www.microsoft.com/SqlServer/Dts/DataTypeMapping.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" SourceType="System.Data.SqlClient.SqlConnection" MinSourceVersion="*" MaxSourceVersion="*" DestinationType="MSDAORA;OraOLEDB.Oracle;System.Data.OracleClient.OracleConnection" MinDestinationVersion="08.*" MaxDestinationVersion="*"> <!-- smallint --> <dtm:DataTypeMapping > <dtm:SourceDataType> <dtm:DataTypeName>smallint</dtm:DataTypeName> </dtm:SourceDataType> <dtm:DestinationDataType> <dtm:SimpleType> <dtm:DataTypeName>INTEGER</dtm:DataTypeName> </dtm:SimpleType> </dtm:DestinationDataType> </dtm:DataTypeMapping> <!-- int --> <dtm:DataTypeMapping > <dtm:SourceDataType> <dtm:DataTypeName>int</dtm:DataTypeName> </dtm:SourceDataType> <dtm:DestinationDataType> <dtm:SimpleType> <dtm:DataTypeName>INTEGER</dtm:DataTypeName> </dtm:SimpleType> </dtm:DestinationDataType> </dtm:DataTypeMapping> ... </dtm:DataTypeMappings>
This topic provides only an overview of 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.
- If you're looking for info about one or more of the steps in the wizard - for example, about choosing a data source - select the corresponding page in the navigation menu. There's a separate page of documentation for each page of the wizard.
If 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).
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.