SSIS Import Export Wizard

In Visual Studio or SQL Server Data Tools (SSDT), an SSIS Import and Export Wizard is provided to give users a simple way to create a data flow task based package to transfer data from source to destination. This wizard is also called SQL Server Import and Export Wizard which can be launched by several ways as shown below.

SourceHow to Launch the Wizard
Visual Studio or SSDTRight-click the SSIS Packages folder in the Solution Explorer and choose "SSIS Import and Export Wizard..."
Visual Studio or SSDTClick PROGECT menu and choose "SSIS Import and Export Wizard..."
Start menuClick Start => All Programs => Microsoft SQL Server 2012 => Import and Export Data (32) or Import and Export Data (64)
SQL Server Management StudioRight-click a database, point to Task and then click "Import Data..." or "Export Data..."
Command prompt windowIn a command prompt window, change the current folder to C:\Program Files\Microsoft SQL Server\110\DTS\Binn and run DTSWizard.exe.

Note Note
When data sources only have 32-bit providers installed on the source machine, the 32-bit wizard should be launched instead of the default 64-bit version. The 32-bit version is located in the folder C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn for SQL Server 2012.

In the following example, I'm going to create a data flow task in a package by SSIS Import and Export Wizard to transfer data in an Excel file to the student table in the SQL Server database Test. The excel file Students.xlsx was created as below and saved in C:\SSIS folder.

[Students Excel Data Source File]

Pay attention, the first line of the data is the header.

Now it's time to create the package.

  1. Create an empty package ImportExportWizard.dtsx in the LearnSSIS1 project and open it if it is closed.

  2. Right-click the "SSIS Packages" folder in the Solution Explorer and choose "SSIS Import and Export Wizard...".
  3. [Launch Import and Export Wizard]

    A welcome window of the wizard will be popped up as follows.

    [Import Export Wizard Welcome Page]

  4. Click "Next >" button to go to "Choose a Data Source" step. We'll choose "Microsoft Excel" as data source and path of the file Students.xlsx. Make sure the Excel version is "Microsoft Excel 2007" and "First row has column names" checkbox checked.
  5. [Choose a Data Source]

  6. Click "Next >" button to go to "Choose a Destination" step. Input "localhost" as Server name and choose Test as Database.
  7. [Choose a Destination]

  8. Click "Next >" button to go to "Specify Table Copy or Query" step. Leave the default setting. If you choose "Write a query to specify the data to transfer", you will have to write SQL statement running on the data source in the next step.
  9. [Specify Table Copy or Query]

  10. Click "Next >" button to go to "Select Source Table and Views" step. Choose "Sheet1$" because the data is in this sheet of the excel file. Then select "[dbo].[student]" table as the destination table.
  11. [Select Source Table and Views]

  12. Click "Edit Mappings..." to pop up "Column Mappings" window.
  13. The columns with the same names in both source and destination table will be automatically matched. In this example, "Age" in the source is matched to "age" in the destination automatically. But the counterpart of "Student Name" cannot be found in the destination table. So we'll have to select "name" in the destination column manually.

    [Column Mappings]

    There are many option settings in this window. For example, if the destination table is not existed, you can choose "Create destination table" and "Edit SQL...". If you want to remove all the existing records before inserting new ones, you can choose "Delete rows in destination table". Also you can "Drop and re-create destination table" or "Enable identity insert". For details, you can check here in Microsoft MSDN.

    In this sample package, we just choose the default setting "Append rows to the destination table".

  14. Click "OK" to close the column mappings window and go back to the wizard. Then you can click "Preview..." to check the source data.
  15. [Preview Data]

  16. Click "OK" to close the preview data window. Then click "Next >" button to go to "Reiew Data Type Mapping" step.
  17. [Reiew Data Type Mapping]

    Please be noted, the source data type of Age is double and the destination type is int. We'll see if it works later.

  18. Click "Next >" button to go to the last step "Complete the Wizard".
  19. [Complete the Wizard]

  20. Click "Finish" button to finish the wizard and start to create a data flow task.
  21. [The Execution Was Successful]

    If success, you have the option to View Report, Save Report to a File, Copy Report to Clipboard or Send Report as Email.

  22. Click "Close" button.You will see a data flow task is created as below. Then click "Data Flow" to see the task in details.
  23. [Data Flow Task 1] [Data Flow Details]

  24. Run the package and check the student table in SQL Server and you will find 2 new records are added.
  25. [Result: 2 Records Added]

In this section, we used SSIS Import and Export Wizard to create a data flow task in the new package and it ran successfully. In the next section, we'll formally introduce the most important task in SSIS - Data Flow Task.