[SSIS] We're crying out for composite projects - by Jamie Thomson

Status : 

 


17
0
Sign in
to vote
ID 781080 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 3/10/2013 1:49:57 PM
Access Restriction Public

Description

Hi,

I’m currently working on a system that incorporates multiple SSDT database projects and an SSIS2012 project. There are multiple clients that use this system and each one of those clients gets their own instance of the system, complete with dedicated hardware per client, etc... In other words, if we have X clients then the system exists X times on X different sets of hardware infrastructure. I believe the correct terminology here is that we have a multi-tenant system.

 

The problem we have with this setup is that each tenant has some subtle differences that require us to maintain different pieces of code for each tenant. For example, one tenant may want to report at level3 of the customer hierarchy while another tenant wants to report at level2 - writing the stored procedure to satisfy both requirements is difficult and quickly became unworkable so we have a situation where each tenant will have their own version of the stored procedure and we need to make sure we deploy the requisite version for each tenant.

 

Luckily SSDT database projects support this scenario very well because they have the notion of “composite projects” - that’s where all of the DDL for a single database (let’s call it [reportDB] ) can be split over multiple projects. We have a set of core code (that is used by all tenants) in one database project and then client-specific code in a another project - one per tenant. We end up with a few database projects:
-reportDB_core
-reportDB_tenantA
-reportDB_tenantB
-reportDB_tenantC


reportDB_tenantA/reportDB_tenantB/reportDB_tenantC all have a reference to reportDB_Core so that when any of those three projects gets deployed, the core code will get deployed along with it (this is a really great feature of SSDT database projects by the way which I have talked about more fully here: http://sqlblog.com/blogs/jamie_thomson/archive/2013/03/10/deployment-of-client-specific-database-code-using-ssdt.aspx).

Unfortunately we have much bigger problems with the SSIS portion of the system because SSIS has no such similar support i.e. there is no notion of “composite SSIS projects”.
Sign in to post a comment.
Posted by Jamie Thomson on 6/25/2013 at 1:46 AM
OK thanks Walter, that's positive I guess. I'm coming across this requirement more and more as the number of SSIS2012 projects ramps up so I really do think it should be a high priority.

regards
Jamie
Posted by Microsoft on 5/23/2013 at 1:08 PM
Hello Jamie. We’ve looked into this item. While it will not be addressed in the current release cycle, we have added it to a list for consideration for the next. Thank you for the feedback. We will keep track of the suggestion. -Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)