Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

SSIS & Environment Variables Expand / Collapse
Author
Message
Posted Monday, August 09, 2010 8:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 26, 2012 11:11 AM
Points: 21, Visits: 79
I've been struggling w/ some quirky issues lately regarding use of environment variables with package configurations in my SSIS packages.

When does a package read the value of an environment variable?

We’ve got into a position where we need to regularly change the value of an env var. We were hoping a package references the value at runtime, but we thought we found that it was in fact when a package was deployed.
To make it worse, we’re finding that this may not even be the case. When the value of an env var is changed, sometimes the package picks it up, sometimes not, even after a redeploy.
This little problem is quite reproducible, and for the env var values that the change doesn't get picked up by the package, I've verified, as the service account that is running the packages, in the 3 places I know to look that the env value is in fact changed and correct in all three places it just isn't right in the SSIS package when it runs:
Places I checked:
1) My Computer>Properties>Advanced...
2) DOS "SET" command
3) The registry.

Is anybody else experiencing similar problems with Environment Variables?
Is there anywhere else I should be checking to see the current value of an env var?

I've got a lot more detail I can provide, but I was trying to keep things breif.
Post #966020
Posted Tuesday, August 10, 2010 4:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:08 PM
Points: 7,025, Visits: 6,194
How are you setting your environment variables?
Where do you store your values (table, XML Config file, etc.)?
What string are you using to call the package and what is the package calling instead of the values you us in the string?

There are two possible explanations to your problem. 1) Overriding variables or 2) missing config file.

1) I don't remember the exact details, but a few years back, we found out that SSIS re-applies certain variables after those variables are "called" in the SSIS execution string. This happened before we started using XML config files. Once we switched over to those, we seemed to have had no problems. Well, except for issue #2, that is. @=)

2) Say I have pkgA and I created it on my PC, creating a config file under the path C:\ConfigFiles\MyConfig.dtsconfig. Then I move it up to Production and call it using \\ProdServer\Production.dtsconfig. Now if Production.dtsconfig doesn't exist, SSIS will default to looking for the config file on my box (assuming it has permissions to read that folder). And if it doesn't work, then it will read the hard coded values in the package.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #966500
Posted Tuesday, August 10, 2010 4:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 5,074, Visits: 8,910
I seem to remember that when I tried using an environment variable in a package configuration, I had to restart the computer before SSIS would pick it up.

John
Post #966508
Posted Tuesday, August 10, 2010 8:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 26, 2012 11:11 AM
Points: 21, Visits: 79
Brandie, thanks for the reply. I view Environment Variables as Package Configurations when talking SSIS. So all Env Vars are package configurations, not all package configurations are environment variables. Other Package Configurations are XML config files, registry entries, Parent package variables, etc, all set up in BIDS under the SSIS>Package Configurations.
Environment Variable values are set on a particular machine under System Properties>Advanced Tab>Environment Variables Button>System Variables. I think you can set them using DOS commands and I guess you could edit them directly in the registry as well.

I'm calling the package from a SQL 2005 Job, w/ an SSIS step.
The package seems to be picking up the old value of the env var. Do to unfortunate circumstances, we have to switch the values of Env Vars a lot and we found that the value we changed from persists when a package is rerun.

We aren't able to reboot the server as often as we flip this env var around, in fact its a real big event to bounce a box, even a dev box.
And it really seems to be just one particular env var, one we use to store the name of a server. When we flip it to another server name, for some reason the value @ runtime is the old value. I made a bare bones package to simply report the value of this particular env variable and another one I just created brand new. I have two global vars in my package whose default values are just random strings.
I mapped my global vars to the env vars and in the package I slap them in to a third var via a script task then email myself that third var via an email task. I deploy my package, run via a job, note the values. I then change the two env vars' values, run the package via the job, note the values. I then redeploy my package, run via the same job, note the values. Results are very weird. From the first run of the package, my one troublesome variable seems to be passing a value not equal to the value of my env variable at the time I first deployed the package. I know this value as it is one of the values we switch among, but I don't recall the last time we had the env var set to this, not in recent past, but probably since the last bounce. For each of my runs of the package, this troublesome variable remains consistent coming across as the same "old" value. The "new" variable seems to work fine, the value I change it to comes to the package correctly only after I redeploy the package.

