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

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<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 781080 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 3/10/2013 1:49:57 PM
Access Restriction Public



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_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 Microsoft on 6/1/2016 at 7:47 PM
Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SSIS. The reasons for closing these bugs are following:
1.     The fix is risky to implement in the current version of the product
2.     Scenarios reported in the bug are not common enough

If the issue is a critical business interruption, please call CSS (Customer Support Services).

Thanks again for reporting the product issue and continued support in improving our product.
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.

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)