none
Configuring child packages RRS feed

  • Question

  • Simple: I have a parent and a child package with a connection manager in a dynamic environment, meaning, I cannot use system variables, nor registry config, nor xml config (unless I can work with a relative path).

    The connection of my parent can be overwritten by dtexec, but how can I set the connectionmanager of my child package, as other configurations relates to that connectionmanager, I cannot use parent-child configuration which I did :-(.

    It would be nice if I could pass on the .dtsconfig file to my child packages.

     

    any help is appriciated.

    Thx, Bram

     

     

    Wednesday, August 17, 2011 2:25 PM

Answers

  • hi Reza,

    Thanks for your time, In fact the problem is solved by executing the childeren via DTExec, that way I overwrite the connectionmanager (CM) of the sql server that is used for my SQL Configuration.

    As I have one deployment for 3 environments and I cannot change any configuration except via dtexec because this is done via commandline, I could not use a static configuration like refering to a xml file or environment variable, or registry key,...

    First I was thinking of passing the configstring of my CM via parentchild, as I could set the con string of my parent via dtexec, from there on I could pass the string to my children.

    However, do not use parent-child configuration when the variable is used for other configurations (again I refer to the article).

    So my solutions is to use instead of execute package task, the execute process task and launch the children with dtexec which again I can overwrite the configuration :-),

    • Marked as answer by BramDe Friday, August 19, 2011 7:02 AM
    Thursday, August 18, 2011 12:53 PM
  • Hi, Bram. It sounds like you have a working solution to this, and so I wouldn't recommend changing it. However, there are a couple of other patterns you could consider, if you run into this scenario again:

     

    1. Pass all values with Parent Package Variable configurations. Instead of having yor child packages retrieve some values through SQL Server, have the parent package pass in all values to the child. That way, you work around the "other configurations can't rely on PPVs" limitation, and it gives you a single point to retrieve all configuration values. I find this works well when I can't count on having a central SQL Server to use.

     

    2. Use indirect configuratons with a user environment variable. In this case, you need to use a batch file to run your parent package. Before executing the package, yoiu can do a SET MyEnvVariable="my connection string" in the batch file. This lets all subsequent packages called in the same process use the value you set at the beginning of the batch file.


    John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com
    • Marked as answer by BramDe Friday, August 19, 2011 7:02 AM
    Thursday, August 18, 2011 6:08 PM
    Moderator

