Error on first attempt at running package on SSIS server

[Kevin Talley]

Hello,

I have been testing CozyRoc locally (Visual Studio) for a couple of weeks now, and am very impressed with the components/tasks that I have used.  Today, I wanted to start the 30-day server trial license, so that I could make sure that everything would work as expected in our typical production environment (our Data team, at least for now, will simply run our "data imports" directly from SQL Agent in SSMS.  However, for some reason, my Excel loader step is failing with an "object not found" error when trying to load the specified sheet:

"Import Data Sheet" is the correct sheet name, and this works fine if I run the package locally using Visual Studio.  In our existing import packages, I use the "stock" Excel source, and have never run into this (or similar) issue.  Is there something about the CozyRoc "Excel Source Plus" where I need to do something differently?

Thanks in advance!

Kevin Talley

[Kevin Talley]

FWIW, I can reproduce this exact error message in Design mode.  If I use local copy of the Excel file (with sheet name "Test Sheet", for example) during the setup (where you select Sheet from dropdown), and then change the file path to the same Excel file (but sheet name on this one is "Test Sheet X") on the file share server (which is where SSIS server needs to grab it), it gives me that same exact error message ("Test Sheet" object not found).  THIS one makes sense to me, because yes -- even though data is the same, the sheet name IS different, and would not be found.  But if I change the file share version to have "Test Sheet" as the sheet name, and try to run the package from SSIS (vs. local Visual Studio), it always fails with this error.