TechNet
Export (0) Print
Expand All

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.

  • Flat files.

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

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

System_CAPS_ICON_tip.jpg Tip


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

StepWizard pages
WelcomeWelcome to SQL Server Import and Export Wizard
Pick the source of the data.Choose a Data Source (SQL Server Import and Export Wizard)
Pick the destination for the data.Choose a Destination (SQL Server Import and Export Wizard)
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 (SQL Server Import and Export Wizard)

 Configure Flat File Destination (SQL Server Import and Export Wizard)
Specify what you want to copy.Specify Table Copy or Query (SQL Server Import and Export Wizard)

 Select Source Tables and Views (SQL Server Import and Export Wizard)

 Provide a Source Query (SQL Server Import and Export Wizard)
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 (SQL Server Import and Export Wizard)

 Convert Types without Conversion Checking (SQL Server Import and Export Wizard)

 Column Mappings (SQL Server Import and Export Wizard)

 Review Data Type Mapping (SQL Server Import and Export Wizard)

 Column Conversion Details Dialog Box (SQL Server Import and Export Wizard)

 Preview Data Dialog Box (SQL Server Import and Export Wizard)
Copy the data.

Optionally, save the SSIS package that the Wizard has created in memory.
Save and Run Package (SQL Server Import and Export Wizard)

 Save SSIS Package (SQL Server Import and Export Wizard)

 Complete the Wizard (SQL Server Import and Export Wizard)

 Performing Operation (SQL Server Import and Export Wizard)

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

System_CAPS_ICON_note.jpg Note


This section describes the option to save the SSIS package that the Wizard creates. You only have the option to save the package if you have SQL Server Standard Edition or higher installed.

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.

Community Additions

ADD
Show:
© 2016 Microsoft