GloriousRuins (4/30/2015)Could anyone help me with issues I'm having with the reverse scenario
That's exactly how you should do it so, the issue that you're having may be related to something else. If all else fails, try the following so that it only evaluates the file name at run-time:
1) Copy your xlsb to an xlsx file. Keeping the path and name the same isn't necessary but, it just makes it easier - the goal is just to have something with the same exact contents.
2) Repoint your connection there and ensure that everything works.
3) Create a variable with the FilePath or ServerName (doesn't matter which one) of your Excel file connection (stick to the xlsx file for now to ensure this works correctly).
4) Use an expression within the Excel connection manager's FilePath or ServerName to point at your variable. Run and test to make sure this works. If not, turn off validation on the data flow task and anything else that you can think of.
5) Once you get 5 to work, Change your variable to point at the xlsb and run again.
Let me know if this works.
________________________________________Get some SQLey goodness at Dirt McStain's SQL Blog