April 6, 2011 at 11:25 am
Good afternoon. In developing an SSIS package, I configured it to use package configuration and store its configuration values in the SQL Server. The connection manager that I used for the SQL Server package configuration is the same connection manager used by other tasks in the package. I set-up this connection manager's connection string to be a configurable value. In deploying the package to a SQL Server instance and scheduling it as a job, I know that when the job runs, the SSIS package will try to use the connection string information given it at design-time. I also know that I can overcome this by modifying the command line of the SQL Agent job using the "/connection" switch. Since the connection manager is the same for configuration and other tasks in the package, will the other tasks use the overridden value or will they still read the original value from the database table?
Furthermore, is this bad practice? Should I use two separate connection managers -- one for the configuration connection and one for the other tasks, even though they point to the same database and server?
Any help is greatly appreciated.
Thanks...Chris
April 6, 2011 at 2:17 pm
CGSJohnson (4/6/2011)
Good afternoon. In developing an SSIS package, I configured it to use package configuration and store its configuration values in the SQL Server. The connection manager that I used for the SQL Server package configuration is the same connection manager used by other tasks in the package. I set-up this connection manager's connection string to be a configurable value. In deploying the package to a SQL Server instance and scheduling it as a job, I know that when the job runs, the SSIS package will try to use the connection string information given it at design-time. I also know that I can overcome this by modifying the command line of the SQL Agent job using the "/connection" switch. Since the connection manager is the same for configuration and other tasks in the package, will the other tasks use the overridden value or will they still read the original value from the database table?Furthermore, is this bad practice? Should I use two separate connection managers -- one for the configuration connection and one for the other tasks, even though they point to the same database and server?
Any help is greatly appreciated.
Thanks...Chris
While I don't know the answer to your first question, I'd say you should use two separate connection managers. Until you find your answer for your first question, you should use separate connection managers simply because you don't know if altering one will affect the other.
I'd also say you should use two separate connection managers because they have two different desired behaviors. One you want to be able to change and the other you don't want to be able to change. having separate connection managers means you'll never accidentally change the second connection.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 6, 2011 at 5:23 pm
Thanks for the response.
April 6, 2011 at 11:42 pm
Ai ai ai. This made my head hurt. 🙂
This link may help if you're used to how 2k5 works for SSIS configs and commandline settings: http://msdn.microsoft.com/en-us/library/bb500430.aspx
CGSJohnson (4/6/2011)
In developing an SSIS package, I configured it to use package configuration and store its configuration values in the SQL Server. The connection manager that I used for the SQL Server package configuration is the same connection manager used by other tasks in the package.
Time out here. To confirm: You have a single connection setup at the bottom pointed at some DB. Both your configuration table and your data are in this database?
I set-up this connection manager's connection string to be a configurable value.
So you get your configuration via the configuration? This is why my head hurts.
In deploying the package to a SQL Server instance and scheduling it as a job, I know that when the job runs, the SSIS package will try to use the connection string information given it at design-time.
Errr, yes, and no. It'll apply design time configs, yes. Then the command line configs, then it will reapply the configurations (in 2008) so if you changed what configuration to look at it will change the loadup of those settings.
I also know that I can overcome this by modifying the command line of the SQL Agent job using the "/connection" switch. Since the connection manager is the same for configuration and other tasks in the package, will the other tasks use the overridden value or will they still read the original value from the database table?
The entire connection will be moved, so your data AND your configuration read because they're the same connection will be shifted.
Furthermore, is this bad practice?
Yes.
Should I use two separate connection managers -- one for the configuration connection and one for the other tasks, even though they point to the same database and server?
Yes.
Typical chain of events you want for configurations: Setup connection for configurations. Load said connection via environment variable (by preference) so that as you move between D/Q/P environments you don't have to adjust much if anything. Load your data into your configuration table in that predefined database. Normally it's a dedicated database just for configurations and package usage. Now setup the rest of your connections with configurations as you normally would, storing through the preset connection.
Anything else is going to drive you absolutely batty later as you try to manipulate the configuration.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 7, 2011 at 10:01 am
🙂 Sorry for the headache, Craig. I appreciate the response, in spite of the headache.
If your head hurt, then SQL Server must be scratching its head too! Never a good thing...to confuse a computer.
My responses to your comments/questions are below:
1. Yes, I have one connection manager, pointed to a database, and both my configuration table and data tables are in that database.
2. In the current set-up, yes, I receive my configuration via the configuration, which I am over-riding with the "/connection" switch. I know that it is common practice to use environment variables to hold connection strings in differing environments, but it will be very difficult to have that request accommodated at my employer. Using the "connection" switch seemed easier and less painful (for me).
3. If the configuration and data tables will be read (the final read) from the information provided in the "connection" switch, and not the information stored in the database, then everything is OK. I am just verifying this point. I understand your point, as well, to separate these two connections -- the config connection and the data tables connection. This seems odd, since they both refer to the same server and database.
Again, thanks for the response. I appreciate it.
Chris
April 7, 2011 at 11:27 am
CGSJohnson (4/7/2011)
3. If the configuration and data tables will be read (the final read) from the information provided in the "connection" switch, and not the information stored in the database, then everything is OK.
Everything is NOT okay. Your command line switches will be re-overridden by the configuration. This change was to allow you to change configurations via commandline and still have all the configuration pieces work. If it's in a configuration, you cannot change it via commandline, unless it's the where/which configuration attributes you're modifying.
I am just verifying this point. I understand your point, as well, to separate these two connections -- the config connection and the data tables connection. This seems odd, since they both refer to the same server and database.
That's for when they don't, because some admin somewhere decides to get fancy and standardize everyone so all his configurations are centrally located... like most folks do. 🙂 A central configuration repository means that if I have database MonstersInc and I call the configuration Boo, every package out there can use Boo and if I ever have to move the database I can affect all the packages in a single step.
Your method I'd have to figure out where all the configurations are stored, then start one by one'ing the changes. That's why it's non-standard practice. Too many things overlapping can make it hard to work with long term.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 7, 2011 at 12:23 pm
I'm sorry...I don't know why I can't get clarity on this situation, but I guess I am not understanding the following statement correctly.
"...After the utility applies the run-time options, the utility reloads the design-time configurations from the new location..."
Doesn't the last sentence say that SSIS will try to locate design configurations in the new location given? And since I am over-riding the configuration location with the new location, won't it read from the actual location that I want?
Is the "/connection" switch on par with the "/set" statement? I know that in SQL 2008, which is what I am using and which I should have mentioned earlier, the "/set" statement is over-ridden by the configuration (thanks SQL Saturday 70 in Columbia and Andy Leonard).
As for the centralized configuration database, if the SQL Servers are on disparate networks and cannot talk to each other, this means that you must maintain this central configuration database in each network, right?
Thanks for your help with this, Craig
April 7, 2011 at 12:33 pm
CGSJohnson (4/7/2011)
I'm sorry...I don't know why I can't get clarity on this situation, but I guess I am not understanding the following statement correctly."...After the utility applies the run-time options, the utility reloads the design-time configurations from the new location..."
Doesn't the last sentence say that SSIS will try to locate design configurations in the new location given? And since I am over-riding the configuration location with the new location, won't it read from the actual location that I want?
Is the "/connection" switch on par with the "/set" statement? I know that in SQL 2008, which is what I am using and which I should have mentioned earlier, the "/set" statement is over-ridden by the configuration (thanks SQL Saturday 70 in Columbia and Andy Leonard).
As for the centralized configuration database, if the SQL Servers are on disparate networks and cannot talk to each other, this means that you must maintain this central configuration database in each network, right?
Thanks for your help with this, Craig
If I may ask for a clarification on this, why are you overriding the configuration?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 7, 2011 at 12:54 pm
Hi, Stefan. I want to over-ride the configuration because I am deploying the SSIS package in a different environment (moving from DEV to QA, QA to STG, etc.).
Thanks...Chris
April 7, 2011 at 12:59 pm
CGSJohnson (4/7/2011)
Hi, Stefan. I want to over-ride the configuration because I am deploying the SSIS package in a different environment (moving from DEV to QA, QA to STG, etc.).Thanks...Chris
I'd suggest your best option in that case would be to go into the SSIS package and change the connection configuration for the package in question.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 7, 2011 at 2:32 pm
CGSJohnson (4/7/2011)
Doesn't the last sentence say that SSIS will try to locate design configurations in the new location given? And since I am over-riding the configuration location with the new location, won't it read from the actual location that I want?
Yes. But wouldn't you be better off just setting that in the first place per environment with an environment variable, and using that to inform the configuration where to look for that environment's?
Is the "/connection" switch on par with the "/set" statement? I know that in SQL 2008, which is what I am using and which I should have mentioned earlier, the "/set" statement is over-ridden by the configuration (thanks SQL Saturday 70 in Columbia and Andy Leonard).
Yes, yes it is.
As for the centralized configuration database, if the SQL Servers are on disparate networks and cannot talk to each other, this means that you must maintain this central configuration database in each network, right?
Thanks for your help with this, Craig
No problem. Yes, disparate networks would mean different storage locations. But if you're on different networks then the different machines will have their own environment variable pointing you at the correct location to get the rest of the configuration information (like what server/db is the data I want for dataflow2 on?). This is all about standardization and maintenance rather then versatility. If job on x machine failes its configurations were stored at y. Not every (or even many) admins are up to date on version mechanic differences between overrides.
It should be a last case scenario (in my mind), not a primary coding mechanism.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 7, 2011 at 3:47 pm
Hi, Craig. I know that suggested practice is using environment variables, but that is not an option right now. It would be hard for me to convince the powers to be to use them. I'll look at alternatives.
Thanks...Chris
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply