error codes 0xC020801C and 0xC0202009 returned when trying to load XLSM file using SSIS package in SQL Server 2008 environment, loads fine in test environment

Hi All,
I use an SSIS package to dynamically  load data from specific tabs of  xlsm files into a database.
The files are all structured identically, occasionally I find inconsistencies in cell formats which may cause issues  when loading and usually this is resolved by modifying the cell format.
The SSIS package loads over 250 files successfully but I am now experiencing an issue with one file which will not load in my server environment, however it does load successfully in my test environment.
The thing that is confusing me is  that it loads fine in the test environment,  but will not load in the server environment . Other files are loading fine in the Server environment which suggests that the server environment is ok and the issue is with the file, however  the file loads fine in the test environment.

The error messages I get in the BIDs Development environment  on the server environment are
Error: There were errors during task validation.
[SSIS.Pipeline] Error: One or more component failed validation.
[SSIS.Pipeline] Error: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
[Excel Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Open in new window

The test environment is 64 bit Windows 7 Professional with SP1, SQL Server 2008 R2 RTM
The server  environment  is 64 bit Windows Server 2008 R2 Standard, SQL Server 2008 R2 SP2
Any enlightenment appreciated,

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:

I think I recall reading something about this recently.

Is the TransactionOption set to "Supported" or "Not Supported"?

If "Supported", try changing to the other option.


blossomparkAuthor Commented:
thanks for your reply,
the thing is, it is happening on this one file only, all other files are processed successfully. Also this file  is processed successfully in the test environment.
blossomparkAuthor Commented:
tried both other options in TransactionOption, "NotSupported" and "Required", same result
[ fanpages ]IT Services ConsultantCommented:
Yes, I read your question text & noted your concerns about just a single file being affected, & that the two environments were functioning differently.

The reason I mentioned the option setting I did is that the same symptoms also were exhibited in the same conditions in the original article I was reading, & when this setting was changed (in the environment where the single file was not being processed), the issue was resolved.

Sorry this did not happen for you on this occasion.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
blossomparkAuthor Commented:
it was worth a try, thanks for the input
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.