Code: 0x00000001 Exception has been thrown by the target of an invocation error was thrown when I was trying access the excel files from script task in SSIS. The SSIS package works fine when running from development tool. But throws an error while called from SQL Server Agent job. This can be fixed be following steps
1. Go to Run and Launch DcomCnfg.exe
2. In the component services window , Navigate to Component Services à Computers à My Computer à DCOM Config
3. Find “Microsoft Excel Application” on right pan. Right click and select properties.
4. In the properties go to Identity tab. Choose the option “This User” and provide the user and password with which the excel will be launched while called programmatically.