I think this has probably been blogged about and documented elsewhere to death but it's well worth posting something about (even as a reminder to myself).
SSIS package configuration is great but as always, as people use something heavily, its usage gets more sophisticated and so do the problems. One thing I like is to be able to centrally log and also centrally get configuration values but to do this I need to be able to support multiple environments (dev, test, etc.) and also multiple applications (the same variable used in different packages). This is where I've found the configuration to be little limited so in true developer fashion we came up with an alternative configuration mechanism. I won't go into the details but effectively we get all configuration values from a call to a database.
The only problem with this is that I want to be able to tell the package where the configuration database is, what environment I'm executing in or the application name I'm running under but I can't do that with hard coding a config file into the package. Of course there are alternatives. A number of server variables or registry entries but really I just want to do this the once and have control over everything else.
In my perfect world I would want to structure my data in something similar to this;
This contains pretty much everything I need all in a single location. I can have multiple configuration files for different SSIS based systems, a place for source files and if I wanted to drop in XSD files I could create a new folder or just stick it in a suitable existing location. But this is when these little issues creep in.
How do I tell my package where the SSISRoot folder is? I don't want to use configuration files as that defeats the object of having a single location where I keep my configuration files, very chicken and egg. So what alternatives are there? An environment variable can be used to point to the config file location but then I need an environment variable for every config file I have once again defeating the object. What about creating an environment variable that points to SSISRoot, that would deliver what I need. I tried this. I created the variable and called it from Start>Run %SSISRoot%, it worked and I get the folder location that I wanted. The next thing to do was to put "%SSISRoot%/Config/SSISConfigFile.dtconfig" into the path for the file however that had little success, and when I say little I mean none whatsoever.
So I'm back to square one. Hard coding the package config file into the packages. This is one of those issues I would be grateful to hear any clever workarounds that anyone has so please let me know as this is getting increasingly frustrating.....!
Posts
1 comment:
I used the xml configuration file to separate my environments. It has to be the first config file that gets read. For me it only has the SQL Server where the [ssis configurations] table is but you could store all of your directory info too. I had a hard time with the xml security "feature" not letting me share my xml configuration file with other user accounts like SQLDevJobSvc, or SQLQAJobSvc or SQLProdJobSvc. I did briefly use EncryptSensitiveWithPassword in the package to allow the sharing of the xml configuration file with other user accounts, but creating a password that is hardcoded in a job just seems wrong. Then it dawned on me that if the xml configuration file doesn't know who created it then it wouldn't know who to keep out (que the singing angels).
Remove the following xml attributes and any package can run as any user and read any xml config file written by anyone:
GeneratedBy=
GeneratedFromPackageName=
GeneratedFromPackageID=
...You seem too smart to store sensitive (user/pwd) information in a file, but just in case I'll include the blurb we use in our office:
"If sensitive information must be stored, then XML File configuration cannot be used
due to security. (And you need to rethink what the heck you are doing)"
Post a Comment