executing package DTExec

I created a package and stored in SQL server.

I am running it as follows

master.dbo.xp_cmdshell dtexec /sq "ABC DEF GHI" /ser "Prod"

ABC DEF GHI --> is my package name.

When i run this from a job, it runs without error

but when i run the above sql i am getting error as

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near '/'.

Am i missing some thing, please advice. Is that the problem between spaces in the package name?

I missed the single quotes

master.dbo.xp_cmdshell 'dtexec /sq "ABC DEF GHI" /ser "Prod"'

Executing an SSIS package from TSQL without using xp_cmdshell?

How can I execute an SSIS package from TSQL without using xp_cmdshell?

I have a web-app which calls some SQL which executes my SSIS package (a DTSX file, but stored in the server). But the security policy for my application won't permit me use to xp_cmdshell.

I want to do this:-
DECLARE @.returncode int
EXEC @.returncode = xp_cmdshell 'dtexec /sq pkgOne"'

Is there another way for executing a Package without going to the command line (e.g. is there some other system stored proc)?

Thanksandyabel,

Well...you could create a SQL job that has the command to execute xp_cmdshell in it and then in your web app run the following in TSQL

EXEC MSDB..SP_Start_Job @.Job_Name = 'Your Job Name Here'

The SQL job would not be scheduled to run and would only be run when you tell it to via your app. You would not have to worry about the security policy because the job is going to fire on the SQL server and that is where the xp_cmdshell is going to run from.

The bad is that the SSIS return code is going to be passed back to the SQL job and not your web app. But, if you post the return code to a table on your database and then have your app scan that table you can get the code that way...if you need it.

Execute xp_cmdshell from store procedure (called from aspx)

Hi, I have been searching for an answer to allow me to execute xp_cmdshell from withing store prcedure by calling the store procedure from an aspx via click on a button. This is what I found from my reserch but was not able to know where to set the rights and what each of them means:
"To run xp_cmdshell for a non-system administrator user, you must grant the following rights.
MSSQLServer and SQLServerAgent Services
Act as part of the Operating System.
Increase Quotas.
Replace a process level token.
Log on as a batch job."

The above quote was from the following link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;264155

The bottom line is to be able to call a store procedure from an aspx page to execute the code which contain xp_cmdshell command, an example of such command is like:
------------------
EXEC master..xp_cmdshell 'dir d:\BT_import\Data\sales_option_price_report.csv'

Thanks for your help
ehx5The "rights" refer to the user account that is running the extended stored proc, namely the user account for teh MSSQLServer service and the SQLServerAgent service. Once you know who they are you can go to the security policy editor and grant them the said rights. However, this is *VERY* dangerous and I really would recommend that you do NOT take this path. If someone gets onto to DB the xp_xmdShell 'Format c'. You get the idea. Much, much better to let some Business logic component sort this out, or even your own extended proc.

execute xp_cmdshell and other SA storedproc

Hi all,

I have to execute stored procedures containing
xp_cmdshell and certain system storedprocedures in msdb and master
with a user who is not SA.
(i.e iam able to execute stored procedures when i log as sa,
but any other user cannot run them)

Pls tell how to do this, it is quite urgent.Books online reviews:
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft Windows NT 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.

Follow the corresponding links on BOOKS ONLINE about this topic.

Execute SSIS Package from SP without xp_cmdshell?

A developer needs to execute an SSIS package from a stored procedure and I do not want to enable xp_cmdshell on SQL 2005.

One suggestion is to have the application invoke dtexec on the client PC, but this would cause the package to be executed on the client and also requires some SSIS components be installed on each client.

Another idea is to configure a Windows Service to execute dtexec, but I do not know the risks of this approach. Any thoughts?

Thanks, DaveI did something like this a long time ago in VB by creating an exe or dll in VB using the CmdExec. I placed the exeutable on the web server and my app call it.

Execute external command in SQL 2000

