executing dts from sproc

[dndaughtery]

How can I test the execution of a DTS package in a stored procedure? I'm using the following SQL cmd in the Sproc:



EXEC master..xp_cmdshell 'DTSRUN /N TestDTS /E /S Localhost'

 

[jbenson001]

How can you test it? Call the SP and check the output results of the DTS.

 

[dndaughtery]

I get the following error when I try to run the sproc in Query analyzer:

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: Create Table CertegyJob Step
DTSRun OnError: Create Table CertegyJob Step, Error = -2147467259 (80004005)
Error string: 'H:\DEVEL\CardsInc\Certegy Interface\test020906' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003044

Error Detail Records:

Error: -2147467259 (80004005); Provider Error: -534774783 (E01FFC01)
Error string: 'H:\DEVEL\CardsInc\Certegy Interface\test020906' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003044

DTSRun OnFinish: Create Table CertegyJob Step
DTSRun OnStart: Copy Data from CertegyJob to CertegyJob Step
DTSRun OnError: Copy Data from CertegyJob to CertegyJob Step, Error = -2147467259 (80004005)
Error string: 'H:\DEVEL\CardsInc\Certegy Interface\test020906' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003044

Error Detail Records:

Error: -2147467259 (80004005); Provider Error: -534774783 (E01FFC01)
Error string: 'H:\DEVEL\CardsInc\Certegy Interface\test020906' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003044

DTSRun OnFinish: Copy Data from CertegyJob to CertegyJob Step
DTSRun: Package execution complete.
NULL

 

[jbenson001]

Have you tested the DTS from the designer? Check and see if you get the same errors.

Jim

 

[SQLDenis]

your package can't see the H drive

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dndaughtery]

I looked into some other DTS packages that are run often and noticed the destination file address is on D://...
How can I find out which Directories SQL Server can see?

 

[jbenson001]

Go into Mycomputer of the server that is hosting SQL Server. You will what drives are available and mapped. If H: isn't there, you will need to map it.

Jim