THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Introducing sp_ssiscatalog (v1.0.0.0)

Regular readers of my blog may know that over the last year I have made available a suite of SQL Server Reporting Services (SSRS) reports that provide visualisations of the data in the SQL Server Integration Services (SSIS) 2012 Catalog. Those reports are available at http://ssisreportingpack.codeplex.com. As I have built these reports and used them myself on a real life project a couple of things have dawned on me:

  • As soon as your SSIS Catalog gets a significant amount of data in it the performance of the reports degrades rapidly. This is hampered by the fact that there are limitations as to the SQL statements that I can embed within a SSRS report.
  • SSIS professionals are data guys at heart and those types of people feel more comfortable in a query environment rather than having to go through the rigmarole of standing up a reporting server (well, I know I do anyway)

Hence I have decided to take a different tack with the reporting pack. Taking my lead from Adam Machanic’s sp_whoisactive and Brent Ozar’s sp_blitz I have produced sp_ssiscatalog, a stored procedure that makes it easy to get at the crucial data in the SSIS Catalog. I will spend the rest of this blog explaining exactly what sp_ssiscatalog does and how to use it but if you would rather just download the bits yourself and start to play you can download v1.0.0.0 from DB v1.0.0.0.

Usage Scenarios

Most Recent Execution

I find that the most frequent information that one needs to get from the SSIS Catalog is information pertaining to the most recent execution. Hence if you execute sp_ssiscatalog with no parameters, that is exactly what you will get.
EXEC [dbo].[sp_ssiscatalog]
sp_ssiscatalog_basic.png
This will return up to 5 resultsets:
  • EXECUTION - Summary information about the execution including status, start time & end time
  • EVENTS - All events that occurred during the execution
  • OnError,OnTaskFailed - All events where event_name is either OnError or OnTaskFailed
  • OnWarning - All events where event_name is OnWarning
  • EXECUTABLE_STATS - Duration and execution result of every executable in the execution
All 5 resultsets will be displayed if there is any data satisfying that resultset. In other words, if there are no (for example) OnWarning events then the OnWarning resultset will not be displayed.
The display of these 5 resultsets can be toggled respectively by these 5 optional parameters (all of which are of type BIT):
  • @exec_execution
  • @exec_events
  • @exec_errors
  • @exec_warnings
  • @exec_executable_stats

Any Execution

As just explained the default behaviour is to supply data for the most recent execution. If you wish to specify which execution the data should return data for simply supply the execution_id as a parameter:
EXEC [dbo].[sp_ssiscatalog] 6

All Executions

sp_ssiscatalog can also return information about all executions:
EXEC [dbo].[sp_ssiscatalog] @operation_type='execs'

sp_ssiscatalog_executions.png
The most recent execution will appear at the top.
sp_ssiscatalog provides a number of parameters that enable you to filter the resultset:
  • @execs_folder_name
  • @execs_project_name
  • @execs_package_name
  • @execs_executed_as_name
  • @execs_status_desc
Some typical usages might be:
//Return all failed executions
EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_status_desc='failed'

//Return all executions for a specified folder
EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_folder_name=
'My folder'

//Return all executions of a specified package in a specified project
EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_project_name='My project', @execs_package_name='Pkg.dtsx'

Installing sp_ssicatalog

Under the covers sp_ssiscatalog actually calls many other stored procedures and functions hence creating it on your server is not simply a case of running a CREATE PROCEDURE script. I maintain the code in an SQL Server Data Tools (SSDT) database project which means that you have two ways of obtaining it.

Download the source code

You can download the latest (at the time of writing) source code from http://ssisreportingpack.codeplex.com/SourceControl/changeset/view/70192.

image

Hit the download button to download all the source code in a zip file. The contents of that zip file will include an SSDT database project which you can open up in SSDT and publish just like any other SSDT database project. You can publish to a new database or any existing database, even [SSISDB] if you prefer.

Download a dacpac

Maintaining the code in an SSDT database project means that it can all get packaged up into a dacpac that you can then publish to your SQL Server. That dacpac is available in a zip file that is downloadable from DB v1.0.0.0:

