executing DTS packages

  • Thread starter Thread starter dave
  • Start date Start date
D

dave

I would like to know if it is possible to execute a DTS
package from within a stored procedure. If so, could
someone steer me in to right direction as to what the
syntax would be for that?

Thanks, Dave
 
Here is code I use to execute a DTS package called "Update Web Date"

EXEC master..xp_cmdshell 'DTSRun /S SQLServer /N "Update Web Data" /E',
NO_OUTPUT

HTH,

J. Clay
 
Additional info:

I either found a reference to this in the BOL or on the MSDN web site.
(Been a while) Either way, where you find it will give you more references
to the switches you can use.

J. Clay
 
j.

thank you for the code sample. however, i am having
problems getting it work. i have the following code
sample in query analyzer and here is the error i am
getting...

declare @Table nvarchar(50)
declare @strDTS nvarchar(50)

set @Table = 'BizTypes'
set @strDTS = 'DTSRun /S SQLServer /N "' + @Table + '" /E'
print @strDTS

exec master..xp_cmdshell @strDTS

print 'procedure complete'

DTSRun: Loading...

Error: -2147467259 (80004005); Provider Error: 17 (11)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]
SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL
Server
Help file:
Help context: 0

NULL

do i have to replace the "SQLServer" with something else?

thanks, dave
 
hello again j.,

please ignore my first reply to you post. i am able to
get the dtsrun to work fine when i manually type dtsrun /s
(local) /n"biztypes" /e in the command prompt window.
but when i try to automate it using the following code...

declare @Table nvarchar(50)
declare @strDTS nvarchar(50)

set @Table = 'BizTypes'
set @strDTS = 'DTSRun /S(local) /N"' + @Table + '" /E'
print @strDTS

exec master..xp_cmdshell @strDTS

print 'procedure complete'

i get the following error...

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -
2147467259 (80004005)
Error string: Error opening datafile: Access is
denied.

Error source: Microsoft Data Transformation Services
Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0

Error Detail Records:

Error: 5 (5); Provider Error: 5 (5)
Error string: Error opening datafile: Access is
denied.

Error source: Microsoft Data Transformation Services
Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0

DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
NULL

if you have any suggestions as to what i am doing wrong,
i would greatly appreciate it.

thanks, dave
 
Dave, unfortunately, I don't have any suggestions. I had found the
reference in a search and was able to convert it to the one issue I needed.
Look in the SQL Books on Line for executing DTS, or on MSDN. It has been so
long since I did this that I cannot recall where I actually got the sample
that I used to create my procedure.

Quick question though...You are using the variable "@Table". Is this
holding the name of the DTS Package? Also, in looking closer, you may want
to put a SPACE after the /S and /N. The /S is for the server name and the
/N is for the Name of the DTS Package.

HTH,
Jim
 
Quickly, I did a search for DTSRun in BOL. Found a reference to dtsrun
Utility in the Command Prompt Utilities. This shows all of the available
switches and options for the DTSRun command. It appears that I was wrong in
the space issue. Also, in my example, the only reason I had quotes around
the package name is that when I created it I named it with spaces, so if you
package name doesn't have spaces, it shouldn't require quotes.

The /E switch specifies a trusted connection. So if you are not using
trusted connections, you will need to specify usarname and Password witht
the /U and /P switches.

For more info, the info is in BOL.

Jim
 
Back
Top