I am trying to execute SSIS package from xp_cmdshell.
I have created SSIS package which exports data into excel file. After export, I have written some code in script task to modify those file to add new line on the top and write module name on the first row.
This package runs fine when executed from integration services and from BIDS.
Now my client want to execute this package through stored procedure only without creating SQL agent job. I know its odd requirement but I have to do it. I am not able to execute it package through xp_cmdshell. it throws below error.
Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file 'E:\SSIS\EXPORT_DATA\CSV_VENDOR.xls'. There are several possible reasons: NULL ? The file name or path does not exist. ? The file is being used by another program. ? The workbook you are trying to save has the same name as a currently open workbook.
I am using below code to execute ssis package from xp_cmdshell