dtsrun utility problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

dtsrun utility problems

Author

Topic

craig.handley
Starting Member

4 Posts

Posted - 2002-03-08 : 06:56:24

I am running the dtsrun utility from a stored procedure:
EXEC master..xp_cmdshell 'dtsrun /Sserver_name /Npackage_name /Uuser_name /Ppassword'

The DTS package imports a text file into a table.

When the file that the DTS package is importing is on a local drive, the stored procedure works OK, but when the file is located on a network drive I get the following error:

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: Copy Data from gehe to [Epos].[dbo].[GeheDat] Step
DTSRun OnError: Copy Data from gehe to [Epos].[dbo].[GeheDat] Step, 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: Copy Data from gehe to [Epos].[dbo].[GeheDat] Step
DTSRun: Package execution complete.

Please can anyone help!!

Thanks, Craig

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-08 : 07:55:44

2 things:

1. Don't use a mapped drive letter (like F: or G:) to access the file, use the UNC path (\\server\folder\file.txt) Try this first, if it still doesn't work:

2. Check that the system account under which MS SQL Server and SQL Server Agent run have rights to the network drive(s). You can check these in Enterprise Manager by right clicking the server registration and choosing the Properties:Security tab, and for SQL Agent, right-click it and choose Properties:General. You may need to coordinate the login permissions with your network admin.

craig.handley
Starting Member

4 Posts

Posted - 2002-03-08 : 09:20:23

I am using the UNC path - so that is not the problem.

In the SQL Server Properties Security tab the Startup Service Account is set to an NT user account that does have access to the network drive that I am trying to access. I cannot find the SQL Agent login details, I have tried under SQL Server Properties General tab but no details seem to be there.

Anything else that I can try?

Craig

craig.handley
Starting Member

4 Posts

Posted - 2002-03-08 : 09:48:34

I've found the SQL Server Agent Service Startup Account and that's set to an NT User Account that has access to the network drive as well.

Please help - I'm really stuck!

Regards, Craig

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-03-08 : 16:32:48

Just to make sure that you're using the UNC path correctly check this post

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9983

helena

craig.handley
Starting Member

4 Posts

Posted - 2002-03-11 : 03:38:30

I know that the DTS Package is set up correctly as I can run it from Enterprise Manager OK, I can also run the package by calling the dtsrun utility from the MSDOS command line. It is only when I call the the xp_cmdshell stored proc that runs the DTS package (via dtsrun utility) that tries to access a network drive that I get Access is Denied.

tjubaer
Starting Member

1 Post

Posted - 2008-03-04 : 06:59:48

quote:
Originally posted by craig.handley

I know that the DTS Package is set up correctly as I can run it from Enterprise Manager OK, I can also run the package by calling the dtsrun utility from the MSDOS command line. It is only when I call the the xp_cmdshell stored proc that runs the DTS package (via dtsrun utility) that tries to access a network drive that I get Access is Denied.

Hey Craig

I faced the same problem like you did. And i got the resolution.

Scenario:
CSV file in one PC accessible via UNC path.
DTS package file is in another PC.
Run DTS package file from the second PC using query analyzer.

Resolution:
- Created an user account in PC 1 (user/user).
- Created same user account in PC 2 (user/user).
- On PC 2 go to registration property of the localhost from enterprise manager.
- Click security tab
- Under the section start up service account select this account radio button and enter user name and password (pcname\user/user)
- Restart sql server
- Using enterprise manager go to management > sql server agent property
- Under the section service start up account select this account radio button and enter user name and password (pcname\user/user)
- Restart sql server agent
- now run the dts package from sql query analyzer using xp_cmdshell

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources