I tried importing it as Excel 97/2003 .xls format (instead of 2007) and it is failing.

Also I tried to follow as per the below link for SQL 2008 and found the below error after pressing the "Test Connection" button

"Test Connection failed because of an error in initializing provider. Unrecognized database format 'c:\XXX\test.xlsx'

Below problem what I am facing with my normal strategy

1. In the first wizard ("Choose a Data Source") selected data source as "Microsoft Excel"

2. Selected the .xlsx path which has many sheets that needs to be imported into respective tables

3. Excel version selected as 2007

4. In the second wizard ("Choose a Destination") selected destination as "Microsoft OLE DB Provider for SQL Server"

5. Server name selected

6. Authentication - Selected SQL server authentication

7. Selected the database where i want to import data

8. In the third wizard ("Specify table copy or query), I have selected Copy data.....

9. In the fourth wizard ("Select source tables and views") I have selected list of all the tables and mapped them to the corresponding tables

10. In the fifth wizard ("Review Data Type Mapping") I am finding below error message

"Found X unknown column type conversion(s). The package will not not be run.