[SSIS] Automatically Provide a ConnectionString to the SSIS Catalog - by Jamie Thomson

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 725932 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 2/21/2012 9:33:04 AM
Access Restriction Public


Many people have built their own logging frameworks for prior versions of SSIS and I anticipate that many will want to integrate those solutions with the new SSIS Catalog in SSIS 2012.
Typically this will be done by creating new tables and stored procedures in the [SSISDB] database however this presents a problem. The package execution process knows which server the [SSISDB] database resides on (thus enabling it to log to it) but the packages themselves do not.

Similarly package developers may wish to interrogate [SSISDB] to discover things about their in-flight executions (e.g. When did the execution begin?)

Typically then developers are going to have to pass in the name of the server hosting [SSISDB] when a package is executed. This is possible using @@SREVERNAME but its its still a bit unwieldy - it would be better if the location of [SSISDB] were available to every package that gets executed against that catalog.

I understand that you'll need to wait until SSIS2012 hits the streets and you get real feedback from customers before deciding whether a solution here is necessary or not - so this Connect submission is really just to make you aware that this situation is going to crop up (please don't use that as an excuse to close this Connect submission though).
Sign in to post a comment.
Posted by Jamie Thomson on 3/13/2012 at 2:12 AM
Thanks for the reply.

Your advice makes sense but it is totally at odds with advice that I received form SSIS product team members during the CTP phase. Can I suggest that you publish some guidance around this topic? Answer the question "How does one extend [SSISDB]?"

Posted by Microsoft on 3/12/2012 at 11:12 PM
While SSISDB is a user database, we advise against extending SSISDB with custom structures and logic. The risks of impacting the operational behavior of SSISDB (negatively) is high with custom structure and logic. It would be difficult to provide a good and consistent user experience by allowing extendabilty of this kind. We understand extendability is important, but we need to ensure extendability is done with the proper design built with extendability in mind without impacting SSIS operational behaviors.    
Posted by Phil Brammer on 2/27/2012 at 11:10 AM
I voted up on this suggestion, but I'm also a bit torn on whether Microsoft should make it real easy for developers to add potentially bad logic to the SSISDB database. That is, if they do their own custom logging, they could fill up the SSISDB causing a production outage. Should SSISDB be kept clean except for Microsoft's own objects? Just a question. I see both sides of this discussion, and honestly I will likely be extending SSISDB with my own objects anyhow.
Posted by iain27 on 2/21/2012 at 6:29 PM
I completely agree - we are finishing up a logging framework now