Running Jobs (UNC vs Drive Letters )

By Bill Graziano on 2 July 2000 | Tags: Import/Export , DTS

Russell writes "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 . . .

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.

Related Articles

Use SqlBulkCopy to Quickly Load Data from your Client to SQL Server (4 May 2007)

Using Fuzzy Lookup Transformations in SQL Server Integration Services (22 January 2007)

How to Asynchronously Execute a DTS package from ASP or ASP.NET (27 March 2005)

Programmatically Save DTS Packages to Files (24 May 2004)

A Practical Guide to SQL Server Yukon Beta 1 DTS (4 February 2004)

Import text files script (19 October 2003)

DTS and C# (12 September 2003)

SQL Server DTS Best Practices (27 May 2003)

Other Recent Forum Posts

Object cannot be cast from DBNULL to other types coming randomly in SSIS Package-Migrated from VS 2008 to 2015,SQL 2008R2 to SQL2016 on 1st run only (2h)

Error in sp procedure- Msg 50000, Level 16, State 1, Procedure spCheckDBInfo, Line 193 [Batch Start Line 0 (5h)

Two records into a single record? (5h)

Convert integer 0101 into 1010 and 11 to 00 in SQL server (9h)

Basic SQL Join Error – HELP! (17h)

Dynamic SQL Problem (1d)

T-SQL -- store common definitions (1d)

SQL - Finding duplicate rows of data (1d)

- Advertisement -