error while executing SSIS package from xp_cmdshel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

error while executing SSIS package from xp_cmdshel

Author

Topic

hmraval
Starting Member

4 Posts

Posted - 2013-11-28 : 06:40:04

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

52326 Posts

Posted - 2013-11-28 : 07:03:11

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-28 : 07:04:22

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

hmraval
Starting Member

4 Posts

Posted - 2013-11-28 : 07:12:10

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 ?

hmraval
Starting Member

4 Posts

Posted - 2013-11-28 : 07:13:04

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-28 : 07:15:02

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-28 : 07:17:16

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

hmraval
Starting Member

4 Posts

Posted - 2013-11-28 : 07:56:52

Thanks for reply.

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

C:\Windows\SysWOW64\config\systemprofile\Desktop

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources