Delen via

  • Artikel

Using the SQL Server Import and Export Wizard to Move Data

The SQL Server Import and Export Wizard offers the simplest method to create a Integration Services package that copies data from a source to a destination.

Note

On a 64-bit computer, Integration Services installs the 64-bit version of the SQL Server Import and Export Wizard (DTSWizard.exe). However, some data sources, such as Access or Excel, only have a 32-bit provider available. To work with these data sources, you might have to install and run the 32-bit version of the wizard. To install the 32-bit version of the wizard, select either Client Tools or Business Intelligence Development Studio during setup.

You can start the SQL Server Import and Export Wizard from the Start menu, from SQL Server Management Studio, from Business Intelligence Development Studio, or at the command prompt. For more information, see How to: Run the SQL Server Import and Export Wizard.

The SQL Server Import and Export Wizard can copy data to and from any data source for which a managed .NET Framework data provider or a native OLE DB provider is available. The list of available providers includes the following data sources:

  • SQL Server 

  • Flat files

  • Microsoft Office Access

  • Microsoft Office Excel

Some wizard features work differently, depending on the environment in which you start the wizard:

  • If you start the SQL Server Import and Export Wizard in SQL Server Management Studio, you run the package immediately by selecting the Execute immediately check box. By default, this check box is selected and the package runs immediately.

    You can also decide whether to save the package to SQL Server or to the file system. If you select to save the package, you must also specify a package protection level. For more information about package protection levels, see Setting the Protection Level of Packages.

    After the SQL Server Import and Export Wizard has created the package and copied the data, you can use the SSIS Designer to open and change the saved package.

    Note

    In SQL Server Express, the option to save the package created by the wizard is not available.

  • If you start the SQL Server Import and Export Wizard from an Integration Services project in Business Intelligence Development Studio, the package cannot be run as a step in completing the wizard. Instead, the package is added to the Integration Services project from which you started the wizard. You can then run the package or extend it by adding tasks, transformations, and event-driven logic by using SSIS Designer.

For more information about how to enhance a package that you have saved, see Designing Packages in Business Intelligence Development Studio.

SQL Server Import and Export Wizard: Permissions Required

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

  • Permissions to connect to the source and destination databases or file shares. In Integration Services, this requires server and database login rights.

  • Permission to read data from the source database or file. In SQL Server, this requires SELECT permissions on the source tables and views.

  • Permissions to write data to the destination database or file. In SQL Server, this requires INSERT permissions on the destination tables.

  • If you want to create a new destination database or table or file, permissions sufficient to create the new database or table or file. In SQL Server, this requires CREATE DATABASE or CREATE TABLE permissions.

  • If you want to save the package created by the wizard, permissions sufficient to write to the msdb database or to the file system. In Integration Services, this requires INSERT permissions on the msdb database.

For more information about how to use SQL Server Management Studio to manage permissions for Integration Services packages, see Using Integration Services Roles.

SQL Server Import and Export Wizard: Data Type Mapping

The SQL Server Import and Export Wizard provides minimal transformation capabilities. Except for setting the name, the data type, and the data type properties of columns in new destination tables and files, the SQL Server Import and Export Wizard supports no column-level transformations.

The SQL Server Import and Export Wizard uses the mapping files that Integration Services provides to map data types from one database version or system to another. For example, it can map from SQL Server to Oracle. By default, the mapping files in XML format are installed to C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles. If your business requires different mappings between data types, you can update the mappings to affect the mappings that the wizard performs. 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 transferring data from SQL Server to DB2, you change the nchar mapping in the SqlClientToIBMDB2.xml mapping file to use GRAPHIC instead of VARGRAPHIC.

Integration Services includes mappings between many commonly used source and destination combinations, and you can add new mapping files to the Mapping Files 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.

Note

If you edit an existing mapping file, or add a new mapping file to the folder, you must close and reopen the SQL Server Import and Export Wizard or Business Intelligence Development Studio for the new or changed files to be recognized.

External Resources

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN:


For automatic notification of these updates, subscribe to the RSS feeds available on the page.