Anyways after completing package, I created a procedure to call package using DTExec as explained in point 5 of below link
http://visakhm.blogspot.com/2011/10/different-ways-to-execute-ssis-package.html
I added the script to call DTExec using xp_cmdshell and on testing i got below error message
SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
The reason was straightforward, the excel connection manager used inside package was not supported in 64 bit.
I was a bit surprised because I was able to run this package locally without any error.After doing some analysis, I found out the reason. In SSIS under project -> properties inside Debugging tab there’s a property called Run64bitRuntime and it was set to false. That’s why package was running fine locally.
My next attempt was to see how to enable package running in 32 bit mode while using DTExec.
I found out that in 64 bit machine there are two versions of DTExec existing one for 32 bit and other for 64 bit. By default, when I’m calling the DTExec the 64 bit version was getting called. So solution was to call 32 bit version explicitly. For this purpose, I modified the script as follows
ie from
EXEC xp_cmdshell ‘DTExec /f….’
to
EXEC xp_cmdshell ‘”C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe” /f….’
and this made ure 32 bit version of DTExec was called and solved the issue
Thought of posting this as somebody facing the same problem will get benefitted…
