November 28, 2013 at 4:09 am
Hello Friends,
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
EXEC master..xp_cmdshell '@"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /SQL "SSISPackage1" /SERVER "Server1"
Please help me I am struggling to fix this issue.
November 28, 2013 at 4:55 am
Hi
Please create C:\Windows\SysWOW64\config\systemprofile\Desktop and
try axecute execute again.
Br.
Mike
November 28, 2013 at 5:38 am
Hi Mike,
This solution worked for me.
I can't believe this, I have tried lot of solution but did not work and by creating this path works for me.
I don't understand how this solution fixed my issue.
You are genius, Thanks a lot.
November 28, 2013 at 5:54 am
I'm afraid I'm not genius, I simply meet that problem in the past and I lost some
time to resolve it. Problem is related with Runtime.InteropServices.
Br.
Mike
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply