This release cycle is in full tilt and I’m busy working on tightening up some of the superfluous aspects of our code promotion processes. We’ve settled in on building the configuration scheme around SSIS’ indirect XML configuration file, which we expose to SSIS via an environment variable. The environment variable simply passes the dtsConfig location on disk to the SSIS package, rather than having you explicitly provide the dtsConfig location. Once the package pulls in the config file at runtime, the individual nodes of the XML formatted dtsConfig can be assigned to package variables and then used in expressions. Here’s what the screen looks like in SSIS for both a direct and indirect XML configuration:
The XML-based dtsConfig file has worked really well, allowing us to provide a single point of setup configuration for each environment to the SA’s that hold the keys to the installation. Our process goes like this: remote development zone -> remote final deployment zone -> test zone -> production (hands off). We try to keep everything from all dev’s merged into the initial development zone in order to create a single flow of artifacts through each deployment. We take the following actions to configure each zone:
- Create the XML environment variable indicating where the dtsConfig is located.
- Configure the dtsConfig with server names, flat file locations, SharePoint URLs, etc.
That’s it! From these two simple steps in each zone, we are able to pass the environment specific values from the dtsConfig to our deployment AND unit test files. PowerShell plays really well with reading and writing XML – so well we use them to log checkpoints to an XML config during the deployment process. In addition, we use the XML object to reconfigure our unit tests when they are initiated at each environment – again based on the dtsConfig values!
Sonam Rastogi has a handy write up of how to use the XML object with PowerShell: http://blogs.msdn.com/b/sonam_rastogi_blogs/archive/2014/05/14/update-xml-file-using-powershell.aspx
I won’t go into our specific details of our code, but will simply rave about how well the indirect configs have worked for our completely disconnected environments. Add in PowerShell’s easy to use XML object and you can quickly automate the deployment and testing of your own database solutions.
bowdenc 