I apologize for the dual posting in microsoft.public.sqlserver, now...

[ To continue ]
I have a DTS package that imports a text file from a UNC path.

Right clincking and executing under Enterprise Manager produces successul
imports of data. However running the DTS package by invoking it from within
a stored procedure causes the package to fail with the following results.

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217887 (80040E21)
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.

Can someone please explain to me what the problem is here? I am invoking
the DTS package in the following manner within the storded procedure.

exec master..xp_cmdshell 'dtsrun /Sdsisql /NPryor_Import_Seminar_Data /E'
; --, NO_OUTPUT;

Thanks,
Andrew

Have a read of the xp_cmdshell topic under Books Online, in particular the
section that relates to the security context. This is normally the SQL
Server service's service account. What is your service account and does it
have permissions to access the UNC share and file?


--
Darren Green
http://www.sqldts.com


"Andrew" <afriedl (AT) data-source (DOT) com> wrote

This is definately a permissions problem. Any idea about how to go about
solving it?

"Andrew" <afriedl (AT) data-source (DOT) com> wrote

In article <udX2#UDXDHA.384 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, Andrew
<afriedl (AT) data-source (DOT) com> writes
Find out the SQL Server service's service account. Make sure it is a
domain account (assuming both machine are running in a domain). You will
then need to log onto he source machine and change the permissions on
the share and add a minimum of read for the service account.

If you are in a workgroup create a mirrored account, same user name and
password on both machines. Add permissions for the share as above.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com