image

The zip file actually contains two dacpacs:

image

SSISReportingPack.dacpac is the one that contains sp_ssiscatalog however SSISDB.dacpac represents all of the objects in the SSIS Catalog hence is a pre-requisite and has to be bundled along for the ride. Simply unzip these two files into a folder.

Ordinarily a dacpac can be deployed to a SQL Server from SSMS using the Deploy Dacpac wizard however in this case there is a limitation. Due to sp_ssiscatalog referring to objects in the SSIS Catalog (which it has to do of course) the dacpac contains a SqlCmd variable to store the name of the database that underpins the SSIS Catalog; unfortunately the Deploy Dacpac wizard in SSMS has a rather gaping limitation in that it cannot deploy dacpacs containing SqlCmd variables. Hence, we can use the command-line tool, sqlpackage.exe, instead. Don’t worry if reverting to the command-line sounds a little daunting, I assure you it is not. Simply open a command-prompt and cd to the folder containing the unzipped dacpacs:

SNAGHTML4323bf97

Type:

  • "%PROGRAMFILES(x86)%\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /action:Publish /TargetDatabaseName:SsisReportingPack /SourceFile:SSISReportingPack.dacpac /Variables:SSISDB=SSISDB /TargetServerName:(local)

or the shortened form:

  • "%PROGRAMFILES(x86)%\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /a:Publish /tdn:SsisReportingPack /sf:SSISReportingPack.dacpac /v:SSISDB=SSISDB /tsn:(local)

remembering to set your server name appropriately (here mine is set to “(local)” ).

If everything works successfully you will see this:

SNAGHTML4322c773

And you’re done! You’ll have a new database called [SsisReportingPack] which contains sp_ssiscatalog:

image

 

Good luck with sp_ssiscatalog. I have been using it extensively on my own projects recently and it has proved to be very useful indeed. Rest-assured however, I will be adding many new capabilities in the future.

Feedback is welcome.

@Jamiet

Published Wednesday, November 07, 2012 12:21 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Koen Verbeeck said:

This seems awesome. I'll try it out soon.

November 7, 2012 1:56 AM
 

mbourgon said:

Jamie, can I request some functionality?  We're trying to get SSIS 2012 jobs to email failure info, since the default sysjobhistory is, honestly, useless (we might be able to get it by changing the subevent type, then the logging mode, of the job step.  

So I currently have the below code that will get the package that was run for a particular job/step (getting the actual job name will be done via tokens; I left placeholder code to find the most recent)

DECLARE @full_ssis_command VARCHAR(4000)

, @job_id UNIQUEIDENTIFIER

, @job_step_id INT

--get most recent failed SSIS package

SELECT TOP 1 @job_id = job_id FROM msdb.dbo.sysjobhistory WHERE run_status <> 1 AND step_id > 0 AND message LIKE '%SQL Server Execute Package Utility%' ORDER BY instance_id DESC

--determine which job_step

SELECT TOP 1 @job_step_id = step_id FROM msdb.dbo.sysjobhistory WHERE run_status <> 1 AND step_id > 0 AND job_id = @job_id ORDER BY instance_id DESC

SELECT @full_ssis_command = command FROM msdb.dbo.sysjobsteps WHERE job_id = @job_id AND step_id = @job_step_id

IF @full_ssis_command LIKE '%.dtsx%'

BEGIN

