Multiple SSIS environments each pointing to their own DB running on the same PC at the same time?

[all4L]

I'm new to SSIS, however I'm trying to do something which I would've thought should be quite a simple use case with SSIS, and yet I can't seem to find a solution.
To cut a long story short, I'm trying to get two SSIS environments (e.g., DEV1 and DEV2) to run independently on the same PC. There are times when I have to do ongoing development work and emergency bug fixes within the same time frame.

For this reason, using the system environment variables to hold something like the database connection string will not work since DEV1 and DEV2 point to their own databases each with their own connection string (and I don't want to have to manually keep setting environment variables as this is error-prone).

Also, one other requirement is I need to freely copy packages between my DEV1 and DEV2 environments, and not have to edit packages to reset package variables etc (e.g., some of the variables hold paths which point to data files/folders for the respective environment).

We're using SQL Server BIDS 2008 R2 for our development envionrment...

Things I've tried and failed to work:

(1) XML Config files - the path to the XML file is hardcoded, so when I copy a package that uses the XML config file from DEV1 to DEV2, the DEV1 XML file gets used in DEV2 environment - not good!

(2) SQL Server Configuration - the connection string to the SQL Server Configuration table is also hard-coded into the package, so again, copying a package that uses the DB configuration to the other environment results in the wrong configuration being used - again, not good!

(3) I tried using a batch file with the CONFIG and COMMAND flags to assign values to the package variables, only to find that in 2008 R2, the design-time variables overwrite command line parameters - confusing!

(4) I've even tried creating a flat text file with the environments values I want to use, and attempting to read the file using basic C# code, but even that doesn't work unless I specify an absolute path, which defeats the purpose of being able to copy packages between environments without having to edit the packages variable values - so basically, still stumped!

I've been at this for the last few hours, so it's very possible that I've developed square eyes and am missing really obvious.

Surely, there must be someone out there who has managed to get multiple SSIS environments each pointing to their own database to run on the same PC, at the same time?

Thanks in advance.

 

[fredericofonseca]

xml config files - you can supply the path to the file on the command line for DTEXEC - if you have different environments then obviously the path to the config files should be based on the environment

Sql server - again the connection property for the connection to Sql server to get the remaining configuration can be supplied on the command line - again that command line should be specific to the current environment


as for hardcoded values on the packages - anything like that that is environment specific should be overriden using variables/configuration files

and to avoid "manual" intervention, all your command files and/or executables should be on their own environment folder and should read any config based on relative path, not absolute path

e.g.
c:\dev1
c:\dev1\config
c:\dev1\bin
c:\dev2
c:\dev2\config
c:\dev2\bin


program x starts on c:\dev2\bin
- gets executable path which returns c:\dev2\bin
- adds "\..\config" to the path to get the config directory resulting in "c:\dev\bin\..\config" - and then read the config file from there


and yes it works on 2008R2 - it is what I am using and all config files are environment specific and it works

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886