August 9, 2010 at 8:59 am
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.
August 10, 2010 at 4:17 am
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.
August 10, 2010 at 4:28 am
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
August 10, 2010 at 8:53 am
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.
August 10, 2010 at 8:59 am
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
August 10, 2010 at 9:03 am
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.
August 10, 2010 at 9:07 am
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.
August 10, 2010 at 9:18 am
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.
August 11, 2010 at 12:07 pm
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
August 11, 2010 at 12:24 pm
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
August 11, 2010 at 12:26 pm
I appreciate that... thank you.
xp_cmdshell was the hint I needed...
I ran that and low and behold there's that value that keeps haunting me.
Bouncing the SQL Service was my next step, was going to do it first thing this morning before everybody got to work but my boss told me I had to have the Windows admins do it, even though its dev.
I'll get to work on scheduling the SQL bounce.
I think Environment Variables are great if you don't have to ever change them. And that was our intent when we first started using them.
The thing w/ Config files is that the deployment utility tries to package them up and if the DBA doesn't hack the Manifest to not push the config file, you overwrite your prod config. We use config files for data related variables. But we don't want app devs even knowing where their production SQL server is. Ideally using environment variables to store SQL Server names would let us (DBAs) promote app dev developed pacakges to QA and Prod with out having to change a single line of code or config file. But since we hosed our QA SSIS server and could run pacakges from it anymore, our work around was to run QA and dev pacakges from our dev SSIS server, hence the env var value shell game.
Anyway... thanks for the help gang. I'll follow up once I get the SQL service restarted
January 4, 2013 at 10:46 am
Hi Andrew,
I have a problem in using SQL table for package configuration.Let me explain in detail.
I have the task Flatfile source---->(correct output) to oledb destination
Flatfile source---->(error output) to flatfile destination
This is the package task.
Now I have Servername,Databasename,flatfilesource name,flatfile destination name
These are all the connection strings which are placed in SQL table(SSIS_Configurations)
But when I need to change the database name and flatfilesource name,I updated at the backend SQL table((SSIS_Configurations).
But when I open the package it is still pointing to the old database and flatfilesource name.
How should I proceed further?? It's very urgent.Please help.
Thanks in advance.
-Nisha
January 4, 2013 at 11:23 am
When you save your package from BIDS, the values you have for all properties are saved in the DSTX.
If you have package properties stored in your SSIS Config table, those package properties are overwritten at runtime by the values stored in the table... The DSTX stored in MSDB, or wherever, never changes. So when you pull your package down from your server, you are seeing what was deployed, which was the DSTX you saved from BIDS.
Part of the Runtime, pre-execution phase of your SSIS pacakge is to step thru your various package configurations substituting the property value in the package with that stored in your SSIS Config table, for example.
Are you finding that when your package runs, your select properties aren't being updated to your table stored values?
January 4, 2013 at 11:28 am
Thanks for your reply..
If I need to change the Db name and Flatfile source name to the package (which is not deployed yet)
can I update the SSIS_config table at the back end???
I tried it by updating it at the back end,but when I opened the package I need to do the changes manually,So when I do it manually in BIDS te table is updated.But when I update at the back end its not reflecting in BIDS.
So what should I do to do that automatically.Because I have 100 packages which needs to be changed often.(i.e; Database name and Flatfile source name)
January 4, 2013 at 11:43 am
My knee jerk reaction is to say "Deploy your packages". Just create a dev folder on your SSIS server and plop them in there. Its pretty easy to deploy 100s of packages using the deployment utility.
Any reason I can think of to not deploy them and run in design mode in BIDS is negated by your volume of packages.
even so... Running in design mode mimics runtime, so if you are in BIDS, you can see that your Connection is pointed @ Server ABC and you know that your package config is set to server XYZ, then wen you click Run in BIDS, Pre-Execution will go pick up the value from your config table and use it.
What I'm not sure about tho is, since you are running in design mode, if you DSTX that is open in BIDS gets overwritten with the config table values. So in the case of the above example, after completion is your packakge that you have open in BIDS showing server XYZ, or is it still ABC?
I can't remember.
If the above doesn't mae sense or does't work out... you can put your 100+ DSTX files in a folder and user a text editing tool do a masive Find/Replace on server ABC changing it to XYZ, then open each of those DSTXs and run as you normally do.. But I advise against tinkering w/ DSTX directly.
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply