Is the SSIS Catalog going to be enough?

At first sight, the built-in reports deliverd within SSMS already give quite some information. I guess I have to dive real deep in an SSIS 2012 project at a client to find out what's missing.

The first thing that pops to mind is rowcounts affected by Execute SQL Tasks.

The problem with the new built in logging is the fact that it is not centralized. If you have 10 servers, you will have to visit each server to look at your ssis logs. So custom built logging or tools like BIxPress will continue to be needed.

Good point - totally depends on the tasks, but I know that our current implementation captures row counts from stored proc calls or ad-hoc SQL calls where appropriate. We've been keeping things somewhat simple for the time being, so we wouldn't have an immediate need to extend other logging functionality. I can definitely see it for larger and more complex packages, though.

We've mostly switched to BIxPress for our auditing and generally find that pretty useful. That may or may not change when we start using 2012. Our main reasons for using the software were for the real time monitoring and much easier reporting than our current custom logging. If SSIS duplicates all/most of that in 2012, we'd need to see some significant benefits to keep using that tool.

-Peter

Jamie,

I was thnking the same thing, what if I need to log a variable value OnPostExecute and report that value with the duration and result of a task?  We use custom logging based on your popular event handler example which logs Insert, Update, Delete counts OnPostExecute using variables.  As you mention it would be nice to be able to capture row counts from SQL tasks.

Is it possible to log the read\write variables of a task in the [catalog].[event_message_context] table?  That may do the trick.  Like if I set @[user::UpdateCount] using sproc output parameter in a SQL task, will it log that value in [catalog].[event_message_context]?  It would not be ideal to query those values from a related table compared to having columns in [catalog].[executable_statistics].  For that reason it may be best to add our own tables.

It would be cool if there were a way to extend the new logging by adding custom stored procs that can fire before & after the built-in ones to populate custom tables without the need to add custom event handlers to packages.  I suppose this would be easy enough by simply adding a SQL task to the event which could select from catalog to get duration, but that brings another question...What about the order of events?  Can we be sure the [catalog].[executable_statistics] is written before the OnPostExecute Event Handler is fired for the same task?

I agree it would be nice to specify the server and db name of SSISDB so two servers could share one catalog.

I'm fairly certain I'll keep my framework around, although I will have to play with the new features more before deciding they don't provide everything I need.  Currently, we log the package start / end times, what versions they are, the descriptions associated, what tables are affected by the package and how many records were extracted / inserted / updated / deleted.  Whether the package was successful, if it was restarted, what the parent process was that called it, which "processing group" the package belongs to, etc.  I also do not like that you can no longer control exactly what events are being logged.  You only have the four or five presets.  So, if you want to find the PipelineComponentTime or PipelineExecutionTrees you get every other event under the sun (verbose)???  gah.  Oh well, I guess for the majority of people this will be a good thing.

RE:"Yeah I like that idea a lot. In other words, it would be nice if there were some hooks that we just plug our own sprocs into so that they get executed when a particular event occurs, right?

I can't see that ever happening mind you, for starters SQL Server doesn't have a good plug-in model"

I'm thinking simple hook like this at the end of the built-in sp which logs to executable_statistics.  We create a sproc called 'sp_after_log_executable_statistics'and if it exists it's fired.  Of course how does it get paramenters from SSIS variables is beyond me.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[catalog].[sp_after_log_executable_statistics]') AND type in (N'P', N'PC'))

EXEC [catalog].[sp_after_log_executable_statistics]]

GO

RE:"I also do not like that you can no longer control exactly what events are being logged."

You mean you can't disable event handling on certain containers and tasks and you cant specify not to log OnInformation events for Basic level, for example?  That is a bummer.

The Catalog.packages view shows the major & minor version of a package. The catalog.executions view shows the package start & end times, and the package status (created, running, canceled, failed, pending, successful etc.).

The catalog.execution_data_satistics view shows the number of rows sent from the source component. You can use the catalog_add_data_tap & catalog.add_data_tap_by_guid stored procedures to capture a copy of the data for a specific data flow path.

We presently log the product version.  This helps to identify issues that can arise when machines are running different versions of SSIS.

PingBack from http://www.jamesserra.com/archive/2012/01/sql-server-2012-denali-ssis-logging/

Hey Jamie,

Looks like the release documentation for view [catalog].[event_message_context] shows a context_type field having possible value:

70 | Variable | Indicates that the value of a variable is captured.

http://msdn.microsoft.com/en-us/library/hh479590(v=sql.110).aspx

Hopefully this means variable values can be captured in the catalog.  Is it true that this is only captured for OnError events?

If so, it's another example of beng shame that we can't control when events are logged.  We should be able to capture context OnPostExec for selected tasks.  Bottom line, built-in logging should obey the logging properties set on each container\task if desired.

Josh