Running Jobs (UNC vs Drive Letters )

The full text of Russell's question is When we create a DTS package to import files from lets say Visual Foxpro and use UNC in ODBC, the package runs fine. However when we try to run the package as a job, it won't run. We have to change the ODBC to reference a drive letter and then the job(s) run fine. We have numerous other situations where things work outside of the job structure, but won't run as a job. They all deal with file location (seems like files have to be on drives connected to the SQL Server machine and also we can't use a UNC description."

Thanks for the question Russell. Right now I'm fighting kind of weird problem myself. The questions are truncating on the screen at 255 characters. They store just fine in the database but just don't display properly. The hosting company is looking into it and I'm sure we'll have it fixed shortly.

Back to Russell's question. Remember that DTS packages don't always run where you think they do. As an example, suppose I have a text file on Server A and a SQL Server on Server B and I'm doing all my DTS development on my little, underpowered, overworked, underappreciated laptop (which only has the SQL Server tools) in the middle. Suppose I create a package to import 1000 records from Server A to Server B on my laptop and save it on Server B. When I run that package on my laptop it's actually running on my laptop. Those 1000 records will go from Server A to my laptop and on to Server B. Any references to ODBC data sources are based on what is defined on my laptop. The job attaches to Server A based on how I logged onto my laptop.

When you schedule the DTS task as a job on Server B, the package will now run on Server B using it's ODBC datasources. It will also run as the user setup to run the SQLAgent service. As a first step I'd suggest running the job from the console of Server B and making sure that works.

If you used Enterprise Manager to schedule the DTS task as a job you'll see it created a DTSRUN step with an encrypted command line. I'd junk the encrypted command line and create my own so I know exactly what it's doing. You can have the job run as a trusted connection (the SQLAgent user) or you can specify under what user account the job should run. You can check out books online for the syntax of the DTSRUN command.

Concerning drive letters vs. UNC names, I always try to use UNC names whenever possible. This gives me better portability since I don't have to worry about which machine has which drive letters mapped to what. Your problem seems to be the exact reverse though. I'd check to make sure that your permissions are set correctly. Remember that your scheduled job is running as the user that runs the SQLAgent service. I'd check to make sure this user can access the UNC you are referring to. Try logging into the console as your SQLAgent service account and running the job interactively.

I don't know if this will help you solve your problem or not but it should point you in the right direction. I've got comments up and running now so let us know how it's going. If it still doesn't work you might post the error message (or email it) and we'll see what else we can do.

Discuss this article: 2 Comments so far. Print this Article. This page has been read 32,521 times.

If you like this article you can sign up for our newsletter. We send it out each week that we post a new article. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Email This • Subscribe to this feed • Kick it • Save to del.icio.us • View blog reactions