I know there are many pages giving the steps and i've tried everything. I've used following steps in the connection:
Microsoft.ACE.OLEDB.12.0 as Provider
.xlsx file with entire path to Data Source
"Excel 12.0 Xml;HDR=YES" as Extended Properties
The above has been setup as connection. Now when i try using this connection manager in OLEDB Source, for "Table or view" data access mode, it gives "Unspecified Error" and when i try using "SQL command" and give a query as "select * from [Sheet1$]", it still gives me error.