So far it seems like its length of characters in sheet name. Even though sheet name is 31 chars long (excluding trailing $ sign), SSIS doesn't pick it up every time.
Assuming that inclusion of '$' in sheet name makes it 32 chars long, may be failing because of that.
Anyone else have such problem or have found out a solution to use such excel files with 31 char sheet name, please advise.
Thanks,
Harpreet