SQL Server Import and Export Wizard
TOC
Collapse the table of content
Expand the table of content

SQL Server Import and Export Wizard

 

Updated: July 28, 2016

Applies To: SQL Server 2016

The 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.

System_CAPS_ICON_tip.jpg Tip

If you have to copy a large number databases or tables, 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.

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).

If you're ready to run the wizard and want to know how to start it, 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. To use some of these data sources, you may have to download and install additional files.

  • 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 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 typically have to download additional files to connect to these data sources.

  • Text files (flat files).

  • 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.

  • 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.

  • Any other data source for which a connector is available. 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.

      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).

System_CAPS_ICON_tip.jpg Tip

If your data source requires a connection string, you can find examples on this third-party site - The Connection Strings Reference.

The following table lists the steps for importing and exporting data and the corresponding pages of the wizard. Depending on the options that you select in the wizard, you typically don't see all of these pages.

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.

StepWizard pages
Welcome
You don't have to take any action on this page.
Welcome to SQL Server Import and Export Wizard
Pick the source of the data.Choose a Data Source
Pick the destination for the data.Choose a Destination
Optional steps to configure the destination.

- Create a new destination database.
- If you're copying data to a text file, configure additional settings.
Create Database

Configure Flat File Destination
Specify what you want to copy.Specify Table Copy or Query

Select Source Tables and Views

Provide a Source Query
Optional steps to configure the copy operation.

- Create a new destination table.
- Decide what to do if the wizard doesn't have a set of data type mappings between the source and destination that you selected.
- Review column mappings between source and destination.
- Handle issues with converting data types between source and destination.
- Preview the data to be copied.
Create Table SQL Statement

Convert Types without Conversion Checking

Column Mappings

Review Data Type Mapping

Column Conversion Details Dialog Box

Preview Data Dialog Box
Copy the data.

Optionally, save the SSIS package that the Wizard has created in memory.
Save and Run Package

Save SSIS Package

Complete the Wizard

Performing Operation

To run the SQL Server Import and Export Wizard successfully, you have to have at least the following permissions:

Permission requiredPermission required if you're using SQL Server
Permissions to connect to the source and destination databases or file shares.Server and database login rights.
Permissions 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.
Permissions to create the destination database or file, if applicable.CREATE DATABASE or CREATE TABLE permissions.
Permissions to 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 terminology 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 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.

You can change an existing 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.

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 must map between a unique combination of source and destination. The schema for mapping files, DataTypeMapping.xsd, is published here.

Sample mapping file

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>  
  

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

Community Additions

ADD
Show:
© 2016 Microsoft