AZGroups.com

Does anyone know what would cause a DTS Package (that executes fine when run from the designer) to fail when run as a Scheduled Job?

That's the question, here's the details...

The DTS Job has 8 steps (7 Execute SQL tasks, and 1 Execute Package task).  In a nutshell, I clear out and set up a Temp table in the first few steps, then execute another DTS package that connects to our AS/400 and imports some records -- this DTS package was made from the Import/Export Wizard which I just saved as a package.  The remaining SQL steps just compare the temp table records to the real table records, deleting, updating, adding, etc, records to the real table from the temp.

In the DTS Designer if I execute the package, it runs just fine beginning to end.  I then close the designer, right click the Package name in the Local Package pane, choose and setup a schedule for it.  Then, in the Management --> Jobs pane, I right click the newly created Job and choose Start Job.  When I ran the package from the designer it took all of 2 to 3 seconds to complete.  Running the job goes for nearly 15 before it comes back as Failed with the following error:

"Executed as user: SQLPRO001\SYSTEM. DTSRun:  Loading...      Error:  -2147467259 (80004005); Provider Error:  17 (11)      Error string:  [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.      Error source:  Microsoft OLE DB Provider for SQL Server      Help file:        Help context:  0.  Process Exit Code 1.  The step failed."

I don't get it.  How can a package run fine in the designer then fail when run as a scheduled job, giving me an error that the SQL Server doesn't exist?? Huh??

Help?

-- Andrew

In an attempt to get this thing working, I decided to try breaking the DTS package apart into individual steps.  Where before the Job would run a single DTS that contained the 8 steps, I created a Job that has the 8 steps as steps within the Job itself.

And now, this Job runs just fine.

I don't understand what is happening here.  Is there something I need to be aware of or that I am missing here?  Can someone help clear up the confusion?

Thanks

-- Andrew

Several people have already accurately pointed to the permissions issues that arise in DTS packages when running in the designer vs. through the job agent.  When you run a job in the designer, it ignores the owner specified in its definition and runs under the current user account.  When running under the job agent, it executes with the permissions of the owner specified.  If these are different, you will get problems and just about every developer has encountered them sometime in his/her career.

When you broke apart the job into separate steps, did you explicitly set the permissions differently, or, you might check that the default owner for the job's steps is not different than when creating the steps individually.  If it is not related to permissions, I wouldn't understand why it would run with the steps broken apart vs. together.

Ok, I am getting very confused and feel burried with permissions issues that do not make sense to me.  Let me try to get out what I see....

When I create a DTS package, it gets saved as/under the account I am logged in as.  Ok, fine, but the only way to change the owner (that I know of) is to log in as someone else, and do a Save As.  I mention this becuase if I am remoted in to the box, if I am on my box but using Ent Mngr to view the remote SQL Server, or if I am on another SQL Server alltogether and save a DTS to the remote SQL Server, in each of these cases I am the one banging on the keyboard but the actual owner can be different.

Now, you, and many others, say that when executing a DTS in the designer it runs under the account I am loggd in as, but when executed via a Job it runs under a different account...ok, I get this, and makes sense.  But it still doesn't help me understand what is happening.  In a Job I can set the owner of the Job...is this the account it runs under?  But when I check out the error message it says "Executed as user: SQLPRO001\SYSTEM...." but I have the Job owner set as "SQLPRO001\[myaccount]" so why the difference?

When I created the second Job (the multi-step job), I was able to set the steps that executed SQL Queries to run as "dbo" but the step that executes a DTS I could not alter the account to use.  So now it gets even worse.... I have the Job set to an owner, 7 of the 8 steps set to run as dbo, the DTS being called is running under the account of its creator, and the Job (according to the error) is running under the account SYSTEM.\

And I still don't understand why a the original DTS package that executes SQL Tasks and one other DTS package run as a job fails.  But then I make a new job to run the SQL and internal DTS package as individual steps, does not fail.  Both the "all-in-one" DTS package and the DTS package that was run as one of the steps have the same Owner, are executed via the same method in a Job, but one fails, the other doesn't.

