I am having the opposite issue. I am trying to execute SSIS package using xp_cmdshell.
Can anyone please help me?
I have created SSIS package which exports data into excel file. After export excel file, 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.
EXEC master..xp_cmdshell '@"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /SQL "SSISPackage1" /SERVER "Server1"
Package throws error while executing script task.
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:
? 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.
Please help me I am struggling to fix this issue.