DTS Scheduling problem

[StJimi]

Help !!! I've been caught out by the DTS works fine from Designer in Enterprise Manager and runs when manually called by a user (execute package or DTSRun) then refuses to work in the SQL Scheduler. I think the problem might be because in the DTS Package I make a call to a WIN32 .exe with commandline items. This doesn't appear to be allowed when the task is run from the scheduler using DTSRun.

DTSRun /S"Target Server" /E /N"Target Package".

The Task runs the package until it reaches the step that calls the WIN32 .exe it throws the following error:

Executing as user:domain\username. DTSRun: Loading... DTSRun Executing... DTSRun On Start: DTSStep_DTSCreateProcessTask_4 DTSRun OnError: DTSStep_DTSCreateProcessTask_4, Error = -2147220330 (80040496) Error String: CreateProcessTask 'DTSStep_DTSCreateProcessTask_4': Process returned code 128, which does not match the specified SuccessReturnCode of 0 Error source: Microsoft Data Transformation Services (DTS) Package Help File sqldts80.hlp Help Context 4900... ...Process Exit Code 1.

Unfortunately the WIN32 .exe which is an application based importer utility requires a mapped drive I found in Microsoft KB269074 (

http://support.microsoft.com/kb/269074/EN-US/)

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: 180362 INFO: Services and redirected drives

I've already been over the security context issues in production and the account being used by SQLAgent has full rights and is a SYSAdmin and is also the account used to start both the server and SQLAgent. I'm guessing that when the job runs the DTSTask because it is running as a service on the Server it can't run the win32 .exe in a User Context ie if you terminal onto the Server and use DTSRun. Am I really stuck here ? Is there anyone out there who has had to overcome anything similar ? Any advice would be much appreciated !!!

Thanks

 

[EdwinGene]

If you SQL Server is on a different box from your workstation (which it probably is) you have to remember the context under which the DTS package is running.

When you run the DTS from DTS Designer you are running under the context of your workstation. If you use any drives in your DTS package, they will be the drives mapped on your workstation (e.g. C: is your local C:, F: is whatever you have F: mapped to on your workstation). When you run under the context of a SQL Agent Job, the drive mapping used is the SQL Server box drive mapping.

Also, when you run a package from DTS Designer, you are running under the context of YOUR network login. When a package runs from a SQL Agent Job it is running under the context of the SQL Server network user. There may be permissions issues at work in this case.