SSIS: How to call DTExec through xp_cmdshell in t-sql ?

Question

SSIS: How to call DTExec through xp_cmdshell in t-sql ?

Puja Shah

SSCommitted

Points: 1724
More actions
May 18, 2011 at 7:20 am

#251144

Hi,
I want to execute an SSIS Package stored in file system.
exec master.dbo.xp_cmdshell '""C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /F "C:\Users\pujashah\Documents\New Package.dtsx"'
I get following error:
"Microsoft (R) SQL Server Execute Package Utility
Version 10.0.1600.22 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 6:42:40 PM
Error: 2011-05-18 18:42:40.59
Code: 0xC0011007
Source: {0E519291-8D78-43A0-ADB0-5D8001AB8215}
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.
End Error
Error: 2011-05-18 18:42:40.59
Code: 0xC0011002
Source: {0E519291-8D78-43A0-ADB0-5D8001AB8215}
Description: Failed to open package file "C:\Users\pujashah\Documents\New Package.dtsx" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can
be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
End Error
Could not load package "C:\Users\pujashah\Documents\New Package.dtsx" because of error 0xC0011002.
Description: Failed to open package file "C:\Users\pujashah\Documents\New Package.dtsx" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be
the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
Source: {0E519291-8D78-43A0-ADB0-5D8001AB8215}
Started: 6:42:40 PM
Finished: 6:42:40 PM
Elapsed: 0.015 seconds"
But when I run this :
"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /F "C:\Users\pujashah\Documents\New Package.dtsx"'
in command prompt, the package executes without any error.
The process is to generate data for each Id, then to create an excel file having name of Id. This generated data will be exported to excel by calling save ssis package in file system.
Same process is repeated for all Ids.
Please tell me where is the issue.
Thanks in advance...
-Puja

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Puja Shah (5/18/2011)
Error: 2011-05-18 18:42:40.59
Code: 0xC0011002
Source: {0E519291-8D78-43A0-ADB0-5D8001AB8215}
Description: Failed to open package file "C:\Users\pujashah\Documents\New Package.dtsx" due to error 0x80070005 "Access is denied.".
Could not load package "C:\Users\pujashah\Documents\New Package.dtsx" because of error 0xC0011002.
Description: Failed to open package file "C:\Users\pujashah\Documents\New Package.dtsx" due to error 0x80070005 "Access is denied.".

Sounds like it could be a permission issue

Do you have a user in the master database which has execute permissions on xp_cmdshell

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

homebrew01

SSC Guru

Points: 55548

May 18, 2011 at 8:00 am

#1326468

Edited to remove wrong answer.

Puja Shah SSCommitted Points: 1724 More actions · Answer 3

Thanks Welsh for quick response...

I am executing the script from sa (SQL authentication).

Can you please tell me what permissions are required for an sa user to execute xp_cmdshell ?

One more thing, below query executes successfully:

set @sql = ' exec master.dbo.xp_cmdshell ''copy "\\pujashah\folder\Final\Test.xls" "\\pujashah\folder\'+ convert(varchar,@project_id) + '.xls"'''

-Puja

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Puja Shah (5/18/2011)
Can you please tell me what permissions are required for an sa user to execute xp_cmdshell ?
One more thing, below query executes successfully:
set @sql = ' exec master.dbo.xp_cmdshell ''copy "\\pujashah\folder\Final\Test.xls" "\\pujashah\folder\'+ convert(varchar,@project_id) + '.xls"'''
-Puja

You want to make sure that the account that you are executing this as has permissions to all of the resources that are being referenced.

If you execute it as a Job then the SQL Server Service Agent Account needs the permissions.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Puja Shah SSCommitted Points: 1724 More actions · Answer 5

Thanks Welsh....

You were right, it was permission issue. Package was being executed from "C:\Users\pujashah\Documents" location, that have limited permissions.

Thank you again for Help....

-Puja