SSIS 2012 Execute Package task: External Reference does not work with SSISDB - by Marcel Franke

Status : 


Sign in
to vote
ID 779789 Comments
Status Active Workarounds
Type Suggestion Repros 4
Opened 2/22/2013 4:21:43 AM
Access Restriction Public


There are currently two options within the Execute Package Task to execute child packages: Project Reference and External Reference. The problem with project reference is, that all packages need to be within the same SSIS projects. This is an issue for large projects, because it also affects the deployment. But when I switch to External Reference combined with SQL Server, I can not see the SSIS packages which are deployed in the new SSIS db, when I choose the SSIS DB as source.
Sign in to post a comment.
Posted by Jennifer Mahoney on 12/29/2015 at 12:26 PM
We're just starting to setup etl, and the need to script a workaround because the 'new' ssisdb isn't supported for executing externally referenced packages is annoying. Just when I start thinking to recommend Microsoft products for work like this, that 'last 2%' of the product doesn't work where expected. Ditto to Peter K's comments.
Posted by Peter K on 6/18/2015 at 3:40 AM
+1 to this. I am baffled by the Microsoft's statement: "we have found regretfully that it does not meet the bar to be included in an upcoming product release". The current way of organizing SSIS artifacts is SEVERELY lacking. You cannot even create folders to separate packages logically let alone physically in separate projects. As stated numerous times in this thread this is a major issue for any SSIS projects beyond the trivial to the point where things become unmanageable. Have you tried to open a SSIS project of even moderate size (30-40 packages) in visual studio? One thing is the sheer time it takes, another is the utter chaos of having a flat list of SSIS packages and trying to make sense of the structure of the solution.

This makes me wonder what kind of issues DO meet the requirements to be included in future releases? Only product breaking bugs?
Posted by Daryl_Davis on 12/4/2014 at 5:39 AM
Many times, dependent job streams are created from multiple packages, stored separately in different projects due to complexity, concurrent development/maintenance needs, organizational constraints, or other reasons. It is unreasonable to assume that enterprises can dump all packages into a single project. The workarounds available add unneeded complexity. Lack of this ability hinders or removes the ability to upgrade SQL Server versions (without massive investments in developer time to rewrite processes). Please add the ability to execute external child packages.
Posted by Charlie [MSFT] on 9/24/2014 at 7:01 AM
Thank you for contacting us. After analyzing this issue and prioritizing it in the context of our current development plans, we have found regretfully that it does not meet the bar to be included in an upcoming product release. If you feel that you could provide us with more data to change that judgment, we welcome any further information. Your feedback continues to be invaluable to us and we take all your communication seriously. Please continue to contact us with any questions, suggestions and issues as they arise.
Posted by kiwiNspain on 9/9/2014 at 8:49 AM
any update on this? seems ridiculous that you can't execute child packages outside of the project
Posted by SSISJoost on 6/20/2014 at 4:35 AM
This is a limitation! You often have a master project that executes packages from other projects (Staging, DWH, Datamart, etc). Like BI Monkey mentioned, it's not an option to have all these packages in one project. The only workaround I found was using a Script Task. It works, but a real solution would be much better.
Posted by BI Monkey on 5/29/2014 at 5:06 AM
This is as (other commentators note) an extreme limitation on the ability to use SSIS in a complex enterprise environment. You cannot have a central command and control system for hundreds (or thousands) of packages by having it all in one monstrous project.
Posted by DavidS34 on 3/17/2014 at 7:22 AM
I would have thought that Project Deployment was supposed to be the way for the future of SSIS. Seems like a rather questionable feature to advertise if it doesn't work with standard design patterns.
Posted by Imbastuff on 8/21/2013 at 10:21 PM
Can only agree.

It is very cumbersome, not to mention confusing, to need to create a Execute SQL-task to trigger a sp to run a package in SSISDB ( when you'd like to execute package.
Posted by dline292 on 8/1/2013 at 10:11 PM
Now that the DTS functionality is back for passing parameters to child packages, it allows for easy building of SSIS frameworks with reusable child packages. But with the all or none deployment model, it is challenging to manage large projects with multiple developers, especially since the slightest change to any package in the project updates the project file. Because of this, it's easier to break up large project into smaller projects grouped by related packages. However, with this identified limitation, there is no way to reference the reusable child packages between projects.

SSIS 2012 made a huge leap forward towards making SSIS a serious contender in the ETL space, but this is a major limitation. Every effective developer utilizes centralized and reusable code, and the lack of this feature prohibits it. Please add this.
Posted by Olaf Krumnow on 6/5/2013 at 2:05 AM
Is there a workaround available? How can I start a task deployed to a server from another SSIS task?
Posted by Microsoft on 5/23/2013 at 1:06 PM
Hello Marcel. 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 (