SSIS, using configuration settings in a Sql Server table
I'm wrestling with configuration files using a Sql Server table. I understand the basics but need some further understanding.
I started with Config files and only selected those things that might change (e.g., the server name in a Sql Server connection info setting). Then I moved to d.b. tables with the same approach - only select those items that change.
So for a Sql Server connection, I chose the server name, and I got this in the Sql Server connection d.b., this looks perfectly legit to me:
ConfiguredValue: sv-bisql-d8\SBI
PackagePath: \Package.Connections[SBI_A
However, when I also add in the ConnectionString, I get this:
ConfiguredValue: Data Source=sv-bisql-d8\SBI;Ini
PackagePath: \Package.Connections[SBI_A
So there's overlapping info in both settings (sv-bisql-d8\SBI) . . . then why is there a separate option for ServerName ? I thought by choosing server name that would be enough, but if I did so, would that handle the same info in the connection string ?
Hope this makes sense.
I started with Config files and only selected those things that might change (e.g., the server name in a Sql Server connection info setting). Then I moved to d.b. tables with the same approach - only select those items that change.
So for a Sql Server connection, I chose the server name, and I got this in the Sql Server connection d.b., this looks perfectly legit to me:
ConfiguredValue: sv-bisql-d8\SBI
PackagePath: \Package.Connections[SBI_A
However, when I also add in the ConnectionString, I get this:
ConfiguredValue: Data Source=sv-bisql-d8\SBI;Ini
PackagePath: \Package.Connections[SBI_A
So there's overlapping info in both settings (sv-bisql-d8\SBI) . . . then why is there a separate option for ServerName ? I thought by choosing server name that would be enough, but if I did so, would that handle the same info in the connection string ?
Hope this makes sense.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ValentinoV, thanks !
>>You don't need to put both properties in the config table, just ConnectionString is sufficient.
- but I was hoping to just use ServerName, as it's a lot simpler. Would that work and the Connection string is built from that ?
>>I assume you're not yet familiar with the term "indirect configuration"?
- actually, yes :-) So I'd use an XML file to point to the table where the configurations are, so when going from dev to test to prod, I only have to change the XML file to point to the proper table in the environment. But when I first move the settings from dev-table to test-table, I have to move the rows, then update the values, right ?
so here's my model
- XML points to Sql-Server-Admin-DEV server-config table to get config settings
- config table has two rows,
1. connection string for Oracle-dev
2. connection string for Oracle_2-dev
3. connection string for Sql Server control table, sv-bisql-d8\SBI
then going to test, copy all of above to test server, change values, move package there, and good-to-go . . .
and I was hoping that instead of putting in the long connection string, just use server name.
>>You don't need to put both properties in the config table, just ConnectionString is sufficient.
- but I was hoping to just use ServerName, as it's a lot simpler. Would that work and the Connection string is built from that ?
>>I assume you're not yet familiar with the term "indirect configuration"?
- actually, yes :-) So I'd use an XML file to point to the table where the configurations are, so when going from dev to test to prod, I only have to change the XML file to point to the proper table in the environment. But when I first move the settings from dev-table to test-table, I have to move the rows, then update the values, right ?
so here's my model
- XML points to Sql-Server-Admin-DEV server-config table to get config settings
- config table has two rows,
1. connection string for Oracle-dev
2. connection string for Oracle_2-dev
3. connection string for Sql Server control table, sv-bisql-d8\SBI
then going to test, copy all of above to test server, change values, move package there, and good-to-go . . .
and I was hoping that instead of putting in the long connection string, just use server name.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ValentinoV, excellent ! I have tested this out in one environment but need to run the full cycle to test-prod, I have enough knowledge now to give it a shot :-)
ASKER
great ! I'm off to testing now . . .
I have enough knowledge now to give it a shot
So my mission has been accomplished! :)
Good luck with the project!
VV
So my mission has been accomplished! :)
Good luck with the project!
VV
ASKER
>>the SSIS UI builds that from the infos of the other fields.
- doesn't that imply that I can specify server name and the connection string will use this value ?
>>it is best practice to save both, the server name AND the complete connection string
- but why choose both ? It seems to me like one or the other works, and the simpler approach is ServerName.
Doesn't the connection property build itself from the Server Name ? I can certainly test but wanted to check.
-------------
Also, here's a related question (in regards to migrating from dev to test to prod) - if one did use the Connection string in a config settings table, then when migrating to test / prod, one has to migrate the dev values to test, then write an update statement to make the setting applicable to the new environment, no ? Surely one is not going to muck around with the dtsx package and build the config table entries in each environment ? My expectation is don't touch the code, just the config settings, which implies update statements (at least when migrating for the first time).
- Also, if I was in test environment but wanted to pull from Prod, I'd write an update statement to the config entry to point to Prod, no ?