I have a DTS package which utilizes an "Execute Process
Task". This task calls an EXE local to my machine. When I
initially setup the DTS routine it worked. A few days
later I tried to run it again and the executable failed
on one of its steps.
I tested the executable thoroughly and decided that the
problem was with the DTS routine. I found that when I
perform the following steps the task runs properly:
1. Edit the DTS routine
2. Edit the properties of the Execute Process Task
3. Click on the "..." button to navigate to my EXE (even
though the path supplied is already correct)
4. Run it
However, after saving the package and then closing and re-
opening SEM, the same DTS routine fails when I execute it
manually without performing the steps above.
All I can think is that the pathing for the EXE is not the same for the server as it is on your local machine. Try running the job directly from the server in the same manner. If you get the same error it is to do with the pathing to the EXE file. I have something similar here and I have to run some DTS packages via terminal services to ensure correct pathing.
I know what you are saying (I've run into that before). I don't think that's the case here though because I am running everything on my local machine. So, if I understand you right, I am running the job on the "server" (my machine).
I'v been tinkering with DTS packages and found myself getting caught out with the server path vs client path. Some times it looks on the server i.e Browse folder will actually present a view of C:\Win... of the machine on which your instance resides and sometimes you're looking at your local machine. Confusing but makes sense sometimes.
The error message:
Step Error Source: Microsoft Data Transformation Services (DTS)Package
Step Error Description:CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code -1, which does not match the specified SuccessReturnCode of 1.
Step Error code: 80040496
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4900
I thought it was some kind of permissions issue, but I've tried several things relating to DTS package permissions to no avail.
if you set the package as a job on the server then it is a problem with the path name. The automatied job will run on the server thinking that the server is "god" not the local machine. I would say to share the local drive, map it to the server and set the path that way. It will work if you are running a job.
1. The server machine is my local machine so there is no issue with executable paths.
2. The DTS package is not running as a job. This issue occurs when I run it manually.
When I run the DTS package and it gets to the "Execute Process Task" step it does run the EXE every time. However, the EXE fails if I don't perform the steps listed in my initial post.
JDionne! You da man!! You got me thinking about the parameters and...
I just figured it out!!
I've been so busy looking at possible security resolutions that I missed an obvious one.
The problem was with the parameters to the EXE. One of the parameters is a filepath to an import file. Since the EXE is in the same directory with this file I had just been supplying the file name (with out specifying a path) to the parameter. This works when the current working directory is the path to my EXE. So that's why it would always work after performing the steps in my initial post. These steps changed the current working directory to be that of my EXE.
The solution was to add the path to my filepath parameter.