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 ] 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.
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,