February 9, 2018 at 3:41 am
I'm not sure whether this thread is better placed under the SQL Server 2008 section or the integration services section under Data Warehousing. If I've chosen wrong, my apologies and please feel free to move it or tell me to repost.
Our set up is that we have a development folder with our SSIS packages in it that our under development. Some of our packages run other packages which will be in the same folder (we might also use sub folders in the future but it's not complex enough to merit that right now).
We have a deployed folder which mirrors the development folder and which sits directly alongside it. Unsurprisingly, this holds the finished versions of the packages which are run automatically by the SQL agent overnight.
We have a development DB and a live DB.
We want the versions in the development folder to run other packages in the development folder and/or connect to the development database. We want the versions in the deployed folder to run other packages in the deployed folder and/or connect to the live database.
We've set up variables in the packages to point to the relevant folder and to the relevant database. All pretty bog standard I'd have thought.
At present, when we want to roll something out we copy it from the dev to the deployed folder and change the values of the variables. I'd like to remove the need to change the variable values and using a config file seemed a pretty obvious way of going about it. However I just can't seem to make it work.
My first approach was simply to set up a config file and enter a relative path to the config file in the package. However, as soon as I save this get's resolved to an absolute path. Meaning that we'd still need to open up each package as we copy it from dev to deployed and change the path to the config file - so not much achieved there.
Then I read up in indirect configurations using an environment variable. That's not going to work for us either because the variable will only be able to point to one path. We could set up a second environment variable but we'll still need to open up the packages to change which environment variable they're using.
So how are we supposed to set up configurations in a set up like this? It doesn't feel like an unusual set up but seems to be completely unsupported. I should say, I don't have a lot of control over the setup so, although I'm happy for anyone to suggest changing that (I'd love to hear any suggestion that might move me toward a solution), it's likely I won't be able to adopt them. Really I'm looking for a way to make configurations work in our current setup.
Any ideas?
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply