Home Dashboard Directory Help
Search

SSIS: Execute Package Task should support parameters by Jamie Thomson


Status: 

Closed
 as Fixed Help for as Fixed


93
1
Sign in
to vote
Type: Suggestion
ID: 295885
Opened: 8/30/2007 12:02:20 PM
Access Restriction: Public
1
Workaround(s)
view

Description

dtexec.exe allows us to pass values into a package using /SET and a property path. Why can't we do the same using the Execute Package Task?

I raised this on the forum here: http://forums.microsoft.com/MSDN/showpost.aspx?postid=2077788&siteid=1 and had a really good discussion that involved Cho Yeung.

The overriding argument for this seemed to be that a package that is intended to be called from another package can become a reusable "thing" without having to know anything about the package that is calling it. That is NOT the case when using Parent Package Configurations.

I urge you to read the discussion at the link above and also this similar submission: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=294904
Details
Sign in to post a comment.
Posted by Microsoft on 8/31/2010 at 12:02 PM
We have introduced parameters in upcoming release, and parent package will be able to use parent parameters to set the child package parameter values. Child package will also be able to run on its own. Hopefully this will address most of the issues described in this bug. We appreciate your feedback.
Posted by G_K08 on 5/14/2010 at 5:24 AM
I agree !!! Don't understand why this wasn't done originally. Seems like Common Sense :)
Posted by hardwaremister on 11/5/2009 at 1:47 PM
Oh, that would be SO useful! I would love to do recursion on a package level for load paralelization!

It would make things a LOT easier for dirty restricted procedural data loads, where data is normally provided by a third party as a procedure. This is the kind of lack of flexibility and speed you can see in most organizations, where the ownership of some business process/data is a "secret" and you are only allowed to extract it though a procedure.

I have found that a way of overcoming the incapability of doing set-based operations for extraction, is just paralelization while the source has resources. Normally the procedures tend to be very processor intensive but actually use very little of the disk resources of the machine. That allows almost lineal speedups per compute core... but implementing it into SSIS is not that straight forward.

The way I have figured to work this out is rudimentary but it work, and it's efficient! However it requires several levels of container nesting, and SSIS does not play nicely, failing to draw the constraints...

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/4102b54a-1fe2-43ef-8fb5-8b77db1298de

To make it worse, the solution of the problem has been delayed for the next major release,

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=377714


Bottom line... all this could be spared if you could choose what variables to pass to the children packages, as recursion could easily be achieved. That is not even thinking about how hard would it be to develop/maintain packages designed for high core count machines (32,64...).

Please, give priority to this Feature! As personal suggestions, I would add the following concept for the variable passing:
*Disallow all the default parent package variables
*Disallow the following package variables (list of exceptions)
*Allow passing an object between packages, ie: recorset (long shot, and probably won' be done)


Kind regards,


Francisco Isla.
Posted by RoyZ on 8/12/2009 at 9:55 AM
Agree, should work the same way as dtexec.
Posted by tokenmathematician on 12/16/2008 at 10:33 AM
in 100% agreement
Posted by Ron Draganowski on 9/18/2008 at 8:55 PM
MS dudes, my life would be so much easier if you would please support passing parameters for the Execute Package task. Make it thus!

Ron Draganowski
Consultant, Senior Solution Developer
Olsen Thielen Technologies, Inc.
St Paul, Minnesota
rdrag@ottechnologies.com
<a href="http://www.ottechnologies.com">http://www.ottechnologies.com</a>


Find me on LinkedIn: <a href="http://www.linkedin.com/in/rondraganowski">http://www.linkedin.com/in/rondraganowski</a>
Posted by MatthewRoche on 8/29/2008 at 3:42 PM
Jamie Thompson has posted a delightful blog entry that is on-topic for this Connect item:

http://blogs.conchango.com/jamiethomson/archive/2008/08/29/ssis-parent-package-configurations-yay-or-nay.aspx

Any MSFT personnel who review this item should also take the time to read this blog post and the comments that will invariably be posted to it.
Posted by Jamie Thomson on 8/21/2008 at 7:54 AM
More on this, and something that makes it even more important.

Values that are passed into a package using a Parent Package configuration are evaluated later than those that are passed in using /SET.

This is currently a problem for me because I am dynamically building the location of my log file based on a value that is passed in by a parent package configuration. The trouble is that logging begins BEFORE the parent package configuration is applied and hence I get 2 log files, one prior to the parent package config being applied and one after.

PLEASE PLESE PLEASE FIX THIS!!!
Posted by Microsoft on 4/25/2008 at 2:21 PM
Jamie, sorry we cannot get to for Katmai. We are moving it forward and will be asking for your assistance in determining the overall priority of this and other issues.

Thanks

Darvey


Posted by MatthewRoche on 1/7/2008 at 12:16 PM
It would be difficult for me to agree any more with this suggestion. The lack of reuse in SSIS is a major barrier to productivity and usability, and is likely a major barrier to product adoption as well.

Another potential way to implement the benefits requested in this suggestion would be to allow the /SET and /CONF DTEXEC options (and possibly others as well) to be "inherited" or "passed through" to child packages as well. I personally think that the implementation mechanism is less important than the functionality.
Posted by C Dionne on 12/6/2007 at 1:36 PM
I agree, the execute package control should expose all parameters supported by DTExec. Of the parameters most import is the the connection to be used by the logger for the called package. Currently the logger of the called package uses the default values of the connection it was saved with. The logger connection is being validated before the connection manager expressions are evaluated. This is not useful when the package is being promoted from a development to QA then to production environments. Now we have packages running on our production server that are dependent upon the existence of a database on the development server. Currently the only work around I see, is to stop using the Execute Package control and revert to DTExec - so then what use is the Execute Package control?

This issue can be replicated by creating a chilid package with a logging configured to a sql server database connection manager that has an expression to set the InitialCatalog property. Before saving the package, change the variable used in the expression to evaluate to a "bad" database name. Then execute the child package with an "execute package" task control in another package. Use a set value to override the variable. You'll find the package will not start because the logger cannot connect to the "bad" database .
Posted by jwelch on 8/30/2007 at 5:42 PM
This would be a great way to resolve the "initial connection" problem with using SQL configurations. I use SQL configs heavily, and if I am using a single package, I can just set the initial connection string to the database with the config via the commandline. However, if the first package calls others, there is no way to pass the initial connect string, since parent package variables evaluate later than other configurations.
Posted by Yitzhak Khabinsky on 8/30/2007 at 2:03 PM
The best case scenario is that the child package should be loosely coupled with its parent package. The parent package should be able to execute child package passing child's configuration file location through Execute Package task properties and expressions (simulating /CONFIGFILE, /SET, etc. flags of dtexec.exe) At the same time, the same child package should be able to run on its own, without any parent package calling it, just by picking up its configuration file.
Posted by Eric Wisdahl on 8/30/2007 at 12:26 PM
I believe that this would be a useful suggestion. In addition to making child packages more modular, it might mitigate some of the problems that crop up occassionally. For instance, I am having trouble getting variables which are used to create the connection string passed from parent to child (using the /set option on the parent and parent-child package configuration on the children ... see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2045623&SiteID=1)
Posted by Phil Brammer on 8/30/2007 at 12:09 PM
I, too, agree with this. It is important to start moving away from the non-reusable trend that SSIS instantiated with this initial release. Being able to build a package that isn't tied to a parent (in any way, shape, or form) is a must when trying to build something that is reusable. A point and click interface would be even better! ;)
Sign in to post a workaround.
Posted by AlexCooke on 8/25/2008 at 5:24 PM
You can work around this issue by executing child packages using the execute process task via dtexec.exe and building the arguments expression dynamically to pass configuration files and other set commands to the child package. A fully functional and properly decoupled execute package task is preferable, of course, but it is a workaround in the meantime.