SQL Server Import and Export Wizard
Applies To: SQL Server 2016
The SQL Server Import and Export Wizard is the simplest way to copy data from a source to a destination.
The wizard can also create a destination database and destination tables for you. However, if you have to copy a large number databases or tables, or other kinds of database objects, use the Copy Database Wizard instead. For more info, see Use the Copy Database Wizard.
If you don't have MicrosoftSQL 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).
You may also have to install additional files to connect to certain data sources and destinations. For more info, see Data sources that you can use.
If you're ready to run the wizard, see Start the SQL Server Import and Export Wizard.
The SQL Server Import and Export Wizard can copy data to and from the following data sources.
Enterprise databases - SQL Server, Oracle, and others.
SQL Server doesn't install the files that you need to connect to enterprise databases other than SQL Server and Oracle - for example, to connect to IBM DB2 or Informix. If you already have the client software 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 may have to download additional files to connect to these data sources.
Microsoft Office files - Microsoft Excel and Microsoft Access.
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.
Cloud data sources - 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.
Many other data sources. The SQL Server Import and Export Wizard can also copy data to and from the following sources.
Any source for which an ODBC driver is available, by using the .Net Framework Data Provider for ODBC.
You can 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 an existing DSN (Data Source Name) or a connection string.
Any source for which a .Net Framework Data Provider is available.
Any source for which an OLE DB Provider is available.
Other data sources for which third-party components provide source and destination capabilities. Often these third-party products are described as add-on products for SQL Server Integration Services (SSIS).
The following table lists the steps for importing or exporting data and the corresponding pages of the Wizard. Depending on the options that you select in the Wizard, you don't typically see all of these pages.
To run the SQL Server Import and Export Wizard successfully, you have to have at least the following permissions:
|Permission required||Permission required if you're using SQL Server|
|Permissions to connect to the source and destination databases or file shares.||Server and database login rights.|
|Permission to read data from the source database or file.||SELECT permissions on the source tables and views.|
|Permissions to write data to the destination database or file.||INSERT permissions on the destination tables.|
|CREATE DATABASE or CREATE TABLE permissions.|
|If you want to save the SSIS package created by the wizard, permissions sufficient to save the package to the msdb database or to the file system.||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 an ETL tool for extracting, transforming, and loading data. The pages of the Wizard use some of the language of SSIS.
In SSIS, the basic unit is the package. The Wizard creates a package in memory as you move through the pages of the Wizard and specify options.
For more info about SSIS, see SQL Server Integration Services.
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. At the end of the Wizard, you can save the package. Later you can reuse the package and extend it by using SSIS Designer to add tasks, transformations, and event-driven logic.
The method that you use to run the Wizard determines whether you can save and run the package that the Wizard creates.
If you start the Wizard from the Start menu, from the command prompt, or from SQL Server Management Studio, you can run the package immediately at the end of the Wizard by selecting the Execute immediately check box. By default, this check box is selected and the package runs immediately. Optionally, you can also save the package.
If you start the Wizard from an Integration Services project in SQL Server Data Tools (SSDT), you can't run the package until after you exit the wizard. The Wizard saves the package in the Integration Services project from which you started the wizard. You can run the package in SQL Server Data Tools.
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 add other transformations for column values. As a result, the fixed mapping of data types from source to destination is important.
The Wizard uses the mapping files that are installed by SQL ServerIntegration Services to map data types from one database version or system 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 folder C:\Program Files\Microsoft SQL Server\130\DTS\MappingFiles.
You can change the existing mapping files. 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.
You can add a new mapping file. 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 map between a unique combination of source and destination.
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.
Sample mapping file
Here's a portion of the XML mapping file that maps from SQL Server to Oracle data types (or, more specifically, from the data types used by the .Net Framework Data Provider for SQL Server to the data types used by the .NET Framework Data Provider for Oracle).
<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>
Start the Wizard. For more info, see Start the SQL Server Import and Export Wizard.