Is there a way to execute an external command in SQL 2000. For SQL 2005 I
found the xp_cmdshell procedure.
Thanks,
Tim Kelley wrote:
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>
xp_cmdshell exists in SQL 2000 as well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||The xp_cmdshell procedure is available in SQL Server 2000 as well.
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"Tim Kelley" <tkelley@.company.com> wrote in message
news:OtZrgipLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>
|||Hello,
Use XP_CMDSHELL. This extended procedure is available in Master database in
SQL 2000.
Master..xp_cmdshell 'dir c:\'
Ensure that you are not executing any looping commands or any commands with
high stress using XP_CMDSHELL.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:OtZrgipLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>

Execute external command in SQL 2000

Is there a way to execute an external command in SQL 2000. For SQL 2005 I
found the xp_cmdshell procedure.
Thanks,Tim Kelley wrote:
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>
xp_cmdshell exists in SQL 2000 as well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The xp_cmdshell procedure is available in SQL Server 2000 as well.
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"Tim Kelley" <tkelley@.company.com> wrote in message
news:OtZrgipLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>|||Hello,
Use XP_CMDSHELL. This extended procedure is available in Master database in
SQL 2000.
Master..xp_cmdshell 'dir c:\'
Ensure that you are not executing any looping commands or any commands with
high stress using XP_CMDSHELL.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:OtZrgipLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>

Execute external command in SQL 2000

Is there a way to execute an external command in SQL 2000. For SQL 2005 I
found the xp_cmdshell procedure.
Thanks,Tim Kelley wrote:
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>
xp_cmdshell exists in SQL 2000 as well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The xp_cmdshell procedure is available in SQL Server 2000 as well.
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"Tim Kelley" <tkelley@.company.com> wrote in message
news:OtZrgipLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>|||Hello,
Use XP_CMDSHELL. This extended procedure is available in Master database in
SQL 2000.
Master..xp_cmdshell 'dir c:'
Ensure that you are not executing any looping commands or any commands with
high stress using XP_CMDSHELL.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:OtZrgipLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Is there a way to execute an external command in SQL 2000. For SQL 2005 I
> found the xp_cmdshell procedure.
> Thanks,
>

execute dts package using xp_cmdshell

How to use xp_cmdshell to execute dts package?
I used the following command:
EXEC master..xp_cmdshell 'dtsrun /SServerName /Uuid /Ppassword /Npackagename
'
This returns an error and package did not executed. Any suggestions?
Thank you very much!What is the error message?
Hope this helps.
Dan Guzman
SQL Server MVP
"she" <she@.discussions.microsoft.com> wrote in message
news:773AA9DE-EFCD-43ED-AACF-B2C351110BEA@.microsoft.com...
> How to use xp_cmdshell to execute dts package?
> I used the following command:
> EXEC master..xp_cmdshell 'dtsrun /SServerName /Uuid /Ppassword
> /Npackagename'
> This returns an error and package did not executed. Any suggestions?
>
> Thank you very much!|||The error message are as follow:
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Error opening datafile: The device is not ready.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
The above datafile mentioned by this error message located in my computer
which is different from sql server. If I can execute the package manually
successfully, I do not see why it can not open the file through the stored
procedure call.
"Dan Guzman" wrote:

> What is the error message?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "she" <she@.discussions.microsoft.com> wrote in message
> news:773AA9DE-EFCD-43ED-AACF-B2C351110BEA@.microsoft.com...
>
>|||You have identified your own problem...
WHen you execute the package using DTSrun from YOUR command prompt it runs
on YOUR server, with your drive letters, etc. When it runs via xp_cmdshell,
or from scheduled SQLAgent tasks it runs on the SQL Server, with its drives,
and files...Either copy the directory/files to the sql server, or use a UNC
name to point back to a share on your PC..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"she" <she@.discussions.microsoft.com> wrote in message
news:773AA9DE-EFCD-43ED-AACF-B2C351110BEA@.microsoft.com...
> How to use xp_cmdshell to execute dts package?
> I used the following command:
> EXEC master..xp_cmdshell 'dtsrun /SServerName /Uuid /Ppassword
> /Npackagename'
> This returns an error and package did not executed. Any suggestions?
>
> Thank you very much!|||I am not familiar with UNC name you mention here. I looked up the help file
from SQL server but could not find the information. Can you specify how can
I "use a UNC name to point back to a share on my PC"? What is UNC name?
I did not executed the package from command prompt. I executed it from
enterprise manager without a problem. The enterprise manager runs the
package with the drive letter on file. When I save the package, the package
should contain all the drive letter info on my pc. My confusion is why
xp_cmdshell did not recognize the saved package which should contain all the
drive and file info and point it to the file on my computer.
I may sound silly, I just need someone to point this out or provide a link
to any some kind of tutorial on this subject.
Thank you very much!
"Wayne Snyder" wrote:

> You have identified your own problem...
> WHen you execute the package using DTSrun from YOUR command prompt it runs
> on YOUR server, with your drive letters, etc. When it runs via xp_cmdshell
,
> or from scheduled SQLAgent tasks it runs on the SQL Server, with its drive
s,
> and files...Either copy the directory/files to the sql server, or use a U
NC
> name to point back to a share on your PC..
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "she" <she@.discussions.microsoft.com> wrote in message
> news:773AA9DE-EFCD-43ED-AACF-B2C351110BEA@.microsoft.com...
>
>

execute dts package using xp_cmdshell

How to use xp_cmdshell to execute dts package?
I used the following command:
EXEC master..xp_cmdshell 'dtsrun /SServerName /Uuid /Ppassword /Npackagename'
This returns an error and package did not executed. Any suggestions?
Thank you very much!
What is the error message?
Hope this helps.
Dan Guzman
SQL Server MVP
"she" <she@.discussions.microsoft.com> wrote in message
news:773AA9DE-EFCD-43ED-AACF-B2C351110BEA@.microsoft.com...
> How to use xp_cmdshell to execute dts package?
> I used the following command:
> EXEC master..xp_cmdshell 'dtsrun /SServerName /Uuid /Ppassword
> /Npackagename'
> This returns an error and package did not executed. Any suggestions?
>
> Thank you very much!
|||The error message are as follow:
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Error opening datafile: The device is not ready.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
The above datafile mentioned by this error message located in my computer
which is different from sql server. If I can execute the package manually
successfully, I do not see why it can not open the file through the stored
procedure call.
"Dan Guzman" wrote:

> What is the error message?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "she" <she@.discussions.microsoft.com> wrote in message
> news:773AA9DE-EFCD-43ED-AACF-B2C351110BEA@.microsoft.com...
>
>
|||You have identified your own problem...
WHen you execute the package using DTSrun from YOUR command prompt it runs
on YOUR server, with your drive letters, etc. When it runs via xp_cmdshell,
or from scheduled SQLAgent tasks it runs on the SQL Server, with its drives,
and files...Either copy the directory/files to the sql server, or use a UNC
name to point back to a share on your PC..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"she" <she@.discussions.microsoft.com> wrote in message
news:773AA9DE-EFCD-43ED-AACF-B2C351110BEA@.microsoft.com...
> How to use xp_cmdshell to execute dts package?
> I used the following command:
> EXEC master..xp_cmdshell 'dtsrun /SServerName /Uuid /Ppassword
> /Npackagename'
> This returns an error and package did not executed. Any suggestions?
>
> Thank you very much!
|||I am not familiar with UNC name you mention here. I looked up the help file
from SQL server but could not find the information. Can you specify how can
I "use a UNC name to point back to a share on my PC"? What is UNC name?
I did not executed the package from command prompt. I executed it from
enterprise manager without a problem. The enterprise manager runs the
package with the drive letter on file. When I save the package, the package
should contain all the drive letter info on my pc. My confusion is why
xp_cmdshell did not recognize the saved package which should contain all the
drive and file info and point it to the file on my computer.
I may sound silly, I just need someone to point this out or provide a link
to any some kind of tutorial on this subject.
Thank you very much!
"Wayne Snyder" wrote:

