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.

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

Definitely would love this:

>> Row counts captured using the Execute SQL Task.

Otherwise I'll have to develop my own framework.  Yuck.

Hi,

When we deploy the reports on the SQL Server (SSIS Catalog) and ran it, some reports are created automatically. I need row counts (Extracted, Inserted and updated) in those reports. Is there any property, so that i can see the row counts on the reports itself?

I don't want to go for the Custom Logging, rather than I want to go with those reports. But, for logging, i need row count should be shown for each package.

Please reply.

Thanks,

Onkar

Hey Jamie,

Sorry, its my mistake. I mean to say Packages, not reports. :) And thanks for quick Reply.

I have read the Carla's post above, where she mention the use of the [catalog].[execution_data_statistics] table. I looked into it, but it just gave us the Sent_Row_Count.

But, for the other counts, such as Insert and Update Count, do we need to go just for the Custom Logging? I'm also looking at the Custom Reports, if it helps me.

Thanks,

Onkar Belgamwar

Thanks Jamie,

Actually, i have some packages which just extract the Source data and load the Staging tables, in such cases i'm using the DFT. But, when i load the dimensions, i'm either using the SCD in DFT OR Merge Statement in the Execute SQL Task (In this case, i replace DFT with Merge Statement) for incremental load. So i need to capture those counts in both the cases.

I have used the Row Count Transformation to capture the Rows and store it in the variable, and in case of Merge, i have used the @Action of Merge Statement.

But, when i want to go for SSIS Catalog Dashboards, i don't get such counts. So i'm looking for any work around to get those counts.

-Onkar

Hi Jamie,

Thanks, Now I got the point and understood the whole functionality of the [catalog].[execution_data_statistics] table.

So, for the Execute SQL, I need to write code in the SQL itself.

Regards,

Onkar Belgamwar

Hi Jamie,

First of all, thanks for a great blog!

I’ m currently trying to decide if I should stop using my old SSIS framework and rely entirely in the 2012 built in logging but I have one major issue. Hopefully it is there and I just don’t see it. Here it goes:

Today I’m using the PackageExecutionGUID to store information in all my tables about which package execution that inserted or modified the record. I can’t seem to find this in the new SSIS catalog. Using the ExecutionId is not really enough for me as I want to be able to distinguish between two executions of the same package within the same batch (ExecutionId).

Any ideas? Is there a way to map the PackageExecutionGUID (or maybe another variable available in SSIS) to a record in the [internal].[executable_statistics] table

Thanks

Sorry, I meant the System::ExecutionInstanceGUID variable in the post above.

And I guess I would need this to be able to create my own logging of affected records by T-SQL statements as well. How do I connect my custom logging to the [internal].[executable_statistics] table?

Regards

Thanks a lot for you quick answer. You are correct, that is exactly what I want. I will vote.

I wrote a new post before I saw your answer:

The information in the internal.event_messages table is great but my problem is really that I don’t seem to have enough information available in the package system variables in order to connect any custom logging to the catalog logging.

Let’s say for example that I would like to create my own logging of affected records by T-sql statements. I have a package that makes an insert and an update. The insert uses a regular OLEDB connection and the row count is nicely logged in the [internal].[execution_data_statistics] table. The update is performed using an Execute sql task.

The package is executed twice within a batch (ExecutionId). The first run it inserts 10 records and updates 5. The second time it inserts 20 records and updates 7.

What do I store in my custom logging table in order to follow this information? If I only use the package name and the Execution Id I would only know that 12 records were updated by the package in during the batch, not by which execution (row in [internal].[execution_data_statistics])