So I guess what we're finding is that the package doesn't retrieve the value of env variables at runtime, and sometimes it doesn't retrieve it at all. It doesn't make sense, but that's what we're working off of. Its either that or env variables have their values stored somewhere that isn't updated when I think they are and a reboot is in fact needed. I talked to one of our Windows Admins and he says that the value returned for a env variable when you type "SET" in to the DOS prompt is expected to be that actual/real value of the env variable. We're just not seeing that to be true.

My next step is to request a bounce of the server and see how that impacts things. But from what I'm reading is that a bounce is not necessary.
Post #966768
Posted Tuesday, August 10, 2010 8:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 5,074, Visits: 8,910
I've thought about this a little more and I think what is required is a restart of SQL Server, not the whole computer. This is because the service account picks up the values of the environment variables when it logs in, and to refresh them it needs to log in again. As far as I know, you can't do this without stopping SQL Server and starting it again.

John
Post #966776
Posted Tuesday, August 10, 2010 9:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 26, 2012 11:11 AM
Points: 21, Visits: 79
The database engine service or the SSIS service?
That still doesn't add up entirely, because I can get changes in env vars to make it thru w/ out any server or server bounces.
I'll try both DB engine and SSIS services before I do a box restart.
Post #966781
Posted Tuesday, August 10, 2010 9:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:08 PM
Points: 7,025, Visits: 6,194
John,

I can tell you from experience that restarting SQL Server will not help you with this problem. Your environment variables are getting overwritten by another setting. Probably within the package itself. It's an order of processing problem.

I wish I could remember the details. You might be able to google the issue though.

In my workplace, we often have to change connection settings for our packages. When environment variables didn't work, we went to XML config files. We have one for each of our environments and call the packages using those files. We haven't had a problem since.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #966785
Posted Tuesday, August 10, 2010 9:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 26, 2012 11:11 AM
Points: 21, Visits: 79
I searched the DSTX file of my package and no where is the value that is coming thru for my problem variable mentioned.
Something somewhere on the server I'm running this stuff on is storing the old value.

I've logged on and off of the server several times as the agent service account which is the one running my package in the job. Each time checking the value of my problem env var via command prompt > SET command.

I have seen posts mentioning "sessions" and bouncing a service would reset a session that may be holding on to an old value, and that would make sense if it was consistent.

We'll see if the service restarts work.
Post #966791
Posted Wednesday, August 11, 2010 12:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 5,074, Visits: 8,910
OK, here's what I've just tried (Windows XP SP2, SQL Server 2008 SP1 CU4).

- In Computer Properties, add a system environment variable called Test, value Test
- xp_cmdshell 'set' Result - new variable did not appear in result set
- Restart SQL Server
- xp_cmdshell 'set' Result - new variable appeared in result set
- In Computer Properties, change value of Test variable to Test1
- xp_cmdshell 'set' Result - variable still appeared with old value (Test)
- Restart SQL Server
- xp_cmdshell 'set' Result - variable appeared with new value (Test1)
- In Computer Properties, delete Test variable
- xp_cmdshell 'set' Result - variable still appears, with value Test1
- Restart SQL Server
- xp_cmdshell 'set' Result - Test variable no longer appears

Like Brandie, I avoid environment variables and use configuration files where possible.

John
Post #967706
Posted Wednesday, August 11, 2010 12:24 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:10 PM
Points: 955, Visits: 3,254
Jeff
If you are changing the values often, I would suggest that you use a SQL Table, and build a simple web page to be able to view or modify the values. XML Config files are good, but they require one to have access to the drive where they are stored. And they are nothing more than a simple text file, that can be deleted or modified incorrectly.

I have been using a SQL Table, and have that table in the SQL Maintenance database on each server, in each environment. Very simple and secure to manage, modify and read. Plus it gets backed up every day.

I stay away from environment variable like the plague. They do require a service bounce. They get read only when the service starts.

Andrew SQLDBA
Post #967732
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse