Solved

SSIS, using configuration settings in a Sql Server table

Posted on 2013-11-25
8
411 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
  • 4
  • 3
8 Comments
 
LVL 44

Assisted Solution

by:Rainer Jeschor
Rainer Jeschor earned 100 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 150 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 150 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now