Search results matching tags 'SQL Server' and 'SSIS Reporting Pack'

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].

SSIS Reporting Pack – a performance tip

jamiet — Mon, 05 Nov 2012 22:36:41 GMT

SSIS Reporting Pack is a suite of open source SQL Server Reporting Services (SSRS) reports that provide additional insight into the SQL Server Integration Services (SSIS) 2012 Catalog. You can read more about SSIS Reporting Pack here on my blog or had over to the home page for the project at http://ssisreportingpack.codeplex.com/.

After having used SSRS Reporting Pack on a real project for a few months now I have come to realise that if you have any sizeable data volumes in [SSISDB] then the reports in SSIS Reporting Pack will suffer from chronic performance problems – I have seen the “execution” report take upwards of 30minutes to return data. To combat this I highly recommend that you create an index on the [SSISDB].[internal].[event_messages].[operation_id] & [SSISDB].[internal].[operation_messages].[operation_id] fields. Phil Brammer has experienced similar problems himself and has since made it easy for the rest of us by preparing some scripts to create the indexes that he recommends and he has shared those scripts via his blog at http://www.ssistalk.com/SSIS_2012_Missing_Indexes.zip. If you are using SSIS Reporting Pack, or even if you are simply querying [SSISDB], I highly recommend that you download Phil’s scripts and test them out on your own SSIS Catalog(s).

Those indexes will not solve all problems but they will make some of your reports run quicker. I am working on some further enhancements that should further improve the performance of the reports. Watch this space.

@Jamiet

SSIS Reporting Pack v0.2 now available

jamiet — Sun, 04 Sep 2011 13:57:00 GMT

In December 2010 I announced in a blog post Introducing SSIS Reporting Pack for SQL Server code-named Denali that I planned to provide a series of reports that visualised information held in the SSIS Catalog in SQL Server Denali – I called these reports the SSIS Reporting Pack. At that time SSIS Reporting Pack was a precursor to what I really wanted to provide because the reports were built upon the SSIS Catalog from Denali CTP1; Denali CTP3 brought with it a whole heap of changes to the SSIS Catalog (read more at SSIS Logging in Denali) and accordingly SSIS Reporting Pack has been enhanced dramatically.

Download and Install

You can always download the latest release of SSIS Reporting Pack from http://ssisreportingpack.codeplex.com/releases/. In the release that I am announcing today (v0.2) the reports are delivered in an msi installer rather than simply a zip file as they were before, use the installer to easily make the reports available in whatever location you require:

After installation you will have a folder containing all the report project artefacts.

Open SSISReportingPack.rptproj in Visual Studio just as you would any other Reporting Services project from where you can deploy the reports to your Denali report server of choice. You will need to edit the data source (SSISCatalog.rds) to point to your SSIS Catalog (i.e. the [SSISDB] database). If you do not have your own instance of the SSIS Catalog database ([SSISDB]) but still wish to evaluate SSIS Reporting Pack I have made a copy of [SSISDB] available on SQL Azure that can be accessed using the following credentials:

Server		Username		Password	Database
enf3k2ao1m.database.windows.net		ro		r3@d0nly	SSISDB

The Reports

As I said above the reports have been changed in order to leverage the new additions to the SSIS Catalog in CTP3. The main enhancement I want to draw your attention to is the rich navigation of log data that is now provided

Notice that the log messages are now contextual, each message is shown against the executable that raised the message which in turn is indented indicating its position in the executable hierarchy. I expect this to be of great benefit when navigating package execution logs which, prior to Denali, was very difficult due to it essentially being a long flat list of messages.

SSIS Reporting Pack also has the ability to visualise the duration of each excutable:

In the simple example highlighted here by the red box we can see that iteration 3 of the For Each Loop “FEL Loop over top performing regions” took considerably longer than the two previous iterations; SSIS Reporting Pack gives us the tools we need to drill in and find out exactly where and why that slow down occurred.

Those are two of the main new features of SSIS Reporting Pack but there is lots more to discover so if you are evaluating SSIS in Denali I encourage you to download SSIS Reporting Pack and use it to learn about the plethora of data that is available in the SSIS Catalog. Please do bear in mind that this is still an early release so there are still lots of improvements to be made in the future; you can suggest improvements or report bugs either in the comments section below or (preferably) on the Discussions page at http://ssisreportingpack.codeplex.com/discussions.

@jamiet

Introducing SSIS Reporting Pack for SQL Server code-named Denali

jamiet — Wed, 08 Dec 2010 23:25:00 GMT

In recent blog posts I have introduced the new SSIS Catalog that is forthcoming in SQL Server Code-named Denali:

The SSIS Catalog is responsible for executing SSIS packages and also for capturing the metadata from those executions. However, at the time of writing there is no mechanism provided to view analyse and drill into that metadata and that is the reason that I am, in this blog post, introducing a suite of SSIS Catalog reports called the SSIS Reporting Pack ~~which you can download from my SkyDrive at http://bit.ly/ez55g4.~~ The latest version of the reports can be gotten from http://ssisreportingpack.codeplex.com/releases/.

In this first release the SSIS Reporting Pack includes five reports:

Catalog – A high-level summary of all activity in the Catalog
Folders – A summary of activity in each Catalog Folder
Folder – Project-level activity per single Folder
Executions – A visualisation of all executions per Folder/Project/Package/Environment or subset thereof
Execution – Information about an individual execution

Here is a screenshot of the Executions report:

Notice that the SSIS Reporting Pack provides a visual overview of all executions in the Catalog. Each execution is represented as a bar on the bar chart, the success or otherwise of each execution is indicated by the colour of the bar and the execution time is indicated by the bar height.

I have recorded a video that gives an overview of the SSIS Reporting which I have embedded below. If you are having any trouble viewing the video go see it at http://vimeo.com/17617974

I must stress that this is a very early version of the SSIS Reporting Pack and I am expecting it to change a lot over the coming year. I am very keen to get some feedback about this, specifically:

let me know if anything does not work as you expect
give me your feature requests

The easiest way to get hold of of me for now is within the comments section of this blog post.

That’s all for now. I hope the SSIS Reporting Pack proves useful and I look forward to hearing your feedback. Lastly, that download link again: ~~http://bit.ly/ez55g4~~. http://ssisreportingpack.codeplex.com/releases/.

@jamiet