Search results matching tags 'sp_ssiscatalog' and 'ssis'

sp_ssiscatalog v1.0.3.0 available now [SSIS]

jamiet — Fri, 15 Mar 2013 22:19:07 GMT

I’ve just put out a new version of sp_ssiscatalog. The main change is requesting a list of executions using:

exec sp_ssiscatalog @operation_type='execs'

returns a new field called relative_duration:

This length of the “bar” in that field represents the duration of that execution relative to all the other executions in the resultset. Put more simply, its an attempt to simply visualise which executions took the longest to complete.

The other change in this release is the addition of a new parameter called @ot which is simply a short form of @operation_type. Its there because I basically got fed up of typing @operation_type every time I wanted a list of executions. hence the statement above can now be written as:

exec sp_ssiscatalog @ot='execs'

That’s it, you can download the latest bits from http://ssisreportingpack.codeplex.com/releases/view/103452. Installation instructions are in my previous blog post on sp_ssiscatalog sp_ssiscatalog v1.0.2.0 now available for download [SSIS].

Look for more updates coming soon, I’m hoping to pick up the pace of iterations on this.

@Jamiet

sp_ssiscatalog v1.0.2.0 now available for download [SSIS]

jamiet — Mon, 11 Mar 2013 22:51:20 GMT

v1.0.2.0 – what’s in it?

Things have been a bit quiet on the sp_ssiscatalog front since I last blogged about it three months ago in December 2012. Rest-assured development continues apace however and today I’m making available a minor update, v1.0.2.0 which is now available for download from Codeplex. For those that don’t know I describe sp_ssiscatalog as:

sp_ssiscatalog is a stored procedure that makes it easy to query for information that is strewn around the SSIS Catalog.

There aren’t too many functional changes in this release, it is more focused on making sp_ssiscatalog easier to use. Back in Documenting sp_ssiscatalog I explained how I was adding documentation to the messages tab of SSMS. Hence as of this new release when you execute sp_ssiscatalog you will see information such as this:

which I think should be very useful for anyone that wants to use sp_ssiscatalog to its fullest. Even I who wrote the thing and has been using it day-in, day-out for quite some time now can’t remember the names of all the parameters – now I no longer have to!

Note that you can turn off the display of the documentation using the @show_docs parameter:

exec sp_ssiscatalog @show_docs=0

If you want to display only the documentation and not actually have sp_ssiscatalog do any querying of the SSIS Catalog its @show_docs_only:

exec sp_ssiscatalog @show_docs_only=1

If you have any suggestions for future enhancements please put them in the comments below or submit them to the discussions page on the Codeplex site.

As a reminder, here is the sort of thing you can do with sp_ssiscatalog:

--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'


--Return information about the most recent execution
EXEC [dbo].[sp_ssiscatalog]

One last thing, if sp_ssiscatalog is useful to you and you’d like to support future development feel free to donate to my personal beer fund at http://www.paypal.com/cgi-bin/webscr?cmd=_xclick&business=jamie@jamie-thomson.net&item_name=Supporting%20sp_ssiscatalog.

Installation Instructions

Download the zip file at DB v1.0.2.0. It contains two files, SsisReportingPack.dacpac & SSISDB.dacpac
Unzip to a folder of your choosing
Open a command prompt and change to the directory into which you unzipped the files
Execute:
- "%PROGRAMFILES(x86)%\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /a:Publish /tdn:SsisReportingPack /sf:SSISReportingPack.dacpac /v:SSISDB=SSISDB /tsn:(local)
  (/tsn specifies the target server, change as appropriate. /tdn specifies the database name, you can call it whatever you like.)

If everything works OK you’ll see something like the following:

This will (if it doesn’t already exist) create a database called [SsisReportingPack] (or whatever you chose to call it) which contains [dbo].[sp_ssiscatalog].

sp_ssiscatalog v1.0.1.0 now available for download

jamiet — Tue, 20 Nov 2012 21:42:09 GMT

13 days ago I wrote a blog post entitled Introducing sp_ssiscatalog (v1.0.0.0) in which I first made mention of sp_ssiscatalog, an open source stored procedure intended to make it easy to query the SSIS Catalog. I have been working on some enhancements since then and hence v1.0.1.0 is now available for download from Codeplex.

What’s new in this release

This release includes the following enhancements:

[execution_id] now gets returned in a call to

EXEC [dbo].[sp_ssiscatalog] @operation_type='exec';
Filter events by specifying packages to ignore
EXEC [dbo].[sp_ssiscatalog] @operation_type='exec',@exec_events_packagesexcluded='SomePackage.dtsx,AnotherPackage.dtsx';
[event_message_id] is now returned in a list of events
List of executions can now be filtered via a minimum and maximum execution_id

EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_minimum_execution_id=198,@execs_maximum_execution_id=201
Events resultsets now have a field, [event_message_context_xml] that contains an XML document containing all [event_message_context] info (if any exists)

Installation instructions

Download the zip file at DB v1.0.1.0. It contains two files, SsisReportingPack.dacpac & SSISDB.dacpac
Unzip to a folder of your choosing
Open a command prompt and change to the directory into which you unzipped the files
Execute:

"%PROGRAMFILES(x86)%\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /a:Publish /tdn:SsisReportingPack /sf:SSISReportingPack.dacpac /v:SSISDB=SSISDB /tsn:(local)
(/tsn specifies the target server. Change as appropriate.)

If everything works OK you’ll see something like the following:

or

depending on whether the target database already exists or not

This will create a database called [SsisReportingPack] which contains [dbo].[sp_ssiscatalog]

Feedback is welcomed!

@Jamiet

Introducing sp_ssiscatalog (v1.0.0.0)

jamiet — Tue, 06 Nov 2012 23:21:00 GMT

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]

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'

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.

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:

The zip file actually contains two dacpacs:

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:

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:

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

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