Hi I'm developing an SSIS package that will use XML configurations. When I start I create variables/connection/etc. I then added an XML configuration for some of these values. It writes the values out to the XML file. As development continues, a lot of the values change, so I update these in the XML file. When I run the package, the values that are updated don't persist in the BIDS environment, so when I next add a value to the configuration, it overwrites the XML files with all the old values. Is this behaviour normal? Or am I holding it wrong...? Thanks Sean

"BIDS environment" does not store/capture back the values updated outside in the config files after they were produced. If you re-run the SSIS Package Configuration Wizard and set any new objects to rely on the config files then the original design values get re-applied. This is by design in SSIS versions pre - 2012Arthur My Blog

So if all your settings are stored externally, and you move the package to a different environment/server (with a new configuration file), and you then want to (say) run an individual step/task - you just can't do that, because the settings in BIDS are now different to those on that server, and there's no way to load in the settings from the file - i.e. execute "configuration step" only. Correct? (SQL2008R2) So you'd have to manually update ALL the relevant settings in BIDS before you could run that step?

Yes, since BIDS is not part of the deployed package(s) you cannot and do not need to re-run the Configuration Wizard. You can still however override certain package variables or connection strings if you wish. In SSIS 2012 this becomes even more extended. In the pre-SSIS2012 world you merely amend the dtexec.exe command line - http://msdn.microsoft.com/en-us/library/ms162810%28v=sql.90%29.aspxArthur My Blog

Not sure why you say "do not need to"... say a dataload fails midway through for whatever reason, say a bad data conversion. I can fix the item, but then need to continue the load from that point onward. How would I do that? In DTS, I would open the package, run the Dynamic Properties Task (equiv. of Configuration setting), then Execute the steps needed (as the DTS package now has the correct variable "state" from the DPT execution). In BIDS, I have DEV values in the variables, and LIVE values in the config file, but I can't import those values into the BIDS "state" and then execute the last step(s). I don't understand MS's logic here...

Executing an individual task actually runs the configuration, even though it doesn't show up in the execution log - I've just tried it. A simple Script Task messagebox'ing a variable. Then add a config file setting that variable, and execute the script task. The value comes from the config file, but the "Progress" tab doesn't log anything like "The package is attempting to configure from the XML file..." as it would if you ran the entire package.

To carry the package execution forward from the point of failure consider implementing checkpoints. See a how-to http://www.bidn.com/blogs/danmatisis/ssas/718/creating-checkpoints-in-your-ssis-packageArthur My Blog