Where's my Advil.....

-- Andrew

Andrew,

It sounds like you found the job owner specification and that is not the problem (let's hope).  A quick search yielded some more specific details about running the DTS package as a job and the differences between the two.  I've extracted the appropriate content from their sources:

From:
http://www.databasejournal.com/features/mssql/article.php/3404791

You can also schedule execution of DTS packages using SQL Server Agent jobs. This ability is granted by default to the public group, based on permissions to execute sp_add_job and sp_add_jobschedule stored procedures in the MSDB database (this applies to all SQL Server Agent jobs, not just the ones that invoke DTS packages). Typically, it is recommended to restrict access to them to a limited number of privileged logins. While jobs typically execute in the security contexts of their owner, in the case of DTS packages this might be different. The reason is the fact that a task that contains invocation of a DTS package accomplishes this by the running DTSRun command line utility, which involves, in turn, the use of CmdExec. If you limit permissions to launch CmdExec jobs to members of the SysAdmin fixed server role (which is the default behavior), jobs will still execute in the same security context as their owners. However, you can change this default in the SQL Server Agent Properties dialog box, accessible by selecting the Properties option from the SQL Server Agent node under the Management folder in the SQL Server Enterprise Manager console. In the Job System tab, you need to clear the checkbox next to the "Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps" and provide a Proxy Account name and password. The proxy account provides the security context for execution of DTS packages, so ensure that it has access to all relevant data stores, file system paths or COM components, which might be used by ActiveX scripting tasks. Remember, however, that at the same time you allow users without SysAdmin privileges to run potentially dangerous ActiveScripting jobs.

What further complicates scheduling DTS packages is the fact that content of the corresponding SQL Server Agent jobs includes SID of the SQL Server Agent account as part of the encrypted string passed to the DTSRun command. This creates a problem if the password of the SQL Server Agent account is changed, since it forces you to recreate each package (existing packages will fail due to password mismatch). Also, keep in mind that the SQL Server Agent service needs to be running in order for scheduled job to be launched.

From:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;269074#E0PD0ABAAA

Common Problems

Here are a few other common problems you might encounter when you run DTS packages as scheduled jobs in SQL Agent:

Mapped Drives

If the package relies on the physical location of a file designated by a mapped drive letter, the package may fail when it is run as a scheduled SQL Agent job, regardless of who owns the package. SQL Agent is a Windows NT service and Windows NT services cannot see mapped drive letters. The mapping is part of the user's profile that is loaded when a user logs on to a Windows NT session. Services do not work with user profiles. Use a UNC path instead of a mapped drive letter. For additional information about why a service cannot use a mapped drive, click the following article number to view the article in the Microsoft Knowledge Base:

Relative Path

A relative path (or drive letter) is specific to the current location of the package (like C:\). If a package is designed on a workstation and is then scheduled, the location from which the package is run changes. The drive letter paths now reference a different physical location, that of the server. Unless the referenced files are also moved to the server, the package execution fails.

COM Components in ActiveX Scripts

If COM components (for example, calls to Microsoft ActiveX Data Objects (ADO), Remote Data Objects (RDO), or Decision Support Object (DSO) objects) are called in an ActiveX script, the called components must exist on the computer from which the DTS package is being run. If you run the package from the DTS Designer in SEM or DTSRun.exe, the components must exist on the computer at which you are seated. If the package is scheduled to be run by SQL Agent, the called components must be loaded on the computer hosting the SQL Server.

Package Security

DTS packages can have owner passwords and user passwords. These passwords affect who can edit and run the packages. Neither of these affect the security context in which the package is run.

SQLAgentCmdExec Permissions

If the job is executed under the context of the SQLAgentCmdExec account, and the SQLAgentCmdExec account does not have login rights to the SQL Server, the job may fail with the following error message:

You need to grant the SQLAgentCmdExec account proper login and database permission rights to the SQL Server.

Eric,

Thanks much....gona take me some time to digest all this, let alone comprehend and fully understand it.  Maybe one of these days everything won't always seem so dam complicated. :)

-- Andrew