Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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: Today @ 1:09 PM
Points: 233, Visits: 1,054

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:32 PM
Points: 13,017, Visits: 10,801
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 LessThanDot.

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: Today @ 1:09 PM
Points: 233, Visits: 1,054
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:32 PM
Points: 13,017, Visits: 10,801
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 LessThanDot.

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: Today @ 1:09 PM
Points: 233, Visits: 1,054
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:32 PM
Points: 13,017, Visits: 10,801
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 LessThanDot.

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: Today @ 1:09 PM
Points: 233, Visits: 1,054
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:32 PM
Points: 13,017, Visits: 10,801
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1586653
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse