It would help to know which version of SQL Server you are using.
You may get some information from the history of the job step itself.
- In SSMS, under SQL Server Agent, find the job.
- Right click the job and choose View History.
- Each entry in the Log File Viewer is one job run, so expand a failed run by clicking the [+] sign next to it.
- Highlight the step (usually only one step if your job consists solely of running the SSIS Package). With the step highlighted, some information on what was returned by the failing piece will be in the grey information area at the bottom of the window. Unfortunately many times the information isn’t very useful like “The package execution failed. The step failed.”
Your next steps will depend on how you implemented your package. If you saved your package (SQL 2012 or 2014, I believe) in the Integration Services Catalogs, you can get more detailed information and view each step’s success or failure, by following these steps:
- In SSMS, expand Integration Services Catalogs
- Expand Packages
- Expand Projects
- Choose and highlight the project you saved your SSIS Package under
- Right click the project and choose Reports -> Standard Reports -> All Executions
- This will bring up a report window where you could see each execution of your package, and by selecting “All Messages” for the one that failed, you will see detailed output from your package steps.
If you are using a SQL Server version prior to 2012, or are using the file system method of package execution, the steps will be different. If you could elaborate on your environment, it would be very helpful.