Get Parameter Values from SSISDB Catalog: a bug?

catalog.get_parameter_values

Recently I have been working with a couple of SSIS projects on SQL Server 2012. During this time, I have managed to encounter an issue with SSISDB. This issue is quite trivial; however it can be important for querying/reporting deployed SSIS packages. In fact, I found this issue because I wanted to create a deployment script to configure Environment and SQL Job to run the SSIS project referencing the configured environment.

The issue is to do with SSISDB.catalog.get_parameter_values.

As documented on Books Online (http://msdn.microsoft.com/en-us/library/ff878039), to obtain all project parameters and the parameters from all packages, when executing catalog.get_parameter_values, the “package_name” input parameter should be set to NULL.

** Excerpt from BOL – catalog.get_parameter_values **

However when running the catalog.get_parameter_values stored procedure with @package_name set to NULL, it returns an error instead stating “The input parameter cannot be null”.

Msg 27138, Level 16, State 1, Procedure get_parameter_values, Line 18
The input parameter cannot be null. Provide a valid value for the parameter.

 

I have logged this as a bug at Microsoft Connect:

https://connect.microsoft.com/SQLServer/feedback/details/743735/ssisdb-catalog-get-parameter-values-with-null-able-package-name-input-parameter#details

 

My own version: catalog.get_any_parameter_values

As a temporary workaround, I have a modified version of catalog.get_parameter_values where it is possible to retrieve all project parameters and package parameters in a single stored procedure call.  I’m calling the new modified version, catalog.get_any_parameter_values which can be deployed to SSISDB and consumed in custom queries. Any built-in reports in SQL Server 2012 that may be calling catalog.get_parameter_values stored procedure will not be affected.

Please note that catalog.get_any_parameter_values is a draft version. Deploy and make use of it with care. This is intended to be as a supplement for administering Integration Services Catalog.

Example of usage:

USE [SSISDB]
GO
EXEC [catalog].[get_any_parameter_values]
    @folder_name = 'AdventureWorks BI',
    @project_name = 'AdventureWorks BI ETL',
    @package_name = NULL,
    @reference_id = NULL

 

The results will be similar to the following:

To deploy the script:

  1. Download catalog.get_any_parameter_values from here.
  2. Run the script against SSISDB database in a SQL Server 2012 Database Instance.  The catalog.get_any_parameter_values stored procedure will be created and will only work within the target Integration Services Catalog. If there are multiple servers running Integration Services Catalog, the script needs to be deployed against SSISDB database of each server.

Compatibility level: SQL Server 2012 RTM (11.0.2100)

If you have any feedback on my catalog.get_any_parameter_values stored procedure, please let me know. More importantly, please vote for the bug ticket relating to catalog.get_parameter_values at Microsoft Connect. https://connect.microsoft.com/SQLServer/feedback/details/743735/ssisdb-catalog-get-parameter-values-with-null-able-package-name-input-parameter#details

Other Workaround

An alternative workaround to the issue in catalog.get_parameter_values is to call the catalog.get_parameter_values once per project and per package in the project. I think this is a little inefficient but will do the job well with little risk on the SSISDB.

 

SQL Rally Dallas: My Experience

SQL Rally 2012 in Dallas officially started yesterday (May 10th, 2012). The old saying “time flies when you’re having fun” is so true! I made new friends, met old friends and learnt a lot more than I thought my “sleep deprived and jetlagged mind/body” could.

 

Being a somewhat  generalist within the SQL Server space, I am interested in DBA, DEV and BI sessions equally. So, I decided to attend the following sessions:

 

Craig Purnell on “Upgrade Roadmap: Let’s Delve into SQL Server 2012“ 

     A superb session on what’s in store when upgrading SQL Server, the techniques and what to watch out for.

William E Pearson III on “Overcoming Barriers and Avoiding Mistakes with BI“ 

     Bill delivered this session with lots of audience interaction with the aim of group experience/knowledge sharing.

Patrick LeBlanc on “Developing and Managing a BI Semantic Tabular Model in SQL Server 2012 Analysis Services“ 

     A fundamental session for building Tabular Model solutions in SQL Server 2012.

Bob Ward on “What’s New for SQL Server 2012 Supportability“ 

     Another fantastic session for full-on DBAs!

Greg Galloway on “Making Heads or Tails of Analysis Services Storage“ 

     Greg shared his wisdom and knowledge of how MOLAP and ROLAP differ from the new Tabular Model in SQL Server 2012. A must session for those who are deciding whether to implement using Multidimensional or Tabular Model.

John Welch on “SSIS Performance Tuning“ 

     SSIS could sometimes be a real beast, and John showed us how to tame slow performing SSIS solutions and what to watch out for.

Robert Davis on “TempDB: Performance and Manageability“ 

     This session answered most of those myths (or statements) of TempDB and Robert discussed important factors to consider for better performing TempDB.

 

They were all superb and I wish I could attend the other sessions as well. I asked prior to the conference if the sessions will be recorded and I haven’t received a confirmed answer yet. However, the slide decks will be up on SQL Rally website in due time.

So, all the way from Australia, I’d like to thank the sponsors, PASS, speakers and the volunteers for making SQL Rally possible and such a great event. PASS is about sharing and networking. Safe trip home for interstate and overseas attendees. Hope to see everyone again in PASS Summit 2012 and other SQL events!

One last thing: I love Dallas! You are all lovely and friendly people!!!!

[Edit - 20 May 2012]

SQLRally PPTs and demos are now available to all attendees for download – simply log in and click on a session title to access the materials. http://www.sqlpass.org/sqlrally/2012/dallas/

 

 

SQL Rally Dallas: Wednesday Pre-Con Review

It was a prefect weather to start Wednesday Pre-Con SQL Rally sessions today. I decided to atted Greg Galloway’s 99 Tips for Tuning and Enhancing Analysis Services session today. It was very informative. I highly recommend this session. Greg prepared the material very well and thoroughly, including providing hard copy hand outs for attendees and allowing attendees to copy the demo files. As most pre-con sessions are, Greg’s session definitely came from first-hand expert knowledge and experience as well as hours (or even more) of preparation.

Greg mentioned the followinng in his preview of the Pre-Con session:

Q. If there was one thing you wish attendees would implement after attending your workshop, what would it be? A. I wish people would take an hour to set up logging of Perfmon counters and trace information so you can see how performance is trending over time. I’ll do my best to convince attendees how useful this information can be and show what I personally choose to log.”

Yes, I will be turning on the Perfmon counters in my environment. I’ll be setting up a template and monitor this overtime; both for SSAS Tabular Model instance and Multidimensional instance.

Thank you Greg for the 99 tips! I’ll be referring to your slide deck on regular basis now.

Slide Deck: DirectQuery vs Vertipaq for PASS DW/BI VC

I presented at PASS Data Warehousing and Business intelligence Virtual Chapter on May 3rd, 2012 for the “DirectQuery vs Vertipaq mode in SSAS Tabular Model” session.

I have purposely prepared 30+ slides so that they could be used as a reference to get back to after the session. So here’s the slide deck:

DirectQuery vs Vertipaq modes in SSAS Tabular Model by Julie Koesmarno

If you have any feedback or comments, please don’t hesitate to let me know.

This presentation will be delivered at SQL Rally in Dallas on Friday, 11th May 2012. Come and join me!

SQL Rally Dallas 2012: Tuesday Pre-Con Review

I landed in Dallas on Monday 7th May 2012 afternoon. I was very excited to be in Dallas, not only it was my first visit, but also the fact that my brain would be like a sponge absorbing new information at SQL Rally.

Dallas Convention Center is huge! I was lucky that I didn’t get lost here. Luckily with the guidebook, I was able to find where I was meant to be for registration, i.e. Area A Lobby. (If all else fails, check your wifi, if you can find “PASS Wifi”, you’re close to it!). The registration was easy and smooth. Sri Sridharan greeted me when I registered for the precon sessions. The morning started very well indeed.

So I went to my first session of SQL Rally today which was a pre Con session with Denny Cherry, Storage for the DBA. I decided to come to this session with a motive of knowing or understanding how to talk to the Infrastructure team in terms of setting up storage / virtualisation in an optimised way.

Denny was great in his presentation. He knows a lot about storage, virtualisation, network, SQL Server (and many others) which makes him a good presenter for the session. Although my knowledge of storage limited, I was able to keep up with the speed. Sure, I still have quite a lot of questions, but now I know what questions to ask and what to bingle.  I would highly recommend this session to any DBAs, especially those who have their own Data Centers. If your company provision the storage / server / virtualisation to another third party such as Rackspace, etc, this session would be immensely useful because  you can ensure that you (or your company) are paying for the service that meet the business requirements. I am very certain that every attendee would have had a unique learning experience from one another. If you missed out this session, hopefully it will be featured in another upcoming session.

Now, my afternoon will be spent on spending time to recover from jetlag and enjoying Dallas.