SELECT RIGHT(LEFT(@full_ssis_command,CHARINDEX('.dtsx',@full_ssis_command)-1),CHARINDEX('\',REVERSE(LEFT(@full_ssis_command,CHARINDEX('.dtsx',@full_ssis_command)-1)))-1)+'.dtsx'

END

So, the next steps for me (and hence my request) would be:

* Having sp_ssiscatalog return only one result set, so that I can invoke it via sp_send_dbmail.  It looks like I can do that by invoking:

EXEC ssisreportingpack..sp_ssiscatalog

@execs_package_name = @package_name,

@execs_status_desc = 'failed',

@operation_type='execs'

* Getting the full error message.  I can query message from catalog.event_messages where event_name = 'OnError', and I'm using the underlying queries from the SSMS SSIS Dashboard to get a ton of info, but since yours is going to improve and mine will probably get to a happy point (for me) and stay there, I'd rather use yours.

November 8, 2012 11:03 AM
 

SSIS Junkie said:

Since the release of SQL Server 2012 I have become a big fan of using dacpacs for deploying SQL Server

November 13, 2012 4:53 PM
 

SSIS Junkie said:

13 days ago I wrote a blog post entitled Introducing sp_ssiscatalog (v1.0.0.0) in which I first made

November 20, 2012 4:42 PM
 

jamiet said:

mbourgon,

Nice idea, and I really appreciate you taking the time to submit it - thanks.

Let me clarify though - do you want to get an error message (if one exists) when querying for a list of executions using:

EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_status_desc='failed',@execs_package_name='Package.dtsx'

I'm not quite sure how that would work. If there are multiple executions returned, which one should it return an error message for? Are you suggesting (for example) that there should be an extra column that returns (say) the first error in the execution?

regards

Jamie

November 20, 2012 4:56 PM
 

SSIS Junkie said:

What is the best way to document an API ? Moreover, what is the best way to document a T-SQL API? Before

December 4, 2012 6:19 PM
 

mbourgon said:

Dangit!  Coworker just came across this post and heckled me - you asked a question about my request in November, and I never responded!

I did actually get something like my request working:

http://thebakingdba.blogspot.com/2012/11/sql-server-2012-ssis-getting-useful.html (and another version that uses SQL Sentry, so no extra job step)

Ideally, I'd call the sp_ssiscatalog as a "on failure" step, include the package name, and get back an email with the the messages from the failed execution set.  

Right now, (unless I'm doing it wrong), I can either get all 5 results sets for a package, or all the failure messages for a package - but I can't get the failure messages from just the last execution.  

Thanks

(and I've added this to my RSS feeds, so I'll actually respond!)

@mbourgon

June 11, 2013 2:23 PM
 

jamiet said:

hi mbourgon,

"but I can't get the failure messages from just the last execution."

I presume by "failure messages" you mean "events WHERE event_type='OnError""

Ok, so just let me just clarify something. Do you want to get the error messages for the most recent execution *without knowing the execution_id* of that execution?

Or, to put it another way, you want to say "I know my last execution failed. I don't know its execution_id and I don't want to have to go and dig around in order to discover it, simply show me the dang error messages!!!"

Am I getting close?

JT

June 16, 2013 3:48 PM
 

Mbourgon said:

Yeah, you pretty much nailed it.  For me as an ops guy, if I get woken by it at 2am, it needs to be very KISSable. And like I said, I've got my email job-step working, but obviously your code handles a bunch more variations than mine.

Thanks again!

June 17, 2013 7:44 PM
 

jamiet said:

Hi mbourgon,

I've just released a new version of sp_ssicatalog that (I hope) includes your feature request. Read more at https://ssisreportingpack.codeplex.com/releases/view/108938

Regards

Jamie

July 4, 2013 5:55 AM
 

SSIS Junkie said:

Development of sp_ssiscatalog continues apace with a new release today (4th July 2013). In this release

July 4, 2013 4:19 PM
 

jamiet said:

July 4, 2013 4:28 PM
 

Jamie Thomson said:

Since December 2010 I have been maintaining a suite of reports that can be used atop the SSIS Catalog

September 4, 2013 11:24 AM
 

Thom Ash said:

Downloaded and installed but when I run EXEC [dbo].[sp_ssiscatalog] @operation_type='execs' it returns an empty set.  What am I missing?

January 7, 2014 2:00 PM
 

jamiet said:

Hi Thom,

Don't want to sound facetious but you do have some executions, right? Is there any data in select * from catalog.executions?

January 7, 2014 3:16 PM
 

Thom Ash said:

I don't have catalog.executions in ssidb or ssrsreportingpack and I'm not familiar with that table in another database.

January 7, 2014 3:48 PM
 

jamiet said:

Hi Thom,

You're going to have to trust me on this one, catalog.executions is *definitely* in SSISDB :) (its a view, by the way).

JT

January 7, 2014 4:11 PM
 

Thom Ash said:

By chance did you mean internal.executions?  No entries in it.  The only schema in SSIDB is .Internal and the only schema in SSISReportingPack is .dbo

I don't yet see how tables are populated but do see one of my projects in Internal.Projects but I have over a dozen.

January 7, 2014 4:24 PM
 

Thom Ash said:

Oh.  Was looking for a table.  The view is there and no entries.  So how does the table get populated?  I ran multiple packages since installing.

January 7, 2014 4:30 PM
 

jamiet said:

Hi Thom,

You should always use catalog.executions rather than internal.executions, for two reasons:

-The SSIS team have promised they will strive to maintain backward compatibility for all objects in the catalog schema in future versions of SSIS. They have made no such promises about objects in the internal schema.

-The catalog schema implements a form of row-level security, ensuring that any consumer of the objects only sees data they he/she is allowed to see.

This is why sp_ssiscatalog only consumes from objects in the catalog schema.

My best guess as to why you see no data in there is that the data has been purged/cleaned up. Check the "Catalog Configuration" section of this article: http://technet.microsoft.com/en-us/library/hh479588.aspx for more info.

Regards

Jamie

January 7, 2014 5:36 PM
 

Thom Ash said:

Hi Jamie,

I'm relatively inexperienced with the nuances of SSIS but when I read the article you referenced it talked about a "Integration Services Catalog" node.  We don't have one and we're on SQL 2012.  In reading further it is implied packages and projects are deployed with SSIS but we use TFS/BIDS. (makes me wonder why any project shows up) Article http://technet.microsoft.com/en-us/library/gg471509.aspx talks about 'Enable CLR Integration' and catalog.startup to automatically log on restart.  I don't know what CLR integration is or how to enable.  Cutting to the chase, Is it possible some installation or setup is required prior to utilizing your package?  Is the method of deployment a factor?  (SSIS vs TFS/BIDS)

Sorry about all the questions but didn't find any article which explains.

January 7, 2014 8:58 PM
 

jamiet said:

Hi Thom,

The Integration Services Catalog node should show up in SSMS's Object Explorer pane. Obviously you need to be using the SQL Server 2012 version of SSMS.

"Is it possible some installation or setup is required prior to utilizing your package?"

You do have to create the SSIS Catalog. When you expand the "Integration Services Catalogs" node in SSMS you get the option to do that (it is very easy).

"Is the method of deployment a factor?  (SSIS vs TFS/BIDS)"

I don't really understand the comparison between SSIS & TFS/BIDS. SSIS is a product for moving data, TFS is a source control system, BIDS is the old name for the SSIS dev environment 9admittedly Microsoft do a errible job of naming their products).

To answer your question tho, no, the method of deployment does not affect the deployed package. There are a number of mechanisms to deploy packages (e.g. SSMS, PowerShell, command-line) but they all do the same thing under the covers.

Hope that helps.

JT

January 8, 2014 12:53 PM
 

Thom Ash said:

Hello Jamie,

I now have the missing node and a plausible explanation of why I'm not seeing data.  In SSIS 2012 you have a choice of using a project deployment model or package deployment model.  The default is project deployment model.  The first package I created, and the only one showing in SSIDB, was created using the project deployment model and all others were created using package deployment model.  Since that first deployment the sole package showing in SSIDB was converted to package deployment model and is no longer being tracked by SSIS.  It looks like in order to use your tools I will have to convert everything to project deployment model.  That would be a real headache since we control them with config files instead of the parameters.  I'll have to do some experimentation to see how big a deal it is.

Thanks,

Thom

January 8, 2014 1:38 PM
 

jamiet said:

Hi Thom,

Correct, my tools only work against the SSIS Catalog. In order to be using the SSIS Catalog then you need to be using the project deployment model.

Regards

JT

January 8, 2014 5:50 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement