Hi All,

I wrote a DTS Package that can take the data from a .txt file residing
in a shared folder on a different server (same domain) and dump the data
into a table.

When I open the DTS package and click on execute Package the package
works file.

I also wrote a stored procedure in which, I am running the above DTS
package using XP_CmdShell using something like below:

********************
Create PROCEDURE Run_DTSPackage_XYZ

AS

Declare @RunDTS as varchar(1000)
Declare @Result as int
Declare @sServerName as VarChar(255)
Declare @sPkgName as VarChar(255)
Declare @uPkgID as VarChar(255)
Declare @uPkgVID as VarChar(255)

Select @sPkgName = 'RUNDTS_XYZ'
Select @uPkgID = id, @uPkgVID = versionid from msdb..sysdtspackages
where Name = @sPkgName
Order by versionID

SELECT @sServerName = CONVERT(varchar(255),
SERVERPROPERTY('servername'))
Select @RunDTS = 'DTSRun /S ' + @sServerName + ' /U usr /P usrpwd' + '
/E /N ' + @sPkgName + ' /G ' + @uPkgID + ' /V ' + @uPkgVID
EXEC @Result = master..xp_cmdshell @RunDTS

GO
***************************

I ran this stored procedure in query analyzer and got an error like
"Error opening data file and access denaied.

I checked the folder and it has got full access to everyone eventhough I
want to restrict the access to that folder. Could someone advise me on
what is causing the access denial when I run the dts package using
DTSRun command eventhoug the package executes successfully using
UserInterface in Enterprise Manager? Any clue to resolve the issue would
be greatly appreciated. I want to have my packages run to get the data
from different servers via network path.

***********************

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.



*** Sent via Developersdex http://www.developersdex.com ***

The permissions when executing xp_cmdshell can be different depending on who
is asking.

From BOL

"When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in
which the SQL Server service is running. When the user is not a member of
the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail. This is true only for
Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and xp_cmdshell is always executed under the security context
of the Windows 9.x user who started SQL Server."

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Sreedhar Kaluva" <sreedhar (AT) erw (DOT) com> wrote

Hi Allan,

I'm experiencing the exact same problem, you have stated clearly that,

What would you advise to workaround this problem?

Note that our SQL server is automatically started when the Win 2003 server
is started, i.e. with the user localhost\SYSTEM. We thought about setting up
a dedicated user ID to start SQL server but we are really reluctant to do
that due to a number of reasons. What else could we do?

Much appreciate if you or anyone could shed some lights.

Thanks,
calvin
calvinleung (AT) sailfunds (DOT) com




"Allan Mitchell" wrote: