Need some config explenation Here is my situation. The config in my Main package is set via a .dtsConfig file (the config file sets the connectionstring to the config database). From there on the connectionstring to config database is passed on via Parent-Child variables. I cannot use env. variables. Everything worked fine until I needed to set my connection via Windows Authentication, suddenly I've got tons of errors while executing my package via dtexec => anonymous user,... my windowsId got lost (no problem for me). In dev the .dtsConfig file & my config db is still working with sql users. What I don't understand is why my process fails due to validation errors. Normally during the process after setting the config from my database, it should work fine no? Bram

So your Child packages all have Variables that contain Connection String info, and then all the Connecton Managers have an Expression that is based on the relative Variable? Seems in-efficient. Why not just use the same .dtsConfig XML connection to the SSIS Configuration database for each child package? It's only one more hit to the database per executeion, not like you're going to be hitting that Db thousands of times, right?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

If you're getting validation errors, and not execution errors, I believe that's probably because you need to put in a valid default value for the expression(s) that is eventually going to be overwritten by your configuration.-Tab Alleman

Todd, All config (conn strings, variables, ...) are stored in a SQL server config table. As I cannot use sys. env. variables, I pass the connectionstring of the SQL config table via an xml .dtsConfig file. As far as I know you can only apply the config file to the package you execute (main package) not the child packages, that's why I use parent-child variables (only for the connectionstring of my config db.). I cannot use fix path either as the packages aren't even located on the SSIS server. (very dynamic and secure environment). I know in sql server 2008 the validation process has changed. So it first applies the connectionstring in the variable that was set at design time and after it overwrites this variable with the value of it's parent, which sets the connectionstring to the config database, where all other configuration is stored. I know the configuration works as it runs on different environments, but when my parent-child variable is invallid at design time, I cannot start my package at runtime Here are the steps of this configuration: 1. dtexec /f "...main.dtsx" /Conf "...Conf.dtsConfig" (contains connectionstring of sql config database) 2. in the main the variable config is set with the value stored in the sql config database (as i cannot pass my config file to my child packages) 3. via parent-child config, my config variable is set via the variable of its parent. This config variable sets the connectionstring (via expressions) of my config database.

As far as I know you can only apply the config file to the package you execute (main package) not the child packages, that's why I use parent-child variables (only for the connectionstring of my config db.). I cannot use fix path either as the packages aren't even located on the SSIS server. (very dynamic and secure environment). That is not correct. You can apply the xml-file to each subpackage. But I agree with Tab Alleman. Use Default values and set DelayValidation to true

Hi Tab, You are correct, but at design time I switched to windows authentication, I execute dtexec via psExec and there I loose my Windows id so it runs the package under anonymous user. However in dev at runtime via my configuration, I still use sql users but I got errors that the anonymous user doesn't have the rights :-). In order to fix this I had to set each config variable of all my package with the connectionstring using sql users, which was a lot of work. So in fact everything works, but how is it possible that my package can fail because the connectionstring to my config database is wrong (it will be replaced by the parent-child config with the correct string). So it fails my package before it applies the configuration first!? There must be something that I miss as I cannot see the logic. I hope this forum clears it all out :) Thanks in advance, Bram

I'm sorry applying the xml-file to the child packages is new for me, could you explain me how this works? Does this work's automatically? So if all my connectionsmanagers have the same name, they will all be overwritten by this config file? And setting the DelayValidation to true, is this only required to config connection manager ,or to all my connection managers, or in the properties of my control flow of the package, or to the execute package task? It looks like every component has this property :-) Thanks in advance!

If you work with SSIS2005, all configuered values (Connection Managers, Variables etc.) have to be in the package, then the configuration will work fine. Yes it will overwritten by the configuration. DelayValidation shoud be set to true for all tasks which can make trouble, if a Connection Manager is not set well.

