INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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.

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

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close