Excel to SQL Server using SSIS

Sometimes, we need to need to fetch the data from excel spread sheet to sql server database. It can be easily achieved using import/export wizard or we can achieve by using SSIS tasks.

Let see how to utilise both options to achieve this great task in a simple manner. Open a new SSIS project in Business Intelligence studio.

Goto Project ->SSIS Import and Export Wizard

Clicking the option, we will get the Import/Export wizard. Browse your excel file and select your file.

Now, clicking next you will get the destination. you can specify your server and database name.

Clicking next, you will get the next screen.

You will be given two options,

either you can copy the entire table or you can write your customized queries to fetch the data from excel sheet. On clicking next you can view the spread sheets available. Here you need to select the spread sheet, from which you need to fetch the data.

On clicking "Preview" button you can view the spread sheet data. Click next button. You will get a successful message indicating your package is created. Now you need to execute the package.

You can view the above tasks in your monitor. Preparation SQL task will create a table and Data flow task is used to transfer the data from Excel source to SQL Server Destination.

Right click the package and execute the tasks.

Happy Learning!!!

Regards,

Venkatesan Prabu .J