January 4, 2015 at 7:23 am
We have recently started using SQL Server 2012. Given that we have multiple servers running 2008, we continued using our custom SSIS logging reporting process to monitor the results of our many automated processes.
Interestingly (frustratingly), both the centralized logging to a table and the built in logging functionality of the 2012 Integration Services catalog work fine as long as there are no errors. If there is an error, the execution is not even appearing in the centralized logging table, but all information is still captured by the built in logging functionality.
Of course, there is obviously redundancy here, but it is beneficial for us to have a consistent process while we still have 2008 boxes in play. This hasn't been a problem until recently, so it may be that a restart would resolve the problem. However, we have a lot of critical real-time processes that depend on this server so a restart is likely not be an option and is not within my power to dictate.
Has anyone else ever experienced this issue and if so, how did you resolve?
Thank you.
January 5, 2015 at 12:50 am
m_swetz (1/4/2015)
We have recently started using SQL Server 2012. Given that we have multiple servers running 2008, we continued using our custom SSIS logging reporting process to monitor the results of our many automated processes.Interestingly (frustratingly), both the centralized logging to a table and the built in logging functionality of the 2012 Integration Services catalog work fine as long as there are no errors. If there is an error, the execution is not even appearing in the centralized logging table, but all information is still captured by the built in logging functionality.
Of course, there is obviously redundancy here, but it is beneficial for us to have a consistent process while we still have 2008 boxes in play. This hasn't been a problem until recently, so it may be that a restart would resolve the problem. However, we have a lot of critical real-time processes that depend on this server so a restart is likely not be an option and is not within my power to dictate.
Has anyone else ever experienced this issue and if so, how did you resolve?
Thank you.
Given that we know nothing about the way in which you have engineered your custom logging process, this is rather difficult to answer.
By 'built-in logging functionality', do you mean that provided by SSISDB?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 5, 2015 at 1:08 am
m_swetz (1/4/2015)
We have recently started using SQL Server 2012. Given that we have multiple servers running 2008, we continued using our custom SSIS logging reporting process to monitor the results of our many automated processes.Interestingly (frustratingly), both the centralized logging to a table and the built in logging functionality of the 2012 Integration Services catalog work fine as long as there are no errors. If there is an error, the execution is not even appearing in the centralized logging table, but all information is still captured by the built in logging functionality.
Of course, there is obviously redundancy here, but it is beneficial for us to have a consistent process while we still have 2008 boxes in play. This hasn't been a problem until recently, so it may be that a restart would resolve the problem. However, we have a lot of critical real-time processes that depend on this server so a restart is likely not be an option and is not within my power to dictate.
Has anyone else ever experienced this issue and if so, how did you resolve?
Thank you.
I have used the SSIS catalog for over a year now, and here it logs all errors pretty nicely.
What logging level are you using?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 5, 2015 at 5:57 am
Phil Parkin (1/5/2015)
Given that we know nothing about the way in which you have engineered your custom logging process, this is rather difficult to answer.
By 'built-in logging functionality', do you mean that provided by SSISDB?
Yes, I do.
January 5, 2015 at 6:22 am
By custom logging, I mean I am using an SSIS log provider for SQL Server to log the OnError, OnTaskFailed, and OnWarning events to a SQL Server table. I then do some custom reporting off of the table.
Probably a poor choice of words.
January 5, 2015 at 6:43 am
m_swetz (1/5/2015)
By custom logging, I mean I am using an SSIS log provider for SQL Server to log the OnError, OnTaskFailed, and OnWarning events to a SQL Server table. I then do some custom reporting off of the table.Probably a poor choice of words.
And which one is not working?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 5, 2015 at 10:38 am
If any error occurs, no records are being written to the table at all.
I've just re-deployed the project to the SSISDB and am waiting for an error to see if that corrects the issue.
January 5, 2015 at 12:05 pm
m_swetz (1/5/2015)
If any error occurs, no records are being written to the table at all.
Which table?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 5, 2015 at 12:28 pm
The dbo.sysssislog system table.
January 5, 2015 at 12:39 pm
m_swetz (1/5/2015)
The dbo.sysssislog system table.
That's because in the project deployment model, SSIS logs to the catalog or in other words, to the SSISDB database.
Take a look there in the views under the catalog schema. You'll find your errors there.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 5, 2015 at 1:13 pm
Thank you, I will take a look.
Just for my own edification, does it log to the catalog exclusively on certain errors under the project deployment model? I still get logging to both when everything works correctly.
January 5, 2015 at 1:21 pm
m_swetz (1/5/2015)
Thank you, I will take a look.Just for my own edification, does it log to the catalog exclusively on certain errors under the project deployment model? I still get logging to both when everything works correctly.
It will log everything to the catalog according to the logging level. Errors are always logged to the catalog.
If you specify the built-in logging in the package itself (using a log provider et cetera), it will log whatever events you have configured to be logged to the sysssislog table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 5, 2015 at 1:38 pm
Koen Verbeeck (1/5/2015)
m_swetz (1/5/2015)
Thank you, I will take a look.Just for my own edification, does it log to the catalog exclusively on certain errors under the project deployment model? I still get logging to both when everything works correctly.
It will log everything to the catalog according to the logging level. Errors are always logged to the catalog.
If you specify the built-in logging in the package itself (using a log provider et cetera), it will log whatever events you have configured to be logged to the sysssislog table.
That's what I don't understand. I've checked the package and I'm logging the OnWarning, OnError, and OnTaskFailed events. I've had the same package fail twice today. One of the executions with errors (transaction was deadlocked and was chosen as the victim) logged to the sysssislog table. The other ("there were errors during task validation"), logged nothing whatsoever to the sysssislog (including the start and end of the execution).
Thank you.
January 5, 2015 at 1:49 pm
m_swetz (1/5/2015)
Koen Verbeeck (1/5/2015)
m_swetz (1/5/2015)
Thank you, I will take a look.Just for my own edification, does it log to the catalog exclusively on certain errors under the project deployment model? I still get logging to both when everything works correctly.
It will log everything to the catalog according to the logging level. Errors are always logged to the catalog.
If you specify the built-in logging in the package itself (using a log provider et cetera), it will log whatever events you have configured to be logged to the sysssislog table.
That's what I don't understand. I've checked the package and I'm logging the OnWarning, OnError, and OnTaskFailed events. I've had the same package fail twice today. One of the executions with errors (transaction was deadlocked and was chosen as the victim) logged to the sysssislog table. The other ("there were errors during task validation"), logged nothing whatsoever to the sysssislog (including the start and end of the execution).
Thank you.
Ah, validation errors. Those can be tricky. It depends on which object you defined the logging. Sometimes the validation failed before the object was even active, so it doesn't log anything. Did you configure the events on the package level?
My opinion is that from SQL Server 2012 onwards, you should try to use the SSIS catalog.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 6, 2015 at 11:31 am
That's what I ended up doing this morning. Once I pointed everything at the built in logging tables, it worked fine.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply