July 28, 2010 at 9:34 am
Hello Everyone
Is there a way in SSIS 2005 or 2008 to dynamically determine the server name? I would like to use SQL Server "Package Configurations" to store the login information. I would use the box name in the config table, and then from the Package, depending on what the box name is, hence, the environment of DEV, QA or Prod, select the correct info from the config table
Hope that is not too convoluted, and makes sense. If anyone has any info on where I can read this, please send that.
Thank you in advance
Andrew SQLDBA
July 28, 2010 at 11:34 am
I have seen some multi server setups that all point to a single config server, and that config server uses the host_name() in the where clause, what it does is only return records FOR you from a a vew.
Clear?
CEWII
August 3, 2010 at 10:04 pm
I would do the following ...
1. use computer name environment variable at starting point of the tasks based on that assign set of variables from config to actual variables for further processing based on ur environment.
Assuming, your computername and DB server name is same (most of the cases its same)
if not same, create one (one time setup)
2. also, you can create one config file for each server (one time task), pointing to same folder in all servers, when you move ur package, you don’t need to change anything.
e.g, in dev, my config file is in D drive, and shud be same across all servers (including PROD)
when you move pkg, it will read that servers credentials only... in this case you dont need any ENV variables
3...
as EW suggested to use host_name, you need credentials to get host_name value from db, and credentials you are storing in config, so, i don’t think so, it will work for u.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply