February 22, 2011 at 4:10 pm
Hello all,
I'm implementing package configurations and have a plan. If anyone would like to review or poke holes at my plan then please do.
I need to implement package configurations both for disaster recovery and for development.
I'm grouping my configurations into 3 types. FileLocation, DatabaseServer and Database.
So on one server I may have the following configurations:
FileLocation_General
FileLocation_Myproject1_Export
FileLocation_Myproject2_Export
DatabaseServer_1
DatabaseServer_2
DatabaseServer_3
Database_A
Database_B
Database_C
Database_D
Database_E
I plan on using xml files as my package configuration option.
I plan on using variables in my packages and setting for example a 'source connection file' Connection manager to
@[User::FileLocation_General] + "\\Subfolder1\\Subfolder2\\file1.txt"
Here I face 2 choices. Put all my configurations (FileLocation_General,FileLocation_Myproject1_Export,etc) in one xml file or a have a sperate xml file for for each configuration.
I'm leaning towards the latter for the following reasons.
-If I put them all in one file each package would have to contain all the variables even if the variable is not relevant; if they are in individual files the package developer can pick the ones they need and only add the appropriate variables
-in the latter case rolling out new configurations would seem less likely to blow things up, I would just add a new file specific to the new configuration rather than modifying the single production xml file.
If anyone would like to comment on this, please do.
Thanks
February 23, 2011 at 5:56 am
First of all, is the XML file an indirect configuration or a direct configuration?
Meaning, is the location of the XML hard coded into the packages? If it is, you have to make sure that the exact same path exists on all the machines.
I would create an XML config file for the general configurations, which are the same for all the packages e.g. logging, connection to msdb et cetera,
and an XML config file for each package that contains configurations specific for that package.
If there are too many packages, maybe a configuration file for each "domain".
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 23, 2011 at 8:42 am
I'm using C:\SSIS_CONFIG. So yes this will need to be on all production servers, DR servers, and developer workstations.
It seems we have 3 different ideas here:
1) a single xml file
2) a file for each configuration
3) a file for each package
3 seems to defeat the purpose a bit. If I have to change every xml file then I might as well change the packages themselves, they're xml files as well.
>>If there are too many packages, maybe a configuration file for each "domain".
My highest organizational level at this point seems to be the 'server' level and then next a configuration that can pertain to that server.
February 23, 2011 at 12:09 pm
Chrissy321 (2/23/2011)
It seems we have 3 different ideas here:1) a single xml file
2) a file for each configuration
3) a file for each package
3 seems to defeat the purpose a bit. If I have to change every xml file then I might as well change the packages themselves, they're xml files as well.
There can be some properties of a specific package that need to change according to the environment the package is in. If you're going to change the package each time it moves from one environment for another, you're in for chaos 🙂
What if you forget to change it? What if you are on holiday and some packages need to be deployed?
You don't have to create an XML config file for each package, just for those who have special configuration needs.
Chrissy321 (2/23/2011)
>>If there are too many packages, maybe a configuration file for each "domain".My highest organizational level at this point seems to be the 'server' level and then next a configuration that can pertain to that server.
Sorry, I did not express myself clearly. With domain I meant something like "project" or "department", not a domain like in networking.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 23, 2011 at 1:15 pm
There can be some properties of a specific package that need to change according to the environment the package is in. If you're going to change the package each time it moves from one environment for another, you're in for chaos
I don't want to change the package at all, I just want to change the package configurations contained in the xml file (or files). So if I am in a DR scenario I want to change the config file to contain the name of my DR server rather than my production server.
You don't have to create an XML config file for each package, just for those who have special configuration needs.
All my packages have special needs. They point to a database server, a database, and file locations and any of these configurations can change if I am moving the package from a development to a production environment or to a DR server.
So I want to implement Package Configurations to make my packages portable. I'm seeking any guidance or advice on whether to have one xml configuration file per server or to use a model where I have a file for each configurable item (file location, database server, or database).
February 23, 2011 at 1:51 pm
Chrissy321 (2/23/2011)
You don't have to create an XML config file for each package, just for those who have special configuration needs.
All my packages have special needs. They point to a database server, a database, and file locations and any of these configurations can change if I am moving the package from a development to a production environment or to a DR server.
OK, but does the destination server and database change for each package. Surely there are a bunch of packages who have the same destinations. They can share the same XML config file.
Chrissy321 (2/23/2011)
So I want to implement Package Configurations to make my packages portable. I'm seeking any guidance or advice on whether to have one xml configuration file per server or to use a model where I have a file for each configurable item (file location, database server, or database).
What I usually do is one config file per server (actually I use config tables, but that is another discussion), and in my packages I create a package configuration for each configurable item. For example, I create a package configuration for the import directory by selecting the config file and then choosing the right configuration filter.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 23, 2011 at 2:15 pm
OK, but does the destination server and database change for each package. Surely there are a bunch of packages who have the same destinations. They can share the same XML config file.
Most packages on the server will be similar but there is significant enough variation that it occured to me that one xml file for all the configs might not be the most flexible way to go.
What I usually do is one config file per server (actually I use config tables, but that is another discussion), and in my packages I create a package configuration for each configurable item. For example, I create a package configuration for the import directory by selecting the config file and then choosing the right configuration filter.
Ok so it sounds like the configuration filter will allow you to select one configuration/record. This is similar to what I might try doing by placing each configuration in one file.
Maybe I am forcing the analogy but perhaps the xml configuration folder is analogous to the config table and the series of xml files with one configuration each would be analogous to one filtered record in the table.
How do you tell your packages where the configuration database is? Environment variables?
February 23, 2011 at 2:40 pm
Chrissy321 (2/23/2011)
How do you tell your packages where the configuration database is? Environment variables?
Jup. That is an indirect configuration.
I have a WMI script that can automatically generate that environment variable for you, very useful for deployments.
If you're interested, let me know.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 23, 2011 at 7:24 pm
For a datawarehouse implementation we put each connection in its own xml file and ALL packages shared the same config file, so every connection to a particular database could be changed in one place, if for any reason the database changes servers.. Any shared configurations were also there own file. If there were configurations specific to a single package it had its own file for the package, we didn't have this often..
Like many things in SQL, it depends. The balance between too many files and a good seperation of functions takes some thought and is specific to your case..
CEWII
February 23, 2011 at 11:38 pm
Personally, I've got 40 daily package using a file for each configuration. About 38 of them uses just the 1 config file, and then the others have an extra config file which is needed for the exports again.
It's worked for me for the past year or so with no problems
February 24, 2011 at 7:39 am
Something to consider is which version of SQL Server/SSIS you're using ... configs behave and load a little differently in 2008 than in 2005 if you're running via dtexec and intending on run-time switches overriding design configs in the actual package, if deployed:
In 2005, at package execution, design-time configs are loaded, and then command line/run-time additions (using the /config switch) ... we used this extensively to allow deployed packages utilize xml config files tailored to specific servers and kept in a standardized location on each server. Worked beautifully.
In our testing for upgrading to 2008R2, MS changed the SSIS run-time behavior: design-time options load first, then additional run-time configs (as above), and then the design-time configs are reloaded, which essentially overrides run-time configs.
We did some looking around and testing to use various run-time switches (/config, /set, etc.), which didn't seem to work as documented (and found no working example using the /set to get around this change). Our end solution was to move from xml- to table-based configs and specify a /conn switch at run-time, which results in the design-time configs pointing to the run-time table connection location, giving favor back to run-time configs.
This may not necessarily apply to your DR situation or plans, but keep it in mind if in the planning stages of config setup and based on your SQL SERVER/SSIS versions.
Cheers!
February 24, 2011 at 10:43 am
Thanks, this is good information. We do use DTEXEC with the /config switch. I think in my situation the configurations we pass with DTEXEC (dynamic filenames, query criteria) are distinct from what I want to set with package configurations (databases server,database, file locations) so hopefully this won't present a problem.
Thanks to all!
February 24, 2011 at 10:58 am
Yup - from what we've seen/researched, those seem to work fine ... think the main issue is the configuration loading, specifically, in 2008 with the design configs being re-loaded at run time in a deployed scenario (locally/in BIDS, local xml config files are fine - we still use them that way for testing and some non-deployed packages).
Again, this change appeared in 2008, so if you're in 2005, there shouldn't be any problem ... it's when you've designed around the use of xml config files being dynamically assigned at run-time in 2005 and then upgrade to 2008 that the config loading becomes an issue, depending on your deployment/usage scenario.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply