April 19, 2009 at 4:21 pm
Hello,
I am trying to deploy few SSIS Packages from Dev to prod. I would like to know if there is an option with SSIS packages to be flexible so that when we migrate from development to production we don’t have to change the database connection strings in the package.
Thanks in Advance
-TGR
April 19, 2009 at 7:35 pm
You will want to utilize configurations and you can do this by storing them in a database or by using configuration files. Take a look at this other posting in the forum for advice and also references to best practice articles and a video for reference.
Database Connectionstring as a variable
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
April 20, 2009 at 6:42 am
I use package configurations and love it!
It takes a bit of setting up, though I use a template.
Couldn't be easier.
April 20, 2009 at 6:48 am
Yeah. Package configurations is the best way to deal it.
April 20, 2009 at 11:25 am
You can also store information in an environment variable on the server so your packages become environment independent. I've done this before where we had a 'tenant' database environment in which client db's could be anywhere and were managed via a config db. All the SSIS packages were built on a template that would automagically read the lcoation of this config db from the environment variable and assign the remaining connections accordingly.
Ben Sullins
bensullins.com
Beer is my primary key...
April 21, 2009 at 4:30 am
The thing with environment variables is when you change them you tend to need a reboot for it to come through.
Now, this could be our set up that makes us have to reboot. I’ve not tested it to extremes but with each environment variable change our system doesn’t appear to like it until a reboot has happened.
If anybody doesn’t share the same issue please reply so I can get our DBA’s on the case.
April 21, 2009 at 9:31 am
In my experience I've only had to restart the SSIS service, not the entire server. If you're developing locally and create a new environment variable to be used in a configuration I've only had to restart BIDS.
Try creating a package that writes the environment variable value to a file. Run it once, change the value, and run it again to see what you get.
Ben Sullins
bensullins.com
Beer is my primary key...
April 21, 2009 at 11:26 am
I agree. This has also been our experience.
I double-checked with my DBA to make sure and he agreed as well.
Reboot not necessary.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply