Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Logging for sql agent job in ssis 2012 Expand / Collapse
Author
Message
Posted Tuesday, June 24, 2014 4:46 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 21, 2016 11:37 AM
Points: 284, Visits: 1,471

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?

Post #1585715
Posted Wednesday, June 25, 2014 12:01 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Thursday, September 22, 2016 2:39 AM
Points: 15,371, Visits: 13,116
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1585753
Posted Wednesday, June 25, 2014 10:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 21, 2016 11:37 AM
Points: 284, Visits: 1,471
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?
Post #1585991
Posted Wednesday, June 25, 2014 11:09 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Thursday, September 22, 2016 2:39 AM
Points: 15,371, Visits: 13,116
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1586019
Posted Wednesday, June 25, 2014 12:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 21, 2016 11:37 AM
Points: 284, Visits: 1,471
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.
Post #1586037
Posted Wednesday, June 25, 2014 12:13 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Thursday, September 22, 2016 2:39 AM
Points: 15,371, Visits: 13,116
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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1586041
Posted Thursday, June 26, 2014 11:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 21, 2016 11:37 AM
Points: 284, Visits: 1,471
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.
Post #1586593
Posted Thursday, June 26, 2014 2:25 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Thursday, September 22, 2016 2:39 AM
Points: 15,371, Visits: 13,116
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1586653
Posted Thursday, October 1, 2015 1:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 26, 2016 2:16 PM
Points: 22, Visits: 537
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?
Post #1724758
Posted Thursday, October 15, 2015 2:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 12:54 PM
Points: 55, Visits: 343
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.
Post #1728303
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse