Is it possible to have multiple SSIS environments each pointing to their own database running on the same PC at the same time?

Question

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. Excluding the absolute path causes the exception "System.IO.FileNotFoundException: Could not find file 'C:\Documents and Settings\\DBConfiguration.txt'" - 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, DJ

Answer 1

When I worked in an environment that hosted dev and test on the same instance, here's how we solved it.

We used SQL Server configuration. All of our packages has a user variable named SYSDBConnection. The value of that variable was a connection string to our configuration database. Our packages always had an OLE DB Connection Manager, SYSDB, that was used for configuration and logging. The ConnectionString property on SYSDB was an expression that evaluated to @[User::SYSDBConnection] The effect of this was that the repository for all of our configuration data was controlled by that one variable.

The design-time values for all of our packages pointed to the dev instance. Packages in DEV generally run from the context of BIDS but could not be migrated to TEST until they had a successful execution recorded from within SQL Agent. From TEST forward, packages were always executed from the SQL Agent wherein we would provide a run-time value for the variable SYSDBConnection.

We had a good security practice there of not using the same service account for all of our SQL Server instances, thus TEST could talk only communicate with other servers in the TEST sphere of boxes. It helped identify packages that failed to use configuration or had a new configuration that also needed to be migrated. There were hiccups along the way and much grumbling but we never had an issue where a change got pushed to prod and someone missed a configuration and suddenly dev data is in prod. I've heard that horror story more than I should.

For your specific situation, you are not looking for SQL Agent only execution of the break-fix branch of code. Something's broken and you need to see what is amiss and resolve it. This approach will still work for you. In your SSIS project's property window, in the Debugging tab, there is a CmdLineArguments property available. Assign a value there that corresponds to your test environment. Something approximating this would suffice.

/set \Package.Variables[User::SYSDBConnection].Properties[Value];\"localhost\test"

I thought it worked out well.

An approach that HLGEM had was they used environment variables. The same variable name but associated to a a specific user (qa, stage, etc). While it's more user management than I care to deal with, I thought it was a clever approach and given the right situation, I'd run with it.

Answer 2

I think your situation is this: You have a package that is both in production and in development. While you are busy enhancing the dev version, the prod version needs to be debugged and fixed. In order to reproduce the bugs, you need to point your package to the prod databases.

If this is correct, then this is a branch-and-merge sort of issue that can be handled through version control - either through TFS (or some other versioning tool) or manually.

Not sure why have you retained the hard-coding and not made changes to use expression to create your connection string.