Link to home
Start Free TrialLog in
Avatar of bidgadget
bidgadgetFlag for United States of America

asked on

SQL Server Agent running SSIS package fails with error code 255

I have created an SSIS package in Visual Studio which performs a data flow task.  The package runs fine is visual studio.  I then created a server agent to run the package and the package failed with the below message.  I have not been able to find a solution

The step did not generate any output.  The return value was unknown.  The process exit code was 255.  The step failed.


Avatar of dfke
dfke

Hi,

This error  can be a frustrating issue, but there options to rule out things. Check the package logs: The first thing to check is the logs of your SSIS package, which will give you more information on what's going wrong. Check for dependencies: Make sure all the dependencies required by the package are available on the SQL Server. Verify the credentials: Ensure that the account under which the SQL Server Agent runs has the necessary permissions to execute the package. Verify that the configuration files are in place: If you are using a configuration file to set the properties for the package, double check it's in the correct location and that the SQL Server Agent has access to it.

You can also try to execute the package using the dtexec utility. If the package runs successfully using the dtexec utility, then it's possible that the issue is with the SQL Server Agent:

dtexec /FILE "\path\to\your_package.dtsx"

Open in new window




Cheers
Avatar of bidgadget

ASKER

thank you.  I agree very frustrating.  I have checked the items you suggested and still no luck.  I will try to run using dtexec as you said to see if that resolves and report back.
when I run the DTS command line it runs then crashes.
Hi,

also:

- Check the SQL Server Agent job history to see if there are any additional error messages or details that may help you determine what's going wrong.
- Check the Windows event logs for any related messages or errors.
- Try running the package in 32-bit mode if it's currently running in 64-bit mode or vice versa. You can do this by changing the value of the "Use 32 bit runtime" option in the SQL Server Agent job step properties.


Cheers
Sounds like a  security issue, you can run the SSIS-package within Visual Studio, therefore:
  1. firewall-rules are allowing your workstation to communicate with source/destination servers
  2. your user-account has access to both source/destination servers
  3. if the data-flow is accessing files within a folder then
    • your user-account has elevated folder-access in source/destination servers
    • your user-account may have access to a folder-share (virtual)
ASKER CERTIFIED SOLUTION
Avatar of dfke
dfke

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you all for your help.  This was frustrating because the logs did not show.  Turned out to be a combination of things.  Permissions (needed proxy) as well as part of the packaged required 32 bit, so I ended up separating the package into multiple and identified in step config which is 32bit.   thanks again all.