All replies

  • if you can not user Parent package configuration, this can be a work around:

    in the parent package, execute another package which have some variables , for example on variable for connection string, then inside that package edit the xml configuration file ( you should do it with script task and DOM objects or other XML objects provided by .NET scripts ) , then after executing this package in parent package, you can execute other package, all other packages should have same connection manager with same name in the xml configuration file, and all of them use same xml configuration.

    in this way, first package will update the xml config file, and other packages will use the xml config to start their work.


    http://www.rad.pasfu.com
    Thursday, August 18, 2011 5:53 AM
    Moderator
  • Hi Reza Raad,

    Thanks for the reply,

    The problem is that I cannot use a fixed location for my location. I can only put my packages in the 'dev' folder and via third party deployment tools they are copied in other directories, which has read-only access (files & folder).

     

    Thursday, August 18, 2011 6:37 AM
  • so why you don't try other types of configuration; I mean sql server configuration
    http://www.rad.pasfu.com
    Thursday, August 18, 2011 7:12 AM
    Moderator
  • I'm using SQL server configuration but I need an other configuration method to set the connectionstring of my connection manager that points to the sql server configuration table. I can change this Connection manager in the main package by overwriting it via dtexec but not for my child packages. And the reason that I cannot use the parent-child variable is because no other configurations can depend on a parent-child configuration: http://msdn.microsoft.com/en-us/library/ms141682.aspx .

    I could put my ETL process into one package but as it's a quite large project, I believe it will take ages to open it :).
    Maybe another option is instead of using the dts execute package task, using the dts execute process task and using dtexec with the config file attached?

     

     

    Thursday, August 18, 2011 7:48 AM
  • Did you read my first post?

    in the first post I described a way to get connection string from parent package and change its value in configuration, this configuration can be sql server configuration. after change in that configuration, all child packages will use values in this new configuration, so in the parent you set a connection manager for all child packages.

     

    and why do you think you can use parent package configuration?

    you can pass a variable value from parent to child, then in all children packages you can set connection manager's connection string property with expression which fetch data from the child variable ( the child variable fetched its value from parent variable also ), so with these all you can change connection manager's connection string from parent package with parent package configuration with help of expressions.

     

    let me know if above solutions doesn't make sense for you


    http://www.rad.pasfu.com
    Thursday, August 18, 2011 7:59 AM
    Moderator
  • Yes I have read your first post, I thought your suggestion was pointing to a fixed xml config file. A xml config file on a fixed location is not an option as I will come in a situation that my DEV; INT & PRO packages will read the same config file at the same time.

    I believe I cannot use the parent-child variable to set the connection manager of my SQL server configuration as it specificly says in the msdn article: http://msdn.microsoft.com/en-us/library/ms141682.aspx , that you cannot use parent-child configuration to set properties where other configurations depending on it.

    Note that your above solution is the one that is currently implemented, so I know it doesn't work :-). However it works correctly at runtime but it fails on the validation as it validates on the design time values, and if the validation fails the package fails. As in this situation the production environment is depending on the dev environment this is not acceptable.

    Thursday, August 18, 2011 8:33 AM
  • if your problem is on validation failings, you can set DelayValidation property to true on tasks and connection managers to get rid of validation errors.
    http://www.rad.pasfu.com
    Thursday, August 18, 2011 10:05 AM
    Moderator
  • yes, I know that property and now that doesn't fix the problem as validation ALWAYS occurs just before executing. In fact I believe that is explained in the article.

    I will implement the execute process task instead of the execute package task which gives me the posibility to overwrite the config via dtexec.

    All above "solutions" are tested and only the one with dtexec gives the proper result.

    Thursday, August 18, 2011 11:18 AM
  • could you let me know what was your problem with setting the configuration dynamically?

    and how did you do that?

    did you write an update command in execute sql task to update configuration? or you write .net scripts in Script task? you can paste them here if you any problem there and I will try to help you on fixing issue


    http://www.rad.pasfu.com
    Thursday, August 18, 2011 11:24 AM
    Moderator
  • hi Reza,

    Thanks for your time, In fact the problem is solved by executing the childeren via DTExec, that way I overwrite the connectionmanager (CM) of the sql server that is used for my SQL Configuration.

    As I have one deployment for 3 environments and I cannot change any configuration except via dtexec because this is done via commandline, I could not use a static configuration like refering to a xml file or environment variable, or registry key,...

    First I was thinking of passing the configstring of my CM via parentchild, as I could set the con string of my parent via dtexec, from there on I could pass the string to my children.

    However, do not use parent-child configuration when the variable is used for other configurations (again I refer to the article).

    So my solutions is to use instead of execute package task, the execute process task and launch the children with dtexec which again I can overwrite the configuration :-),

    • Marked as answer by BramDe Friday, August 19, 2011 7:02 AM
    Thursday, August 18, 2011 12:53 PM
  • Hi, Bram. It sounds like you have a working solution to this, and so I wouldn't recommend changing it. However, there are a couple of other patterns you could consider, if you run into this scenario again:

     

    1. Pass all values with Parent Package Variable configurations. Instead of having yor child packages retrieve some values through SQL Server, have the parent package pass in all values to the child. That way, you work around the "other configurations can't rely on PPVs" limitation, and it gives you a single point to retrieve all configuration values. I find this works well when I can't count on having a central SQL Server to use.

     

    2. Use indirect configuratons with a user environment variable. In this case, you need to use a batch file to run your parent package. Before executing the package, yoiu can do a SET MyEnvVariable="my connection string" in the batch file. This lets all subsequent packages called in the same process use the value you set at the beginning of the batch file.


    John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com
    • Marked as answer by BramDe Friday, August 19, 2011 7:02 AM
    Thursday, August 18, 2011 6:08 PM
    Moderator