This issue has actually been on my TODO list for quite some time. Now I had to go back to an old SSIS package and do some changes, I got reminded of how annoying this bug is.
The scenario is SQL Server Integration Services 2008 R2 combined with loading an Excel file. This should in anyones mind be the perfect match (if any such exists). I mean, both Microsoft products, one tailored to accommodate for the other – what could go wrong?
I turns out, that Business Intelligence Development Studio (BIDS) is unable to open the file, when you have configured a file in a remote localtion. Or so at least is my theory. Below is what fixes the flaw:
Opening the package fails:
View Code to fix error:
Remove the inner text of the <DTS:PropertyDTS:Name=“ConnectionString”></DTS:Property> in question:
This Works, until the next time you save the file. :/
Filed a Microsoft Connect Item: https://connect.microsoft.com/SQLServer/feedback/details/894728/visual-studio-bids-unable-to-load-file-dtsx