Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Logging for sql agent job in ssis 2012


Logging for sql agent job in ssis 2012

Author
Message
peacesells
peacesells
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 1475
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?
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16499 Visits: 13207
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
peacesells
peacesells
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 1475
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?
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16499 Visits: 13207
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
peacesells
peacesells
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 1475
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16499 Visits: 13207
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
peacesells
peacesells
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 1475
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16499 Visits: 13207
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
Shon Thompson
Shon Thompson
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
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?
ymbaez
ymbaez
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 345
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search