> You have identified your own problem...
> WHen you execute the package using DTSrun from YOUR command prompt it runs
> on YOUR server, with your drive letters, etc. When it runs via xp_cmdshell,
> or from scheduled SQLAgent tasks it runs on the SQL Server, with its drives,
> and files...Either copy the directory/files to the sql server, or use a UNC
> name to point back to a share on your PC..
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "she" <she@.discussions.microsoft.com> wrote in message
> news:773AA9DE-EFCD-43ED-AACF-B2C351110BEA@.microsoft.com...
>
>

execute dts package using xp_cmdshell

How to use xp_cmdshell to execute dts package?
I used the following command:
EXEC master..xp_cmdshell 'dtsrun /SServerName /Uuid /Ppassword /Npackagename'
This returns an error and package did not executed. Any suggestions?
Thank you very much!What is the error message?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"she" <she@.discussions.microsoft.com> wrote in message
news:773AA9DE-EFCD-43ED-AACF-B2C351110BEA@.microsoft.com...
> How to use xp_cmdshell to execute dts package?
> I used the following command:
> EXEC master..xp_cmdshell 'dtsrun /SServerName /Uuid /Ppassword
> /Npackagename'
> This returns an error and package did not executed. Any suggestions?
>
> Thank you very much!|||The error message are as follow:
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Error opening datafile: The device is not ready.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
The above datafile mentioned by this error message located in my computer
which is different from sql server. If I can execute the package manually
successfully, I do not see why it can not open the file through the stored
procedure call.
"Dan Guzman" wrote:
> What is the error message?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "she" <she@.discussions.microsoft.com> wrote in message
> news:773AA9DE-EFCD-43ED-AACF-B2C351110BEA@.microsoft.com...
> > How to use xp_cmdshell to execute dts package?
> > I used the following command:
> > EXEC master..xp_cmdshell 'dtsrun /SServerName /Uuid /Ppassword
> > /Npackagename'
> >
> > This returns an error and package did not executed. Any suggestions?
> >
> >
> > Thank you very much!
>
>|||You have identified your own problem...
WHen you execute the package using DTSrun from YOUR command prompt it runs
on YOUR server, with your drive letters, etc. When it runs via xp_cmdshell,
or from scheduled SQLAgent tasks it runs on the SQL Server, with its drives,
and files...Either copy the directory/files to the sql server, or use a UNC
name to point back to a share on your PC..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"she" <she@.discussions.microsoft.com> wrote in message
news:773AA9DE-EFCD-43ED-AACF-B2C351110BEA@.microsoft.com...
> How to use xp_cmdshell to execute dts package?
> I used the following command:
> EXEC master..xp_cmdshell 'dtsrun /SServerName /Uuid /Ppassword
> /Npackagename'
> This returns an error and package did not executed. Any suggestions?
>
> Thank you very much!|||I am not familiar with UNC name you mention here. I looked up the help file
from SQL server but could not find the information. Can you specify how can
I "use a UNC name to point back to a share on my PC"? What is UNC name?
I did not executed the package from command prompt. I executed it from
enterprise manager without a problem. The enterprise manager runs the
package with the drive letter on file. When I save the package, the package
should contain all the drive letter info on my pc. My confusion is why
xp_cmdshell did not recognize the saved package which should contain all the
drive and file info and point it to the file on my computer.
I may sound silly, I just need someone to point this out or provide a link
to any some kind of tutorial on this subject.
Thank you very much!
"Wayne Snyder" wrote:
> You have identified your own problem...
> WHen you execute the package using DTSrun from YOUR command prompt it runs
> on YOUR server, with your drive letters, etc. When it runs via xp_cmdshell,
> or from scheduled SQLAgent tasks it runs on the SQL Server, with its drives,
> and files...Either copy the directory/files to the sql server, or use a UNC
> name to point back to a share on your PC..
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "she" <she@.discussions.microsoft.com> wrote in message
> news:773AA9DE-EFCD-43ED-AACF-B2C351110BEA@.microsoft.com...
> > How to use xp_cmdshell to execute dts package?
> > I used the following command:
> > EXEC master..xp_cmdshell 'dtsrun /SServerName /Uuid /Ppassword
> > /Npackagename'
> >
> > This returns an error and package did not executed. Any suggestions?
> >
> >
> > Thank you very much!
>
>