Import Excel File in SQL Server Using SSIS Package

Background

I want to import one thousand records into a database. Much time can be wasted by entering the records one by one. Many applications exist to import and export the data into the database. So I used SQL Server 2008. In that I found a good application, the Import and Export SQL Server wizard. This application has many options, like Flat files, Excel files, Access files and so on. I have done a Flat file import into a database, you can get more details about how to Import a Flat CSV file's data here.

Excel File data

My Excel file has about 350 records. I think that manually entering each record into the database would be highly inefficient.



Fig: 1 Excel Data

So I decided to import the data using the SQL Import and Export application. The first step is open the SQL Server Import and Export Wizard.

Select Microsoft Excel in the Data Source option. When you select Excel the design is automatically changed as in the following picture.



Fig: 2 change the data source here

Select your Excel file path with the browse button and one more thing is changed, the Excel version. I am socked because in my system the Office 2003 version is there. I don't care about that this version. If you move down a bit you will see a check box for making the default column name in the table. As you wish you can check or uncheck it.



Fig: 3 Preview your Excel data

We have an option for previewing the Excel data. If everything is cool then proceed.



Fig: 4 Change the table name here

An Excel file has by default three sheets. All sheets are seen in the preceding image. In my Excel file only Sheet 1 has data so the Destination only shows the first file.

Note: You can change the table name as needrd.



Fig: 5 Column mapping

You can seen all the details about the Excel and table. The column name source, destination, type and nullable column that allow the column that will accept a null value and additional things change the size of the column.



Fig: 6 Create SQL Statement

You can create the table depending on your needs. This query is generated by default.



Fig: 7 Output

After completing that process check your database. I hope your data is successfurlly imported into the respective table.

Final words: I hope you enjoyed this small article.