executing DTS Package from stored procedure

There is a DTS package that work when when it is executed directly
from Enterprise Manager or dtsrun utility.
But it does not work when it is executed from a stored procedure.
Do you have any idea?How are you executing the DTS package from the proc? What does "does not
work" mean? Turn on logging (under Package Properties) to see any error
messages.
David Portas
SQL Server MVP
--|||Following is the code of proc that call DTS.
When I run DTS package directly from Enterprise Manager, it works as I want
(185 rows added).
But when I use following procedure, there is no row added.
CREATE PROC [dbo].[Usp_CFDBE13ExecuteDTSPkg]
@.PkgName varchar(255)
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int
--1. Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT
IF @.hr <> 0
BEGIN
--Create Package object failed
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG, @.PkgName
RETURN 1
END
--2. Load the Pkg
EXEC @.hr = sp_OAMethod @.oPKG,'LoadFromSqlServer', NULL,
@.ServerName='', @.PackageName=@.PkgName, @.Flags=256
IF @.hr <> 0
BEGIN
-- LoadFromSQLServer failed'
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--3.Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
--Execute failed'
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--4.Check Pkg Errors
--EXEC @.ret=spDisplayPkgErrors @.oPKG --Will do it later
--5.Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'
IF @.hr <> 0
BEGIN
--UnInitialize failed
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--6.Clean Up
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
RETURN 0
GO|||If you haven't already done so, turn on logging in the package so that
you can trap errors there and determine that the package does run. The
package execute method alone won't show up package errors.
Using a blank server name in the LoadFromSQLServer method defaults to
the default server instance _not_ to the instance in which your code is
executing. If you are targeting a non-default instance you must specify
the name. Better still, specify the name anyway. You can use
SERVERPROPERTY('SERVERNAME') to retrieve the name of the current
instance if you need to.
Be aware that your package will execute in the security context of the
local or domain account configured for the SQL Server service. That
account must therefore have access to any files or other resources
required by the DTS package.
Finally, make sure that your package uses fully qualified names for any
files that it accesses. The drive mappings available when you execute
locally in Enterprise Manager may not be available on the server.
David Portas
SQL Server MVP
--|||Thank you very much.
I got it.

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!
>
>