Not having read all of this thread, I offer this AGAIN: You CAN specify Configurations in Child Packages. I do it all the time. I execute them from SQL Agent and since the child packages (and the parents) are DESIGNED with the Configurations, I do NOT need to specify any at run-time. Try this little experiment: Create a simple Parent package that calls a Child Package. Design the Child to use an XML configuration for a setting that you can verify. Before you run the Parent via command line DTEXEC, edit the entry in the XML file Run the package and you will see that the child picks up on the new XML value even though you do not specify any Configs in the DTEXEC command. It works, trust me.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Hi Todd C, I believe there was a miscommunication and I would like to come back on this topic as the problem reoccurs. I have done the little experiment, but it needs to be more dynamic, as I cannot refer to a static path of the config file, I need to pass this config file via DTExec. I tried this and it changed the connectionstring in the parent but the child configuration connection manager was not overwritten. So what I've meant in the previous post is that I cannot overwrite my configuration in the child packages via DTExec. What I want, is to set the configuration of my child packages via the parent package (because I can only change my connection at runtime by passing a .dtsConfig via dtexec.) I have done this by passing the connectionstring (of the config db: all other configurations are done via SQL configuration) via parent-child configuration. However, during the validation process it validates the connection manager with the config connectionstring set in design time. As it points to my DEV db's that aren't accessible from prod it fails. I know the delay validation property and I have set this to true on all my connection managers, package and even the tasks, but it still fails on the validation. Maybe due to the configuration sequence http://msdn.microsoft.com/en-us/library/ms141682.aspx ? My current configuration bottom-up: Child packages: variables & connection managers are configured via SQL Server Configuration > The Connection manager of my SQL Configuration manager is set with parent-child variables. Parent package, the config variable is set with the SQL Server configuration (config variable is used for the parent child variable) > the SQL Server configuration is overwritten by passing a .dtsConfig file VIA dtexec. Thanks in advance!

Mybe try this: Create a Variable in your Parent Package and use an Expression or Script Task early on in the Control Flow (PRIOR to calling child packages) and assign this Variable the ConnectionString property of the Configuration SQL Database. (Let's assume you name this variable "Config_Connect_String") Now, in your child packages, set up in your Configurations, FIRST item would be a Parent Package Variable that grabs the value of Config_Connect_String and pulls it down into a Child package variable of the same name. Next, modify the Configuration Database Connection Manager and set up an Expression on the ConnectionString propery, setting it to the value of the variable. Note that you will need to 'seed' the variable with a valid Connection String so that you don't get errors. Now set up additional Configurations based on entries in the Config Database. This approach MIGHT work. It might NOT as well. Depends on if the run-time engine honors the evaluation of the Config Datatabase Connection Manager BEFORE it hits that database to get additional Configuration items. If it does, then it should work. But if the run-time engine applies all Configuraitons, THEN goes on to evaluate the Expressions, you're hosed, sorry. Not sure what else to suggest. Maybe you could explain why you need this approach to be SO dynamic? Is it that the same SQL Agent installation being used to execute packages in BOTH Environments, and the only difference is how you set up each job? Maybe you have two jobs that run the same SSIS Parent Package, but one specifies a PROD xml Config file, and one specifies a DEV version?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Hi Todd C. Thx for suggestion, the solution you described is in fact the solution that is currently implemented. It works except for the validation, as the parent-child variable is ALWAYS applied as last, (I've got this info from: http://msdn.microsoft.com/en-us/library/ms141682.aspx). So as long as my dev environment is available the project validates with dev configuration and runs with pro configuration. However this is not acceptable that the ETL in production depends on my dev environment... The reason why it has to be so dynamic is that I'm working for a big company with a lot of procedures. For example, the ETL is scheduled with Autosys, that starts a Perl script which executes the dtexec command line and points to the packages on a file location. To put my ETL on this file location I must copy it on the dev folder and use a third party tool that copy it on other (read-only) folders and eventually on the PRO folder. I believe I've hit the boundaries of the SSIS configuration, as I don't see any solution at the moment, I will propose to change the deployment procedure to use the SSISdeploymentManifest, I have noticed that via this tool, the path to the XML file is automatically set (in my test the dtsconfig file was at the same location as my packages, but that is fine). However I will still do some research on the deployment. Thanks for the time you've spend on my thread and any suggestions are still welcome, I will post my solution here ones I have it :).

I was thinking, for once :). Couldn't I use the execute process task instead of using the execute package task? That way I coud use the dtexec task to lunch my child packages and I could overwrite my connectionmanager via /CONN or /CONF, the same way as I do for the main package.