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 7
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 JoDouglass on 5/23/2017 at 9:01 AM
As plenty of other people have already noted, this is a really frustrating missing piece of functionality. While there are a couple of workarounds, they result in multiple execution IDs which isn't great when you're wanting to report from SSISDB - I want everything run from a package to be shown as part of that same execution. It seems bizarre that SSIS still hasn't really been updated to fully support catalogue deployments by this point; it feels like there have been a lot of missed opportunities, with great functionality being added but then left incomplete, greatly detracting from the value of that functionality. 90% complete does not necessarily mean we all get 90% of the potential value out of the product. This is a good example, where the workarounds result in far more time on development, and don't quite provide the full solution anyway. I seriously hope this is going to get looked at, despite the two previous responses.
Posted by Microsoft on 8/5/2016 at 6:19 AM
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
3.     A viable workaround is available: You can use SQL Task to call the the other SSISDB to execute through SPs.

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 FergusMcGee on 3/1/2016 at 6:28 AM
" we have found regretfully that it does not meet the bar to be included in an upcoming product release"

Why not? What is the 'bar'?
Posted by Perry Meister on 2/24/2016 at 5:21 AM
Disappointed that after so many votes, this is still not on schedule to be changed. It is really limiting the product to have this restriction, the workarounds are not that acceptable (almost a bit like 'hacking' around the issue).

OK, you can still use package deployment, >nothing< legacy about that! But seriously microsoft, quite a lot of people use SSIS in large scale projects where multiple developers are really busy in all parts of the solution. We need to cross reference accross projects AND deploy only what is changed, not just the whole project.
Posted by Hennie7863 on 2/22/2016 at 5:17 AM
I have two SSIS projects: one for export of data into XLS (I have to create a separate project to run in 32bits) and one another project to run in 64 bits. So I want one master package in which I fill the DWH in 64 bits modus and I want to run an export to XLS. So this is not possible now.
Posted by SmileyMahoney 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 Microsoft 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 (