How To Use Import/Export Wizard In SQL Server - SQL Server / TSQL Tutorial Part 104

We have received SourceFile.xlsx file and we have to load that to SQL server Table. We can either create SSIS Package in BIDS or we can use Import/Export Wizard to load this file in SQL Server Table. In this post, we will use Import/Export Wizard.

Fig 1: Excel Source File

Step 1:
Right Click on Database in which your table exists or you want to create it and load Excel data as shown below

Fig 2: Import Data by using Import/Export Wizard in SQL Server Table.

Choose the Data Source:

Choose the data source which you want to use as source, As we are loading data from Excel, Choose Excel file as shown below

Fig 3: Choose Excel Data Source in Import Export Wizard

Choose a Destination:

Choose the destination where you want to load the data from source. In our case we are loading our data to SQL Server Table. Configure as shown below

Fig 4: Choose SQL Server as Destination

Specify a Table Copy or Query:

You can directly choose the table from where do you want to load the data or you can write query if you are using Database as your source. As we are using Excel as source, we will choose Table(Sheet).

Fig 5: Choose Copy data from one or more tables or views 

Select Source Tables and Views:

In this part of Wizard, we have to select the Tables or Views we want to use from source and load data to destination. As we are loading data from Excel, the Excel Tabs are shown. Choose the Sheet (Tab) which do you want to load. Under Destination, it will show you same name like Source. I have changed that to CustomerData. You can choose any name of your Table you want. You can choose multiple sheets or Tables from Source.

Fig 6: Select Source Tables/Views in Import Export Wizard

Column Mappings:

Click on Edit Mappings and then you can map the source columns to destination columns , Also if you need to choose correct Data type, you can change here.

Fig 7: Column Mapping Import Export Wizard

Save and Run Package:

By Default, Run immediately is checked. I have changed the option to Save SSIS Package and provided the location where I want to save the SSIS Package. Also there is no sensitive information that I want to save in Package such as Password so I have selected Do not save sensitive data.

Fig 8: Save SSIS Package to File System

Save SSIS Package:

Provide the name of SSIS Package and File Location as shown below

Fig 10: Provide Name for SSIS Package

Complete the Wizard: 

Summary of all the steps will be shown to you in this step. You can see the source and destination etc.

Fig 11: Summary of Steps

Once you hit Finish button, The Wizard will execute all below steps and finally save the SSIS Package.

Fig 12: Save the SSIS Package to given location

The Package is created on desktop as per my given path.

Fig 13: SSIS Package created by Import/Export Wizard

To execute this package, double click on it and below window will open. If you need to change the name of File or SQL Server, you can go to Connection Managers and change it. In my case, I do not want to make any changes. Press Execute Button

Fig: 14 Execute Package Utility

Once you hit Execute, Package Execute Progress window will appear and you will be able to see the progress of execution of your SSIS Package.

Fig 15: Package Execution Progress.

Import/Export Wizard is a way to quickly load data between different sources and destinations. You can create your SSIS Package quickly by using Import/Export Wizard and then add to SSIS Project and make changes if required.

If we need to export data from SQL Server then we need to Right Click on Database-->Tasks-->Export Data and Import/Export Wizard will start.

Video Demo : How to use Import Export Wizard to load Excel file data to SQL Server Table