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:
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