September 10, 2007 at 10:33 am
I have taken over maintenance of a DTS package in which one step is an "Execute SQL Task" that attempts to drop and create tables in an Excel data source. This package has worked for ages, but when I try to edit that task I get the error "Microsoft JET Database Engine: Failure creating file". I have discovered that this error occurs even if I change nothing but simply open the task and click "OK".
I have searched for this error message on the internet and things keep coming up about file permissions, changing the account the jobs run as, etc. I am not even at the point where I am trying to run it, I am just trying to edit it. Wouldn't it use my file permissions (which are fine for this Excel file) in that case?
If anyone has hay ideas for me I'd appreciate it!
-- Stephen Cook
September 10, 2007 at 12:11 pm
If you're trying to run the package from the DTS Design window in Enterprise Manager, then your personal permissions are used.
If this is executed via a job, the permissions of the account the SQL Server agent is running under is used.
Make sure that you don't have Excel running on your computer while trying to edit the task. Make sure you look for Excel.exe in task manager and kill it if it's running.
Is the path of the Excel file in a global variable? If so, make sure it's being populated with the correct path.
September 10, 2007 at 1:23 pm
I'm not trying to run it at all, I'm trying to edit the SQL in the task (adding an additional worksheet with "CREATE TABLE").
Excel is not running, and the file is not locked from anyone else using it. The path is hard-coded in the "Connection Properties" for that Excel data source.
I have been poking some more, and here's a strange thing: The path to the file was hard-coded to a local disk drive on the server. I changed it to use the UNC path on the network, and now it works. I suspect that my credentials allow me network access to this folder but not access as a local drive (or something along those lines).
So I guess I solved my problem, although further comments are more than welcome.
-- Stephen Cook
September 10, 2007 at 1:39 pm
That's one of the "tricky" things to remember about DTS when you schedule it: there are two contexts, one for you connecting to it and editing the job, and the other under the user running the job when scheduled (if running from SQLAgent - the service account for SQLAgent.
The minute you edit the DTS package it's using YOUR credentials and your context (meaning, the drives available to you in your own user profile on whatever machine you're using at that point). If your job calls for a drive K:\, then both your user account had better have a drive K:, with the same content(or something structurally equivalent) in the SAME place, etc...
I've personally resorted to two tactics once I scheduled a DTS job:
- don't edit the DTS package from my machine anymore. I resort to doing any adjustments I need from something like Terminal Services to the server, logged in under the service account (so I'm fully aware of what I can get to) that runs the job.
- if it's a job that's heavy on the "outside of SQL" - I schedule it OUTSIDE of SQL. I can then set up a "dedicated" service account to run the DTS package and mess with whatever non-SQL beast it's tangling with that day. Do a search in BOL as to "DTSRUN" if you're interested.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply