SQL Server Import and Export

The SQL Server Import and export wizard provides a graphical user interface onto a SQL Server Integration Services (SSIS) package. Once created the package can be automated, to run on a schedule. It can be further configured and modified by using SQL Server Data Tools (SSDT)

To begin, open the Import and export wizard, right-click a database and select the Tasks sub-menu -> Export data command:

SQL Server Import and Export
  1. Connect to a source database via the Choose a data source step.Permissions: You will need the following permissions to for the source data source/instance
    1. read data from the database or file
    2. Insert permission on the msdb database to save the SSIS package
  2. Connect to a destination SQL Server database in the Choose a destination step.Permissions: The following permissions are required for the destination instance:
    1. write data to the database or file
    2. permissions to create a database
    3. if necessary, permission to create table
  3. Choose the Copy data from one or more tables or views option, In the Specify table copy or query step:https://isqlplus.com/sql-server/
  4. In the Select source tables and views step, choose the table(s) for which you want to export data from:
  5. In the Save and run package step, choose the Run immediately option:
  6. in the Complete the wizard step, Click Finish
  7. Once completed, the following dialog will be displayed:

Troubleshooting: The SQL Server import and export wizard will not distinguish identity columns from any other column type. This will lead to errors when inserting data into such columns

SQL Server Import and Export

Troubleshooting: The SQL Server import and export wizard also doesn’t process tables based on dependency order. An example might be loading a table, with a foreign key (the child), before the referencing table the parent, causing a foreign key constraint failure

Also See:

Import and Export Wizard