Solved

Setting a SQL Server based environment variable

Posted on 2012-03-12
4
190 Views
Last Modified: 2012-06-07
I am attempting to set an environment variable to point to a SQL Server connection in SSIS.  I have been looking online for an example of this, but to no avail.  After successfully setting an environment variable to an XML file, I thought it would be straightforward to set one to a SQL Server connection, but I think I’m doing something wrong.

I am experimenting with this and creating as simple an example as possible.  I have a table in AdventureWorks that contains the configurations I want (see below).  I have two Configuration Filters: SetVar_Hello and SetVar_Goodbye; and they each set the value of a variable test to “Hello” and “Good Bye”, respectively.

I created two environment variables (Control Panel / System / Advanced / Environment Variables / New), with these values:
SSIS_Config_Goodbye =
"CUSTOMIZE.AdventureWorks";"[dbo].[SSIS Configurations]","SetVar_Goodbye";

SSIS_Config_Hello =
"CUSTOMIZE.AdventureWorks";"[dbo].[SSIS Configurations]","SetVar_Hello";

The values of these I took from the Configuration String when I set the filters directly.  I include an image of the setup for setting the environment variable.  My expectation is that when I change the environment variable, the value of the variable test will toggle between “Hello” and “Good Bye”.  What happens instead is that the variable value doesn’t change.  I’d like to hear from anyone who has successfully set this up; and, hopefully, let me know what I’ve done wrong.

And extra credit: when I set a SQL Server variables directly, I find it disconcerting that the old settings I had go away, and the whole thing starts over.  The behavior appears to me a bug, sacrificing previously set values when coming back to the same filter.  If there is a way to keep the old values from going away, that would be great!

Sincerely, ~Peter Ferber
Config-Table.bmp
SQL-Server-ByEnvironmentVar.bmp
0
Comment
Question by:PeterFrb
  • 3
4 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37715519
You should restart your BIDS after you've created your variable, otherwise it won't be known.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37715599
BTW: to set a connection string through an environment variable, you can just select the ConnectionString property of the connection manager when setting up the configuration (Package Configuration Wizard > Select Target Property page).

The above assumes that your environment variable contains a valid connection string.  To make sure it's correct, copy it from the ConnectionString property as well.
0
 

Author Comment

by:PeterFrb
ID: 37715833
I've attached a screenshot of both a indirect and direct configuration.  Having made the direct configuration, I reproduced the Configuration String value (as I can't copy the string, I had to retype it) and made that the value of my environment variable.  To adequately test, I closed and reopened the project, to no avail.  I've even experimented with warm and cold boots (Logging off and back in; restarting the computer), which still makes no difference.  I don't yet know why my environment variable is failing.

Thanks for the feedback, from which I always gain value.  I trust that I will eventually crack this case.  ~Peter

A direct and indirect configuration type
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 37719656
Hmm, I think there's a misunderstanding of concepts here.  In particular, a "configuration string" is something totally different than a "connection string".

To get a view of a connection string, select the Connection Manager that points to the database containing your configuration table.  Then locate the ConnectionString property in the Properties pane.  The string that you see there is the value that you should give your environment variable.

What you seem to have done is used the Configuration String from the Package Configs, that's not how this works. (as you've noticed)

Here's a more visual explanation: http://www.jasonstrate.com/2011/01/31-days-of-ssis-environmental-variable1731/
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

838 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