dts packages and xp_cmdshell

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

dave

i have a DTS package that imports data from a *.csv file
to a table in my sql server database. the *.csv file is
located on a different server than that which the
database resides. the package executes fine when running
it from the enterprise manager. next i created a stored
procedure using the xp_cmshell to run the dtsrun utility
to execute the mentioned DTS package. when i call the
stored procedure i get an access error to the *.csv file.
however, when i move the *.csv file to the same server
that the sql server database resides and update my DTS
package to reflect the new location of the *.csv file,
then it works fine. now for the question. is it safe to
assume that when using the xp_cmdshell to run the dtsrun
utility, that all files involved in the DTS package MUST
be on the same server as the sql database?

thanks, dave
 
Sounds more like a permissions issue than location. If you run it manually,
you ar using the security context that you connected to EM with. If you use
a different login to execute the sp, that login may be the problem.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
 
Back
Top