Creating Packages Using the SQL Server Import and Export Wizard

  • Article

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

Note

On a 64-bit computer, Integration Services installs both a 32-bit and a 64-bit version of the SQL Server Import and Export Wizard (DTSWizard.exe).

The SQL Server Import and Export Wizard can access a variety of data sources. You can copy data to and from the following sources:

  • SQL Server
  • Flat files
  • Access
  • Excel
  • Other OLE DB providers

Additionally, you can use .NET providers and the Microsoft .NET Data Provider for mySAP Business Suite as sources.

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, see Creating Packages in SSIS Designer and Running Packages.

If the SQL Server Import and Export Wizard is started in SQL Server Management Studio, you can choose whether to run the package.

Mapping Data Types

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 2000 to SQL Server 2005, or from SQL Server 2005 to Oracle. The mapping files in XML format are installed by default to C:\Program Files\Microsoft SQL Server\90\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.

Running the SQL Server Import and Export Wizard

When you run the SQL Server Import and Export Wizard, you will perform the following tasks:

  1. Choose a data source and a destination.
    The available sources include OLE DB providers, SQL Native Client, ADO.NET, Excel, and the Flat File source. Depending on the source, you set options such as the authentication mode, server name, database name, and file format.

    Note

    The Microsoft OLE DB Provider for Oracle does not support the Oracle BLOB, CLOB, NCLOB, BFILE, and UROWID data types, and the OLE DB source cannot extract data from tables that contain columns with these data types.

    The available destinations include OLE DB providers, SQL Native Client, Excel, and the Flat File destination.

  2. Set the options for the type of destination that you selected.
    If the destination is a SQL Server database you can specify the following:

    • Indicate whether to create a new database and set the database properties. The following properties cannot be configured and the wizard uses the specified default values:

      Property Value

      Collation

      Latin1_General_CS_AS_KS_WS

      Recovery model

      Full

      Use full-text indexing

      True

    • Choose whether to copy data from tables or views or to copy query results.
      If you want to query the source data and copy the results, you can construct a Transact-SQL query. You can enter the Transact-SQL query manually or use a query saved to a file. The wizard includes a browse feature for locating the file, and the wizard automatically opens the file and pastes its content into the wizard page when you select the file.
      If the source is an ADO.NET provider you can also use the option to copy query results, providing the DBCommand string as the query.
      If the source data is a view, the SQL Server Import and Export Wizard automatically converts the view to a table in the destination.

    • Indicate whether the destination table is dropped and then recreated, and whether to enable identity inserts.

    • Indicate whether to delete rows or append rows in an existing destination table. If the table does not exist, the SQL Server Import and Export Wizard automatically creates it.

  3. If the destination is a Flat File destination you can specify the following:

    • Specify the row delimiter in the destination file.
    • Specify the column delimiter in the destination file.

  4. Optionally, select one table and change the mappings between source and destination columns, or change the metadata of destination columns:

    • Map source columns to different destination columns.
    • Change the data type in the destination column.
    • Set the length of columns with character data types.
    • Set the precision and scale of columns with numeric data types.
    • Specify whether the column may contain null values.

  5. Optionally, select multiple tables and update the metadata and options to apply to those tables:

    • Select an existing destination schema or provide a new schema to assign tables to.
    • Specify whether to enable identity inserts in destination tables.
    • Specify whether to drop and re-create destination tables.
    • Specify whether to truncate existing destination tables.

  6. Save and run a package.
    If the wizard is started from SQL Server Management Studio or the command prompt, the package can run immediately. You can save the package to the SQL Server msdb database or to the file system. When you save the package you can set the package protection level, and if the protection level uses a password, provide the password. For more information about the msdb database, see Managing Integration Services Packages.
    If the wizard is started from an Integration Services project in Business Intelligence Development Studio, you cannot run the package from the wizard. Instead, the package is added to the Integration Services project from which you started the wizard. You can then run the package in Business Intelligence Development Studio.

To start the SQL Server Import and Export Wizard

See Also

Concepts

Integration Services Considerations on 64-bit Computers

Other Resources

Designing and Creating Integration Services Packages

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
Changed content:
  • Added note about mapping files.

14 April 2006
Changed content:
  • Added information about setting package protection level.
  • Added information about bulk editing the metadata and options for tables.

5 December 2005
Changed content:
  • Provided information about adding and updating mapping files.
  • Added Microsoft .NET Data Provider for mySAP Business Suite as a source.