June 24, 2014 at 4:46 pm
I know in previous versions, msdb database would log/capture all sql server agent job details. Now with 2012 and SSISDB, if a job fails in SQL Agent where would it be logged? Also what queries are run behind the scence for execution reports we get in integration Service Catalogs?
June 25, 2014 at 12:01 am
peacesells (6/24/2014)
I know in previous versions, msdb database would log/capture all sql server agent job details. Now with 2012 and SSISDB, if a job fails in SQL Agent where would it be logged? Also what queries are run behind the scence for execution reports we get in integration Service Catalogs?
The actions are logged in the SSISDB database, if you are using the project deployment model.
You can use SQL Server Profiler to find the executed report queries.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 25, 2014 at 10:12 am
Koen Verbeeck (6/25/2014)
peacesells (6/24/2014)
I know in previous versions, msdb database would log/capture all sql server agent job details. Now with 2012 and SSISDB, if a job fails in SQL Agent where would it be logged? Also what queries are run behind the scence for execution reports we get in integration Service Catalogs?The actions are logged in the SSISDB database, if you are using the project deployment model.
You can use SQL Server Profiler to find the executed report queries.
Thanks- Does that mean that all SQL agent job activities are logged in the ssisdb database instead of traditional msdb for project deployment model? I see no information in tables in ssidb that that ties to SQL agent job?
June 25, 2014 at 11:09 am
peacesells (6/25/2014)
Koen Verbeeck (6/25/2014)
peacesells (6/24/2014)
I know in previous versions, msdb database would log/capture all sql server agent job details. Now with 2012 and SSISDB, if a job fails in SQL Agent where would it be logged? Also what queries are run behind the scence for execution reports we get in integration Service Catalogs?The actions are logged in the SSISDB database, if you are using the project deployment model.
You can use SQL Server Profiler to find the executed report queries.
Thanks- Does that mean that all SQL agent job activities are logged in the ssisdb database instead of traditional msdb for project deployment model? I see no information in tables in ssidb that that ties to SQL agent job?
No, just the related SSIS information is logged in the SSISDB. The logging of the job and jobsteps is still in MSDB.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 25, 2014 at 12:07 pm
Koen Verbeeck (6/25/2014)
peacesells (6/25/2014)
Koen Verbeeck (6/25/2014)
peacesells (6/24/2014)
I know in previous versions, msdb database would log/capture all sql server agent job details. Now with 2012 and SSISDB, if a job fails in SQL Agent where would it be logged? Also what queries are run behind the scence for execution reports we get in integration Service Catalogs?The actions are logged in the SSISDB database, if you are using the project deployment model.
You can use SQL Server Profiler to find the executed report queries.
Thanks- Does that mean that all SQL agent job activities are logged in the ssisdb database instead of traditional msdb for project deployment model? I see no information in tables in ssidb that that ties to SQL agent job?
No, just the related SSIS information is logged in the SSISDB. The logging of the job and jobsteps is still in MSDB.
Thanks Koen.
Thats what I thought but i am little confused because whenever a sql agent job fails you get the following message :
" Package execution on IS Server failed. Execution ID: 30620, Execution Status:4. To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report" - so the report is coming off of msdb or ssisdb database?
btw i read a lot of your SSIS articles - it is always helpful - Thank You.
June 25, 2014 at 12:13 pm
The SSIS error messages are logged in the SSIS catalog. The reports in the SSIS catalog fetch their data from the SSISDB database.
In previous versions, the error messages were logged in the MSDB database (as part of the Agent job logging) and/or in a logging provider defined in the package.
ps: thanks for the compliment 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 26, 2014 at 11:39 am
Koen Verbeeck (6/25/2014)
The SSIS error messages are logged in the SSIS catalog. The reports in the SSIS catalog fetch their data from the SSISDB database.In previous versions, the error messages were logged in the MSDB database (as part of the Agent job logging) and/or in a logging provider defined in the package.
ps: thanks for the compliment 🙂
That completely makes sense. However i was trying to find a co-relation between a sql agent job and the logging in ssisdb database tables.I can see that when a job fails it logs the error message in table in [SSISDB].[internal].[operation_messages] but i could not find any information to say that this error message in the ssisdb is for step3 of job for example.
June 26, 2014 at 2:25 pm
peacesells (6/26/2014)
Koen Verbeeck (6/25/2014)
The SSIS error messages are logged in the SSIS catalog. The reports in the SSIS catalog fetch their data from the SSISDB database.In previous versions, the error messages were logged in the MSDB database (as part of the Agent job logging) and/or in a logging provider defined in the package.
ps: thanks for the compliment 🙂
That completely makes sense. However i was trying to find a co-relation between a sql agent job and the logging in ssisdb database tables.I can see that when a job fails it logs the error message in table in [SSISDB].[internal].[operation_messages] but i could not find any information to say that this error message in the ssisdb is for step3 of job for example.
Nope, there is no such relation unfortunately.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 1, 2015 at 1:22 pm
Thanks everyone for the input on this thread. I don't see how MS can expect anyone to use this approach for deployment in a large data warehouse. If you have multiple jobs using the same packages running at the same time, how will you know which errors were associated to which jobs? If they are running at the same time, you will need to dig through a ton of information to get there. In a large IT shop, I can see someone spending a half hour on one failure to gather information that used to take 30 seconds. Has anyone used this approach on a large scale?
October 15, 2015 at 2:13 pm
I'm not a fan of this SSIS catalog reports to identify errors for job failures so I had to find a way to modify a report I already had with the error message from the MSDB database. I added the SSIS Catalog Error Message by using this approach:
1) Find the SSIS Package path in the MSDB database on the sysjobsteps, had to use SUBSTRING/CHARINDEX to get the actual package name from the command column.
2) Find the agent job execution date/time from the sysjobhistory table.
3) Find the SSIS Package path in the SSIS database on the catalog.executions view by concatenating '\SSISDB\' + folder_name + '\' + project_name + '\' + e.package_name
4) Find the package execution start_time on catalog.executions
5) Add ROW_NUMBER to each agent job step exec/ssis package exec order by the startdate
6) Join the MSDB with the SSIS on packagename and row_number and status = 4 (failure) to match up MSDB/SSIS execution and get the execution ID.
7) Then find the error message for the failure using the SSISDB.catalog.event_messages, filtering on operation_id with the execution ID and event_name = 'OnError'
I'd be happy to share the code, I'm sure there's probably a better way to do this but it works find for me for what I need. Message me if interested.
October 15, 2015 at 3:06 pm
ymbaez (10/15/2015)
I'm not a fan of this SSIS catalog reports to identify errors for job failures so I had to find a way to modify a report I already had with the error message from the MSDB database. I added the SSIS Catalog Error Message by using this approach:1) Find the SSIS Package path in the MSDB database on the sysjobsteps, had to use SUBSTRING/CHARINDEX to get the actual package name from the command column.
2) Find the agent job execution date/time from the sysjobhistory table.
3) Find the SSIS Package path in the SSIS database on the catalog.executions view by concatenating '\SSISDB\' + folder_name + '\' + project_name + '\' + e.package_name
4) Find the package execution start_time on catalog.executions
5) Add ROW_NUMBER to each agent job step exec/ssis package exec order by the startdate
6) Join the MSDB with the SSIS on packagename and row_number and status = 4 (failure) to match up MSDB/SSIS execution and get the execution ID.
7) Then find the error message for the failure using the SSISDB.catalog.event_messages, filtering on operation_id with the execution ID and event_name = 'OnError'
I'd be happy to share the code, I'm sure there's probably a better way to do this but it works find for me for what I need. Message me if interested.
i totally agree-I think it does pretty good job of logging but then requires a lot of digging when you need something particular. I think I did something similar when i had implement this quite a while ago. It wasn't an easy/short route.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply