January 28, 2009 at 10:00 am
We are using SQL configs but run into the problem with a developer accidentally running a test against production data.
This happened because the connections managers are being set by the SQL Config and the config connection is still pointing to production.
The developers should know better but it only takes 1 mistake to cause a serious data issue.
What is a good way to prevent this?
January 29, 2009 at 4:02 pm
In addition to the above issue the other is having our production SQL config data accidentally changed.
January 29, 2009 at 11:08 pm
The short answer is that you can't keep people from doing dumb things; if you give people scissors, they're eventually going to run with them. However, there are some things you can implement that can cut down on the possibility of catastrophe.
In this case, you can use SSIS configuration files, one for TEST and another for PRODUCTION. This would allow you to separate the settings for each respective environment. A search of the web or BOL for SSIS config files will produce a plethora of information about how to do this.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
January 30, 2009 at 6:07 am
Tim,
Thanks for the input, I have used the config files in the past and set them up for the test environment then when deploying I can update the server names to point to production. The problem I was running into was keeping the config with the package but when someone would check out the project into a working folder the path was never correct.
Also having 2 configs (test and prod) would require switching the package to point to the other. I have solutions with multiple packages and each would have to be mapped to proper config.
I guess this is where using an environment variable to point to a config file that just has the config server info and points to the correct server for the environment (Dev/QA/Prod)
What would be really great is keeping the configs in the main project directory and reference them with a relative path, if anyone know if this is possible, I'd like to hear how.
January 30, 2009 at 7:32 am
Are you running your TEST and PROD environments on the same hardware?
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
January 30, 2009 at 7:42 am
First step: Cut off developer access to production.
here is what i would recomment. Have the following cofiguration structure:
1. Create and environment variable (indirect XML config - generate from package) that points to an XML file that contains ONLY the connection strings to the databases (at the end of this step u shud have 2 XML files created - one for dev and one for prod)
The dev should have only the XML config file with dev connection strings. Yes, they can always change it, but it will at least prevent them subconsciously running packages on production environment.
2 Use SQL Database configuration table for all other configurations that are not critical to the environment in this table.
January 30, 2009 at 7:58 am
vishal.gamji (1/30/2009)
First step: Cut off developer access to production.here is what i would recomment. Have the following cofiguration structure:
1. Create and environment variable (indirect XML config - generate from package) that points to an XML file that contains ONLY the connection strings to the databases (at the end of this step u shud have 2 XML files created - one for dev and one for prod)
The dev should have only the XML config file with dev connection strings. Yes, they can always change it, but it will at least prevent them subconsciously running packages on production environment.
2 Use SQL Database configuration table for all other configurations that are not critical to the environment in this table.
You can skip the environment variable if you can put the file in the same directory of every server. That's what we were doing with DTS and going to do with SSIS.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 30, 2009 at 9:21 am
It is different hardware.
I agree developers should not have access to prod, but we're a very small shop and that is good in theory but doesn't work out so well. That is why I am looking for the flexible setup.
I like the idea of the environment variable that points to the correct XML config for that environment. The XML config then has the correct server name for the environment. This server name is then used for the SQL Config connection manager. All the package configs are then setup in the SQL configuration using the correct config filter.
This is similar to the Project Real setup.
This will probably be the approach I take. If anybody knows of any gotcha's to this please share.
Thanks for all the replies - Tom
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply