Helpful information and examples on how to use SQL Server Integration Services.
After a short hiatus, the 30 Days of SSIS series is back! Today’s special guest post is from Jamie Thomson.
As you may already have gleaned from earlier posts in the 30daysofSSIS series SSIS in Denali includes the new SSIS Catalog that is used for execution and administration of SSIS projects and their associated paraphernalia. An important new feature provided by the SSIS Catalog is the automatic logging of all package execution activity along with some built-in reports that provide a view over that log activity. One good example of such a report is the “All Executions” report that gives an overview of all executions of packages in a given folder:
All of the underlying data for this and other reports exists in tables in a database called [SSISDB] and this of course means that you also have the ability to build your own reports atop the same data. The volume of data that is available for reporting is determined by the LOGGING_LEVEL at which your packages are executed; the available LOGGING_LEVELs are:
One notable improvement from earlier versions of SSIS regarding LOGGING_LEVEL is that it is configured externally from a package at execution-time. This means that if you need to increase the LOGGING_LEVEL (perhaps for problem investigation) you can do so without having to make changes to your packages.
If you want to build your own reports then you will need to familiarize yourself with the following views, all of which exist in a schema called [catalog]:
*AcquireConnections and ReleaseConnections phases are not logged in Denali CTP3 however they will be added later.
Many of you will have existing logging frameworks that you have built for previous versions of SSIS and you will be happy to know that you are allowed to integrate those frameworks into [SSISDB] by creating your own tables without invalidating your SQL Server license. Moreover, in a later release of SQL Server Denali (its not available in CTP3 unfortunately) the [execution_id] that uniquely identifies each execution in [catalog].[executions] will be available in a system variable called @[System::ServerExecutionID].
You will be able to use this value in your own tables to link your custom logging framework to the built-in log tables in the SSIS Catalog.
You can build reports atop the SSIS Catalog using any reporting tool you wish though of course many will choose to use SQL Server Reporting Services (SSRS). In order to kickstart your report authoring I have provided a SSRS project template that provides some shared datasets that may prove useful:
You can download this template as a zip file from here, note that it will currently only work with Denali CTP3. The Shared Data Source “SSISDB.rds” currently points to (localhost) so simply change that to point to your [SSISDB] of choice and away you go.
Jamie Thomson http://sqlblog.com/blogs/jamie_thomson http://twitter.com/jamiet