So I guess the way to avoid overwriting your config file when you need to configure a new variable, is to add a NEW configuration for the new variable, let it generate the XML file (unless you're brave enough to type it into the XML yourself), then copy the XML into the old config file and delete both the new config file and the new configuration in the package. Is that what people do? I'm trying to work out how best to use this SSIS thing and it seems like there are some non-intuitive ways of doing things. Would like to know I'm not missing something obvious...

So I guess the way to avoid overwriting your config file when you need to configure a new variable, is to add a NEW configuration for the new variable, let it generate the XML file (unless you're brave enough to type it into the XML yourself), then copy the XML into the old config file and delete both the new config file and the new configuration in the package. Is that what people do? I'm trying to work out how best to use this SSIS thing and it seems like there are some non-intuitive ways of doing things. Would like to know I'm not missing something obvious... Yes, you can add a new config file, but the solution becomes clumsy, or simply speaking, hard to maintain because you end up with two files, not one. And let me stress this again, after the package is deployed there is no way to instruct it to work in SSIS 2008 or older against any different (new) config parameters.Arthur My Blog

Well, obviously you can parametrize about everything in the package and thus end up with a single, comprehensive, config XML file. You better open it in specialized XML editors of course. But then, as an advantage, you have all the aspects of your package configurable and flexible any time you need or want.Arthur My Blog

And let me stress this again, after the package is deployed there is no way to instruct it to work in SSIS 2008 or older against any different (new) config parameters. "Deploy"? Why do that? Why not just copy the .dtsx file? (There is a setting "AllowConfigurationChanges" in the deployment options)

To carry the package execution forward from the point of failure consider implementing checkpoints. Thanks. Except now I'm going to have to put checkpoints on EVERY task (Fail Package on Failure). Why wouldn't you?

The intended method of "fixing a package and continuing execution from a mid-point" in SSIS is NOT to open the package in a designer and manually launch it. The intent is for the deployed package to not require any interaction. You're supposed to re-execute the package in its entirety. Checkpoints were a feature intended to make starting from a mid-point possible and fairly easy - but I don't hear many success stories about their use. Instead, you should make your own "checkpoints" within your package. In every process, there are steps which you - as an outside observer - can see have been "completed" because of changes in the environment. Files "now exist" in a certain folder, rows dated today now exist in a table, etc... These are "checkpoints" for you. Use Sequence Containers in your package to group things that have a detectable "I did this" effect. Then, prior to those Sequence Containers, detect that "state" and conditionally execute the container if the state requires it. That way, if your package fails, you diagnose and correct the problem in the environment or package, then re-execute the package without "holding its hand"... Talk to me now on

Thanks Todd. It would be a brave man who would use the checkpoints and who re-runs a failed package after making changes (to the data/database/package) without "holding it's hand". I still don't see what the benefits of deploying a package are. It's still an editable .dtsx file, isn't it? It doesn't get compiled or anything - so why bother? The only advantage I see is a kind of "version-control" of when packages under development are promoted to the live environment. Back to the original point, I still don't see why BIDS doesn't persist the variables from a configuration. If you're running a package in an environment through BIDS for whatever reason which loads configuration values, it seems crazy not to persist those values. Otherwise it's going to cause a lot of confusion & error. What's also frustrating is that a command line CONFIG doesn't overwrite a design-time CONFIG. So when you "deploy" you have to remove the design time config. But then when you need to debug, you have to add back the design time config to reload the values. Why should design time override run time? This whole config thing seems poorly implemented.

It's confusing until you get used to it. And no, like you, I wouldn't have designed it that way either. (It's easy to armchair quarterback.) However, I'm not sure what the real issue is with the "persisting" that's got you in a twist. At the time you add a configuration, SSIS writes the "current dev state" to the XML file, overwriting what you had there, correct? Isn't that what you'd want? "Save" my configuration that I've just set up? I can understand this would be problematic if you were overwriting "production" configuration with your design-time configuration... but in that case, yeah, you're "holding it wrong" (I love the iPhone 4 antenna meme). Your production configuration shouldn't be used (or writeable!!11!one) from dev. If you have configs set up right, you don't have to "remove" anything before deployment. It's not terribly brave to run packages "blind" (just deploy and execute with DTExec, not BIDS), with the following assumptions: You have your packages "chunked" into discrete sections (perhaps just one). But each section knows if it's been done already, and if it fails in the middle, either "undoes" (via transactions) what it did, or what it's done is "harmless" to leave alone. (For example, being "half done" a file-copy process can be OK without cleaning up, as long as there's a "I'm done" flag in a table or file somewhere that hasn't been set yet.) I call this "idempotency" - your packages should be idempotent. The biggest benefit of this (in my mind) is "first, do no harm". If you (the developer) are not running the packages in production, wbere some other meathead is responsible, you don't want them thinking "hmm, the job failed... probably cause of a network problem, so I'll just fire it up again" which inadvertently double-adds or otherwise messes up data badly. You want the operator to attempt to fix the issue - which we're assuming ISN'T with your package, but IS with the environment - then run again, and have your system be resilient enough to fail gracefully if they didn't fix the right thing. If the assumption is that your package is buggy - fix it in dev/test, not in production! IMO, BIDS should NOT be installed on the server. This is too much of a temptation for a dev to get his sticky, dirty hands on production. (I'm a dev, and I'm positively filthy.) It's too much of a shortcut, and too dangerous. Talk to me now on

Hi Todd & thanks for the help, again. I think I'm starting to get the feel for it. A major "aha!" was that packages load their state from the configuration files when you open them in BIDS. I didn't realise this. Coming from DTS, where I used an explicit task to set state (variables/connections/etc), when I opened a DTS package it was exactly as it was where I last saved it. I could run that explicit configuration task to get the state from an .ini file, and then I was good to go. In SSIS, it opens the package in the already-configured state, which has an upside. I do like the ability to manually "reload" the state whenever I like, though. In SSIS I find myself closing & reopening a package to reload the state - or deleting and recreating (with the Reuse option) a config file (when I update the XML file externally). I've also had to create one config file for each child package, instead of having one config file for the whole thing (as in DTS). Otherwise (a) I get validation warnings/errors all over the place, because I've got additional config values that aren't relevant to the current package, and (b) if ever the values of a child package are exported, it would lose all the XML file values for the other packages' configurations. As for "removing configurations before deploying", I think it's because this link: http://msdn.microsoft.com/en-us/library/ms141682.aspx Can easily be understood that (1) your run time config will be overwritten by re-applying the design time config, and (2):"You can use the /ConfigFile option to load additional configurations that you did not specify at design time." "You cannot use the /ConfigFile option to load configurations that replace the configurations that you specified at design time." Maybe it's just me, but that seems to say you can't update values in the package from the runtime /CONFIG switch if there is a configuration file setting that was last saved at design time. I'll have to test this as that would make /CONFIG completely useless... Thanks again.