July 28, 2009 at 8:32 am
I have SSIS package scheculed as a job and runs 2 am every night on production.
We want the same ssis package to run on Dev, QA, as well as production; therefore we want to update the connection string dynamically.
what is the best way to do it
Thanks,
July 28, 2009 at 8:45 am
Read up on package configuration. It's intended to be use for situations like this.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 28, 2009 at 8:48 am
THe short answer is Package configurations. Now the more tricky question is where to store them, there are several choices:
1. XML file
2. Environment variable
3. Registry
4. A Table in SQL
There is a 5th one but in this context it is not applicable, it is Parent package variable, which can be used for chained package execution where one package calls another and passes it some run-time information.
I personally tend to try and stay away from environmental variables, I think they are harder to use and really don't provide much benefit.
So, given all that, I recommend #1, the XML file. I chose a location that is common on all my servers such as C:\SSISConfig and use sub directories to segregate off config files for different projects or processes..
Clear?
CEWII
July 28, 2009 at 11:15 am
Thank you all, I will be doing it with XML file
July 28, 2009 at 1:48 pm
As I said I was using xml file configuration to make my package portable; but I an facing a problem
the connection string property of the OLE DB connection manager is not being pulled from the xml file I defined. It uses the the connection I had specified during design time.
How do I force the SSIS package to use the configuration specified in the xml file.
I've been spending a lot of time on this with no success. Please help..
July 28, 2009 at 2:06 pm
Take a good look at what you have done. What you are saying should not be happening.
Check to see if you have anything (tasks or expressions) that assigns the connection string after the package starts. That will overwrite the affect of the configuration file.
Check that the configuration file actually has the connection information for that connection manager.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 28, 2009 at 4:07 pm
I agree, if the config file contains the connection information and the package configuration is configured to use the file it WILL use them, that is what forces it too..
CEWII
July 29, 2009 at 7:35 am
I've not had problems with config file not being used. Just make sure that on top of creating the config file while editing the package, that you also choose that configuration file to be used when you set the job to run in the Agent.
For ease, you may even create 3 different config files and schedule the package to run back-to-back-to-back each time using a different config and see what kind of results you get from that.
July 31, 2009 at 6:02 am
washawgolla (7/28/2009)
As I said I was using xml file configuration to make my package portable; but I an facing a problemthe connection string property of the OLE DB connection manager is not being pulled from the xml file I defined. It uses the the connection I had specified during design time.
How do I force the SSIS package to use the configuration specified in the xml file.
I've been spending a lot of time on this with no success. Please help..
The package configuration is applied at the time the package is loaded. Therefore if you are designing your package and you edit your configuration file, or you change the specified file in your package, you will need to close the package and re-open it.
John
September 15, 2009 at 1:47 pm
I am faced with a situation where there are around 200 packages currently running in production but they all have hard-coded connection managers. Now they want to create configuration files for all of them. Is there a way to do this without opening each package? I have been googling this but have yet to come across any solution. Any help would be greatly appreciated.
September 15, 2009 at 4:03 pm
aye_taur (9/15/2009)
I am faced with a situation where there are around 200 packages currently running in production but they all have hard-coded connection managers. Now they want to create configuration files for all of them. Is there a way to do this without opening each package? I have been googling this but have yet to come across any solution. Any help would be greatly appreciated.
aye_taur ,
You should have started a new thread for your question.
I hate to tell you, but the only way you might be able to do this without opening each package is by using some external program that is designed for this. I can't say I know of any. Check codeplex.com for such a program.
Good luck and let us know if you find a program that can help.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 16, 2009 at 7:21 am
You could write VB.NET script\script task to read from db and update the connection string dynamically.. would this solve your problem?
September 16, 2009 at 8:56 am
Easier and cheaper to get an IT-literate temp in for a week, I reckon.
Sometimes the development effort for a one-off task such as this just isn't worth it, IMO. But sometimes the lure of learning something new is irresistible, regardless of cost/benefit 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply