SSIS Packages Job fails no information in Job History

Question

SSIS Packages Job fails no information in Job History

Welsh Corgi

SSC Guru

Points: 116520
More actions
April 22, 2016 at 12:08 pm

#400062

I have two SSIS Package in which the Job fails and there is no information in Job History other then the step failed.
There are task to TRUCATE and reload the table.
Other than creating task in the package to store information in a table for each table that is refreshed is there a way to identify the cause of the failure?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

bit difficult to say....bit sparse on info.....care to elaborate a bit. ?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Welsh Corgi (4/22/2016)
I have two SSIS Package in which the Job fails and there is no information in Job History other then the step failed.
There are task to TRUCATE and reload the table.
Other than creating task in the package to store information in a table for each table that is refreshed is there a way to identify the cause of the failure?

Use the Log Welsh!

😎

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

J Livingston SQL (4/22/2016)
bit difficult to say....bit sparse on info.....care to elaborate a bit. ?

The problem is that the Job that executes a package fails with nothing in the Job history but the Step Failed.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323504 More actions · Answer 4

check to see how long your history retention is; it could easily have been written, but aged out by the time you look at it.

if it is still a the server default, failed job steps could easily be pushed out/aged due to other job steps(ie LOG backups ever 15 minutes,for 100 database,s is 1500 messages, 500 more than the 1K default.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Brandie Tarvin SSC Guru Points: 173002 More actions · Answer 5

The most common cause of a TRUNCATE TABLE failure is the login running the code doesn't have db_owner on the database or ALTER permissions on the database. ISTR that even granting ALTER didn't work for me. So try changing the TRUNCATE TABLE to DELETE FROM TABLE.

If it fails then, there is another problem. And you'll have to look at the logs.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Brandie Tarvin (4/22/2016)
The most common cause of a TRUNCATE TABLE failure is the login running the code doesn't have db_owner on the database or ALTER permissions on the database. ISTR that even granting ALTER didn't work for me. So try changing the TRUNCATE TABLE to DELETE FROM TABLE.
If it fails then, there is another problem. And you'll have to look at the logs.

I already did that.

Testing.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Lowell (4/22/2016)
check to see how long your history retention is; it could easily have been written, but aged out by the time you look at it.
if it is still a the server default, failed job steps could easily be pushed out/aged due to other job steps(ie LOG backups ever 15 minutes,for 100 database,s is 1500 messages, 500 more than the 1K default.

I just created the job yesterday.

There is an entry for the job and the step but the only information that I get is that the step failed.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 8

You COULD always configure the SSIS packages to create logs which will tell you what happened on error.....

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

ok.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323504 More actions · Answer 10

you should still check what i mentioned.

last run status can still be seen in Job Activity Monitor with a big red X, but the history could be erased.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

Lowell (4/22/2016)
you should still check what i mentioned.
last run status can still be seen in Job Activity Monitor with a big red X, but the history could be erased.

Yes sir I thought that I mentioned that I did check the job history.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

I executed that package interactively and it barfed at a Enable Trigger Task.

I fixed the issue, then re-deployed and the job is running and is on the last load which is 37 + million rows.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 13

Welsh Corgi (4/22/2016)
Lowell (4/22/2016)
check to see how long your history retention is; it could easily have been written, but aged out by the time you look at it.
if it is still a the server default, failed job steps could easily be pushed out/aged due to other job steps(ie LOG backups ever 15 minutes,for 100 database,s is 1500 messages, 500 more than the 1K default.
I just created the job yesterday.
There is an entry for the job and the step but the only information that I get is that the step failed.
Thanks.

If you go to the "advanced" tab there within each step of the job, I believe you might find the option to "Include Step Output to Log" for future debugging purposes. Can't say for sure for SSIS steps because I avoid SSIS whenever I can and have been successful at that for quite a while. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

Jeff Moden (4/22/2016)
Welsh Corgi (4/22/2016)
Lowell (4/22/2016)
check to see how long your history retention is; it could easily have been written, but aged out by the time you look at it.
if it is still a the server default, failed job steps could easily be pushed out/aged due to other job steps(ie LOG backups ever 15 minutes,for 100 database,s is 1500 messages, 500 more than the 1K default.
I just created the job yesterday.
There is an entry for the job and the step but the only information that I get is that the step failed.
Thanks.
If you go to the "advanced" tab there within each step of the job, I believe you might find the option to "Include Step Output to Log" for future debugging purposes. Can't say for sure for SSIS steps because I avoid SSIS whenever I can and have been successful at that for quite a while. 😀

Thank you Sir.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/