Hello dear all At my customer I have inherited a solution that in my opinion is in a desperate need of beeing reworked. The current setup consists of two servers. Server 1 has the database and SSRS (called db) and Server 2 has SSIS and works as the bachprocessor (called bp). The packages all store sensitive data in the default mode and as the solution was developed as administrator that is now the only user than can develop and run the packages. I have suggested that the customer get two more servers that we set up in a similar fashion (one db and one bp). The customer has agreed to this and I am now working on planning and setting up the two servers sp that I can have proper development, test and production environments. However costs are quite a big factor so I can't get more servers and I should keep license costs to a minimum. I have a few questions on how to achieve this: I plan on setting up the new db-server on a Windwos server standard edition. On it I plan to install 3 named instances of SQl Server developer edition. On the bp-server I plan to install 3 virtual servers (DEV, ST and AT) with SSIS. My questions are: Is this a good plan? Do I need to virtualise the bp-server in order to set up proper environment variables for Connection managers? Is there anything special I should consider when setting this up? Also, while waiting for the servers to arrive and get installed I am working on adding a dev/test environment on the production machine (As that is one step better than developing in production..). So far I have created a copy of the file structure somewhere else on the disk and I have installed a named instance on the SQL-server. What is my best option for setting up the dev/test environment AND be able to reuse that when I get the new servers? Any and all help will be reatly apreciated! /TS

Instead of opening the actual VS solution file when opening the SSIS package, I created a batch file that sets the environment variable to point to the proper XML configuration file, then launches the solution. The batch files are called Production.bat and Development.bat. set SSIS_WAREHOUSE_CONFIG=%CD%\DevelopmentConfig.dtsConfig "Warehouse Packages.sln" If you are using connection manager strings you can certainly set those appropriately in place of the XML config.

Many thanks for taking the time! In my case the packages are run from SQL Server agent. If I understand you correctly I should then have the SQL Server agent call on a bat file that holds the relevant environment variables for that environment? /TS

Ah, I thought you were interested in how to set up for ease as a developer... No, I wouldn't do that for running the packages. You should be able to set the configuration file to use on the configurations tab on the SQL Agent Step properties.

Provided the SSIS is not getting upgraded, I think you can continue developing on the old servers. You can even develop on your own machine. When it comes to virtualization is it a matter of choice, nothing else, just make sure the box supports that. In most companies the process is as follows: a developer develops on SQL Dev on a local machine, deploys to a Cert SQL server that is also a Dev licensed (could be virtualized), then once the package is good to go moved/copied to Prod. I would refrain from setting a dev/test in prod. Packages are very portable after all.Arthur My Blog

I want to move away from the develop in production situation that we have now. The reason we have this situation is that the first developer overlooked the "encryprt sensitive data" setting when he started developing. I just want to be able to have more than one developer and proper testing of future packages before they are moved to production.. Thanks for the tip about using the configurations tab! I must admit that I haven't noticed that one before. Do you know of any good resource for information regarding how to use it with for example xml-configuration? /TS

When you say "Provided the SSIS is not getting upgraded" do you mean the SSIS packages or do you mean upgrading Integration Services (as in going from 2005 to 2008)? At the moment I only have the possibility to use the prod server and in order to move forward at a decent pace with the transformation of the old packages I feel I need to have a Test instance. I'm going to try using a new instance on the production bp server and use the Configurations tab in the Job Step Properties. /TS