Help with setting up the configuration file
I need a little assistance with getting things started for converting my DTS Packages to SSIS. I'm trying to use a configuration file (currently an XML one). I have no issues getting the SSIS package to run looking at the configuration file, but my issue is that only some of the packages will be kicked off through the scheduler and the others will be run manually. I'm assuming that if the scheduler kicks them off it'll run on the actual server (either test or prod) while kicking them off manually will not. Since the main thing the config file is going to be used for is to tell which server to use I was hoping to use one file for most of the packages. I've not had much luck finding a way to keep the stuff I'm working on pointing at test and to put my stuff for prod kept looking at prod. I really don't want to have to change which config information to use each time in the package when I go to compile/deploy as that's likely to be missed during maintenance and is another headache I'd really like to try to avoid. Does anyone have any insight on how I can best handle needing to use multiple environments from one server? Thanks
July 5th, 2011 12:09pm

Hi, please see this article SSIS: Indirect configurations ROCK!at http://consultingblogs.emc.com/jamiethomson/archive/2005/11/02/SSIS_3A00_-Indirect-configurations-ROCK_2100_.aspx and make sure to follow links in it, I hope it will be an eye opener for you.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
July 5th, 2011 12:17pm

The problem with environmental variables is that if I execute the package from my local machine won't it see that it's pointing at test? If I change it to prod then won't the packages I'm currently working on be pointing to prod? And wouldn't it kind of defeat the purpose to go in and change the environmental variable to point at prod, execute the package, then change it back to dev/test? Isn't the way to execute the packages manually to just open the dtsx file and click the execute button? And wouldn't doing that from my machine use what my config/environmental variables are pointed to?
July 5th, 2011 12:31pm

Yes, when you kick off a package from BIDS, and you have an XML file or Environment Variable set for a configuration, it will use the config settings on the workstations as contained in the file/EV. But also, here's one that some people miss: If you open up SSMS on your workstation and connect to the SSIS Instance of a server, and execute a package THAT way, you ALSO use the settings in your local file/EV. There's nothing wrong with using the EV approach to point to an XML file which holds the Connection String of a SQL database that holds all the OTHER Configuration entries. Then you copy that database into your Production environment and change the entries in the table and that becomes your Production Configuration set. But basically, you need a "single point of entry" into the Configurations and an Environment Variable is a good way to do it. Try this: create a couple copies of your XML config file, each with different settings, and stored in sub-folders with different names. In the DEV sub-folder, have an XML file that points to the SEV Environment Configuration set, and in the PROD sub folder, one that points to PROD, etc. All the files should have the exact same name. Now simply copy and past one file from its sub-folder into the folder referenced by the Environment Variable and voila, you will now be executing packages on your local workstation against PRODUCTION resources. When finished, copy and paste the DEV copy over the PROD one.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
July 5th, 2011 12:48pm

I still don't see that as being any different than having two config files, one pointing to prod and one pointing to test, and then swapping the files when I need to run stuff from prod locally and then swapping back after the package executes so I can continue to work on test. Would it be better to just use SQL Server Agent to schedule the job to run as a "one time" thing whenever I need it to run? The jobs that run manually are usually kicked off either once/twice a week/month unless there's an issue then it'd need to run a few times a day until it is successful (after fixing the import files).
July 5th, 2011 2:08pm

You can use an Environmental Variable to point to one set of files in Cert and to another in prod like described here: http://jessicammoss.blogspot.com/2008/05/ssis-configuration-to-configuration-to.htmlArthur My Blog
Free Windows Admin Tool Kit Click here and download it now
July 5th, 2011 2:37pm

Maybe I'm a little thick with that, but I'm running stuff for both test and prod off my local machine so I'm trying to understand how I can do that without changing the package or environment variables.
July 5th, 2011 2:45pm

You have to somehow instruct the package to consume one setting or another. IMHO the Environmental Variable approach is the easiest because you can modify it using even a short command line e.g. SET Env_Var_ConfSetLocation="Path to my test XML config file" Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
July 5th, 2011 2:58pm

But don't I still need to change the environment variable based on if I want to run something on prod or something on test?
July 5th, 2011 4:51pm

You doArthur My Blog
Free Windows Admin Tool Kit Click here and download it now
July 5th, 2011 5:03pm

That doesn't really help me with what I want to accomplish. I may as well have the connections "hard coded" in the package then to prevent things from screwing up.
July 5th, 2011 6:16pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics