Currently, I am working on a project that requires data tranfer from excel files to sql server database. In this article, i will show you how to transfer data from one source to another. Follow the these steps.

1. Launch Business Intelligence Development Studio by selecting All Programs  Microsoft SQL Server 2005/2008 SQL Server Business Intelligence Development Studio from the Start menu. 
2. Select File  New  Project... from the main menu to open the New Project dialog.
3. Press the OK button to create the new project and dismiss the New Project dialog. A designer for the new package is created in Business Intelligence Development Studio
4. Select the Control Flow designer.
5. Open the Toolbox by selecting View  Toolbox from the main menu.
6. Drag a Data Flow Task from the Toolbox onto the Control Flow designer. The Data Flow Task moves data from source to destination providing facilities for transforming the data as it is moved.
7. Select the Data Flow designer tab
8. Drag a Excel Source from the Toolbox onto the Data Flow designer.
9. Configure the Excel Source by right-clicking it and selecting Edit... from the context menu
10. It will open the Excel Source Editor, then in connection manager, click on New to create a new connection.
11. For Excel connection string, select a desire file that you would like to import, then select your excel file version.
12. First row has a column name is already checked, make sure in your excel sheet First row has a column name, otherwise unselect the checkbox and click OK.
13. Now in Data Access Mode, You can either select Table or View or SQL Command. If your sheet is simple then you can go with Table or View.
14. Next select a name of the excel sheet e.g default name will be sheet1$ and you can see the preview to make sure your sheet is being read.
15. Click on a columns, and then select the desired columns and click OK.
16. Now drag a SQL Server destination from the Toolbox onto the Data Flow designer.
17. Select the Excel Source . The component output and output error flow paths (green and red arrows extending downward) appear. Drag the output path to the SQL Server Destination. A green arrow now connects the Excel Source to SQL Server Destination indicating the flow of data between the components.
18. Configure the SQL Server destination by right-clicking it and selecting Edit... from the context menu and repeat step 10 to 14 for connection string.
19. Click on a mapping, and then select the desired input columns and map them to destination columns and click OK.
20. Now You are ready to execute the package, which will read all of the data from the Excel File, and write it to a SQL server database.
21. Select the Data Flow designer. Select Debug  Start Debugging from the main menu to execute the package. Once package execution has completed.
22. Finally, Select Debug  Stop Debugging from the main menu.