Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSIS, using configuration settings in a Sql Server table

Posted on 2013-11-25
8
Medium Priority
?
429 Views
Last Modified: 2016-02-11
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_Admin].Properties[ServerName]

However, when I also add in the ConnectionString, I get this:


ConfiguredValue: Data Source=sv-bisql-d8\SBI;Initial Catalog=SBI_ADMIN;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-Package-{886CDC64-A22C-4BBF-BC29-6A1ABF44D119}sv-bisql-d8\SBI.SBI_ADMIN;;
PackagePath: \Package.Connections[SBI_Admin].Properties[ConnectionString]

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.
0
Comment
Question by:Alaska Cowboy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 44

Assisted Solution

by:Rainer Jeschor
Rainer Jeschor earned 400 total points
ID: 39677824
Hi,
the issue is that the connection string itself is also a property - and the SSIS UI builds that from the infos of the other fields.
Therefore it is best practice to save both, the server name AND the complete connection string inside your configuration table.

HTH
Rainer
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39678586
Rainer, thanks.

>>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 ?
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 600 total points
ID: 39680029
You don't need to put both properties in the config table, just ConnectionString is sufficient.

Looking at your question, I assume you're not yet familiar with the term "indirect configuration"?  It can help to make your config more dynamic.  In short, you use either a configuration file or an environment variable to point your package to the location of the configuration table.  So the connection manager that's used for the configuration table is configured with the ConnectionString from the config file or environment variable.  This allows you to use different config tables for each environment (DEV/UAT/PRD).

More info: Best Practices for Integration Services Configurations
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39680581
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.
0
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 600 total points
ID: 39680656
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 ?

If your DBs don't have different names on different environments then yes, that would work too.

then going to test, copy all of above to test server, change values, move package there, and good-to-go

Exactly!  One thing to keep in mind when using the XML file tactic is that it needs to be located at exactly the same path in each environment, for instance c:\config\your_config.dtsconfig

If that's an issue then you should consider using an environment variable (which has its own disadvantage: sysadmins may not allow it - though I don't see why not).  I've used both methods with success...
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39680830
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 :-)
0
 
LVL 1

Author Closing Comment

by:Alaska Cowboy
ID: 39682361
great ! I'm off to testing now . . .
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39682823
I have enough knowledge now to give it a shot

So my mission has been accomplished! :)

Good luck with the project!

VV
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question