After posting this topic, I was able to access excel data using OPENROWSET function as mentioned in your article with some help from BOL.
here is the query to access Excel 2007:-
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=D:\DATA_SOURCE\Data2.xlsx', 'SELECT * FROM [Sheet1$D3:D3]');
But then this is using SQL.
If I dont want to use the SQL approach in SSIS , then is it possible to achieve the result using SSIS packages.