Include as part of SSISDB rather than seperate database

Aug 2, 2013 at 6:38 PM
Hi Jamie, so I am considering creating some helper tools for Ssis 2012 (create environment from design variables, copy environment etc) and would like to add your tools alongside.

I was considering adding them into the ssisdb database directly in a well-named schema that would avoid namespace clashes in future. was there a strong reason why you didn't do this for ssis reporting pack?

Creating multiple databases for a single system is something that I try to avoid generally for a number of reasons having experienced both methods. My preference is now to use ssdt composite projects wherever sensible and this keeps everything in one place with everything with 2 part object references.. everything is then simpler, security, backups, intuitiveness, deploying updates the list goes on.

Is there any reason why we can't install ssis reporting pack as a composite package to ssisdb and if not could you consider moving the objects into a "ssisreportingpack" schema or something to ensure name clash avoidance in future?

If you can do that then I and others could create an ssdt project that consumed your dacpac as a composite project and layer on my objects (in another separate schema) and life becomes a bit closer to perfect :)

Interested in your thoughts.
Brett Gerhardi
Coordinator
Aug 5, 2013 at 11:10 AM
Hi Brett,
I figured that some people might not want to create objects in [SSISDB] hence I gave them the option of creating it in another database. I expect (tho admit to having not tested this) that you can deploy into [SSISDB] and it will work fine. Have you experienced anything different?

As for creating everything in a named schema....everything already is in a named schema - called [sp_ssiscatalog]. The only object that isn't is the stored procedure [sp_ssiscatalog] itself which is actually in [dbo]. The reason for that is that [dbo] is generally the default schema hence people will simply be able to type [sp_ssiscatalog] and won't have to type the schema.

Regards
Jamie
Mar 13 at 11:00 AM
Hi Jamie,

Is there a reason why the "Restart Framework" stuff is all included in the SSISReportingPack dacpac? Nothing in usp_ssiscatalog appears to depend on it, unless I'm missing something obvious.

Gavin
Coordinator
Mar 13 at 12:09 PM
Ah, you found me out Gav. The RestartFramework is something else I'm putting in there and do intend to make it more widely known sometime soon. Unfortunately I haven't got round to it yet and I've actually had that intention for many months now. You're right that nothing in usp_ssiscatalog depends on it however my long term aim is that there will be some dependency in the future.
Mar 13 at 12:19 PM
Figured as much, I had noticed the RestartFramework stuff in your other codeplex project.

The reasons I replied to this thread rather than starting a new one are that firstly, since the invention of Twitter, I have forgotten about threaded discussion works, and secondly, that I was thinking about doing what the OP mentioned, namely deploying the proc and supporting objects into the SSIS Catalog database.

If you could add a dependency on Sharepoint into the mix, then SSIS reporting pack will be truly "enterprise-ready".
Coordinator
Mar 13 at 12:21 PM
Put that cynicism away :)