ssis excel source cannot acquire connection manager at DESIGN time

Question

I have an SSIS (super)package that consists of several (about) 40 packages, each of which has several data flow tasks, most of them loading some excel data to a sqlserver database. So I have several excel source connection managers, one per excel file. This has always worked fine until recently : I have to change an excel source in one of my packages (a column was added in the excel which has to be loaded in the destination sqlserver table) but I cannot edit the excel source in SSIS : when I click on "columns" I get an error box "DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER". So this is a design time issue, not a run time problem. I've seen a question like this one on stackoverflow but no answer. Anyone has an idea ?

• I' ve already removed office 2007 and reinstalled office 2003 on my machine, but this didn't help
• it has nothing to do with 64bit mode, which I've read about here
• Is there some limit on the number of connection managers in ssis ?
• Most of my connection managers were created with copy/paste and hence they have the same Id, although they point to different excel files. Could this be a problem ?

I should also mention that the package runs without any problems, so it's only "editing/changing" the package that doesn't work. For the moment I circumvent this problem by disabling the data flow that should change and by replacing it with a sql statement that hardcoded inserts the data from the excel into the destination table. Maybe another intersting thing to mention : I can create a new package and add and change excel sources without problems, but then again, when I try to copy this excel source in my package, I cannot edit it.

Answer 1

Did you try to check the advanced options on the Source Connection? Right click on the component and select "Show Advanced Editor..." Select the column mappings tab and press the Refresh button.

This should point out that the XLS connection has some additional columns or that the file that you are pointing at, cannot be pre-validated at runtime.

M.

Answer 2

I'm not sure if this will work but try the Work Offline option in the SSIS menu in BIDS/VS. What this should do is not validate the connection before you edit the component. It might be able to get you into the component.