SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 error while executing SSIS package from xp_cmdshel
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hmraval
Starting Member

4 Posts

Posted - 11/28/2013 :  06:40:04  Show Profile  Reply with Quote
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.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/28/2013 :  07:03:11  Show Profile  Reply with Quote
I think its issue with the account that you use while executing from the proc using xp_cmdshell. You need to give it access to location where you place excel file.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/28/2013 :  07:04:22  Show Profile  Reply with Quote
use this to assign a proxy credential for xp_cmdshell which has access to file path

http://technet.microsoft.com/en-us/library/ms190359.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

hmraval
Starting Member

4 Posts

Posted - 11/28/2013 :  07:12:10  Show Profile  Reply with Quote
Thanks for reply,

I have created proxy account for xp_cmdshell but its not working..

how can I force xp_cmdshell to use proxy account to execute ?
Go to Top of Page

hmraval
Starting Member

4 Posts

Posted - 11/28/2013 :  07:13:04  Show Profile  Reply with Quote
Thanks for reply,

I have created proxy account for xp_cmdshell but its not working..

how can I force xp_cmdshell to use proxy account to execute ?


quote:
Originally posted by visakh16

use this to assign a proxy credential for xp_cmdshell which has access to file path

http://technet.microsoft.com/en-us/library/ms190359.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/28/2013 :  07:15:02  Show Profile  Reply with Quote
quote:
Originally posted by hmraval

Thanks for reply,

I have created proxy account for xp_cmdshell but its not working..

how can I force xp_cmdshell to use proxy account to execute ?


quote:
Originally posted by visakh16

use this to assign a proxy credential for xp_cmdshell which has access to file path

http://technet.microsoft.com/en-us/library/ms190359.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs





once you create it it will use the proxy account automatically unless you're a memeber of sysadmin in which case it will use service accunt instead.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/28/2013 :  07:17:16  Show Profile  Reply with Quote
for more details see

http://technet.microsoft.com/en-us/library/ms175046.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

hmraval
Starting Member

4 Posts

Posted - 11/28/2013 :  07:56:52  Show Profile  Reply with Quote
Thanks for reply.

I don't know how but creating following directory fixed my issue..

C:\Windows\SysWOW64\config\systemprofile\Desktop
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000