June 2, 2014 at 9:34 am
Good Morning,
We are having an issue with 2012 SQL Servers not reporting why a job has failed, this is happening for jobs running SSIS packages in integration services (not the Integration Services Catalog). I have tried to reproduce the error without success and am looking to see if anyone else is seeing this behavior or sees what I am missing.
Usually when you look at the job history for the specific step that failed it will display the error like so:
Source: Execute SQL Task Execute SQL Task Description: Executing the query "select * from madeUpTable" failed with the following error: "Invalid object name 'madeUpTable'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:38:05 AM Finished: 11:38:05 AM Elapsed: 0.421 seconds. The package execution failed. The step failed.
However on some of our jobs we now get a very generic error which makes it very difficult to troubleshoot. If you add an text file in the output for the job step that does receive the whole error, but is set to override at each run (so we lose the error message if the next run is a success).
Executed as user: domain\username. Started: 3:00:01 AM Finished: 4:01:47 AM Elapsed: 3706.97 seconds. The package execution failed. The step failed.
I have tried the following to reproduce the problem, but every time I get the full error message:
• Changed LoggingMode property in the package to disabled, currently set as UseParentSetting
• Changed to execute a stored procedure
• Changed procedure code to be wrapped in a try..catch with raiserror(‘test’,16,1)
• Changed to raiserror(‘test’,18’1) (wondered if there was a smaller range of suitable error numbers)
• Added a Cozyroc task (3rd party) and made that fail
We have this happening on multiple servers, including one that was a new 2012 named instance and one that was upgraded from 2008 R2. We were curious whether the 2008 packages were not upgraded fully/properly with the wizard but this wouldn't be the case for the new 2012 instances.
We are now wondering if this is only happening with certain types of task, but have not managed to find the tasks during testing. Is anyone else experiencing this? Or any suggestions on how to resolve this issue?
Thanks in advance
June 2, 2014 at 12:19 pm
Have you licensed CozyRoc already on the server?
Brian Knight
Free SQL Server Training Webinars
June 2, 2014 at 12:23 pm
Thanks for your reply.
Yes Cozyroc is licensed on the machine and it's not just packages with CozyRoc that are not showing the errors.
June 2, 2014 at 1:35 pm
You may want to consider applying the BI xPress SSIS auditing framework (community edition is free) so you can track the failure a little cleaner than a text file. The community edition is free.
A good place to start looking since you're not in the catalog is with the ProtectionLevel property of the failing packages. If they're using SQL authentication or store any type of secure data, make sure you're using a password or change the property to EncryptSenstitiveWithPassword and assign a PW. Once you do that, when you run the package in Agent you can pass in the /Decrypt to decrypt the package with the proper password.
Brian Knight
Free SQL Server Training Webinars
June 2, 2014 at 1:42 pm
Do you have the logging enabled in the packages? If yes, which events?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 3, 2014 at 7:02 am
Brian Knight (6/2/2014)
You may want to consider applying the BI xPress SSIS auditing framework (community edition is free) so you can track the failure a little cleaner than a text file. The community edition is free.A good place to start looking since you're not in the catalog is with the ProtectionLevel property of the failing packages. If they're using SQL authentication or store any type of secure data, make sure you're using a password or change the property to EncryptSenstitiveWithPassword and assign a PW. Once you do that, when you run the package in Agent you can pass in the /Decrypt to decrypt the package with the proper password.
I will look into the auditing framework, thanks. For the ProtectionLevel we have always used "Rely on server storage and roles for access control", my packages that are working correctly and the ones that are not both use this option. Is it better to use the password option?
June 3, 2014 at 7:30 am
Koen Verbeeck (6/2/2014)
Do you have the logging enabled in the packages? If yes, which events?
We have logging turned on for some of the packages but that writes to a text file not the SQL Agent job history does it?
Thanks for your reply.
June 3, 2014 at 7:34 am
jpomfret7 (6/3/2014)
Koen Verbeeck (6/2/2014)
Do you have the logging enabled in the packages? If yes, which events?We have logging turned on for some of the packages but that writes to a text file not the SQL Agent job history does it?
Yes. It might be useful to see what the SSIS log has to say.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 3, 2014 at 11:19 am
Have you opened the package and tried to run it in BI since the upgrade?
MCSE SQL Server 2012\2014\2016
September 22, 2015 at 3:43 am
I had the same Problem today.
For me the Setup was:
* I am not sysadmin on the Server
* I was not the owner of the job (the Job was owned by a SQL-Login)
Now the error message changes depending on who is starting the SQL Server Agent Job:
* If I start the SQL Server Agent Job: There is no error message (the same result as in the question of this post)
* If I log on with the SQL Login who owns the Job: There is the normal error message as we all are used to
I think this is quite strange.
Do not get me wrong:
The Job itself (or actually the step itself) is executed by the account defined in the step (i.e. SQL Server Agent Service account or Proxy).
This execution does not change depending on who starts the Job.
But the error message does change.
(I did not realize this earlier, because the Jobs ran smoothly. And then it does not make a difference who is actually starting the Job)
But, of course, this only applies to SQL Server Agent Jobs which are started manually and not by schedule.
I hope this helps
Have fun
Martin
September 30, 2015 at 11:35 am
I have the same problem with SQL Server 2014 with SP1. Very disappointing since I have never had this problem with any other version we are using. Running the package through the IDE produces the correct error in the IDE output window, so clearly the package is raising the error properly and this is not a logic problem. DTExec andn DTExecUI also produces the error, so it does appear to be a problem with the SQL Server agent.
July 20, 2016 at 10:11 am
i have a similar problem but this occurred once i changed the protection level from "EncryptSensitiveWithid" to "EncryptSensitiveWithPassword".So that i can execute the package from sql agent without using a proxy account and can execute it with sql agent service account, please let me know if there is any solution for the same.
Thanks
Amit
May 30, 2018 at 4:07 pm
Was anyone able to solve this issue?
we are having this same issue with SQL 2016 sp1.
Thank you in advance.
May 31, 2018 at 5:05 am
param.3553 - Wednesday, May 30, 2018 4:07 PMWas anyone able to solve this issue?we are having this same issue with SQL 2016 sp1.
Thank you in advance.
Hi Param.3553, we usually extract the detailed SSIS error messages from SSISDB.[catalog].[event_messages] via a script which runs on a SQL Job failure. It's a little convoluted but does help with out of hours support.
May 31, 2018 at 8:23 am
Steve Rodgers - Thursday, May 31, 2018 5:05 AMparam.3553 - Wednesday, May 30, 2018 4:07 PMWas anyone able to solve this issue?we are having this same issue with SQL 2016 sp1.
Thank you in advance.
Hi Param.3553, we usually extract the detailed SSIS error messages from SSISDB.[catalog].[event_messages] via a script which runs on a SQL Job failure. It's a little convoluted but does help with out of hours support.
we do the same thing...but getting the full error log in job detail is a lot more handy.
other weird thing is...it's a hit and miss....sometimes we do get a full detail and some times it's just 'failed' and only happens to SSIS.
This never happened in SQL 2008 R2 but started in SQL 2016.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply