Import data from excel sheet to MSSQL

Show configuation settings.

sp_configure

If you don't see 'Ad Hoc Distributed Queries' option in table.

Allow show advance configuation settings.

sp_configure 'show advanced options', 1
reconfigure 

Allow OpenRowset/OpenDatasource queries.

sp_configure 'Ad Hoc Distributed Queries', 1 
reconfigure 

Select data from excel file.

SELECT * FROM EXCELLINK...[Sheet$] -- EXCELLINK have to be linked server
 
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=c:\Excel.xls;Extended Properties=Excel 8.0')...[Sheet$]
 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\Excel.xls', [Sheet$])

If 'Ad Hoc Distributed Queries' is set to 0 you get

"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online."

in this case use code at page top.