Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem with environment variable package configuration


Problem with environment variable package configuration

Author
Message
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2954 Visits: 2629
I have a package where I set up a conection to a local database on my machine. I then made an environment variable package configuration for that connection. Here is the connection string in the environment variable:

Data Source=localhost;Initial Catalog=IT;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-IT

I then configured the same environment variable with the EXACT same name on a test server. Here is the connection string in the environment variable on the server:

Data Source=ALBSQL01;Initial Catalog=IT;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-IT

When I run the package in BIDS it runs fine pulling data from my local machine. When I deploy the package to the test server (stored package in the MSDB database) and then run it from the test server, it is connecting to my local machine rather than using the connection string in the environment variable. I disabled the db local to my machine and ran the package again on the server and it failed because it could not connect. Clearly it is not picking up the value from the environment variable but still looking at the original configuration. I rebooted both the server and my development machine between setting up the environment variable and defining the package to use the environment variable and deploying the package to the server.

So a couple of questions. First how does it use a "localhost" connection on the server and still attach to my local machine? Second, what am I doing wrong so that it does not pick up the connection string from the environment variable where the package is running?

Thanks,
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8263 Visits: 14368
Daniel Bowlin (1/18/2013)
So a couple of questions. First how does it use a "localhost" connection on the server and still attach to my local machine?

That is bizarre. Anything in the hosts file for localhost on the server?

Second, what am I doing wrong so that it does not pick up the connection string from the environment variable where the package is running?

Anything in the log about Package Configurations?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2954 Visits: 2629
Thanks for your response. I did work out the issue but forgot to post back here.

Although I had deployed the package to the server, and I was right clicking on the package on the server, I was doing it from a local instance of SSMS connected to the server. Apparently when you run a package that uses an environment variable, the environment variable is read from the system where the package is being called from. In other words since my SSMS was on my laptop, it was reading the environment variable from my laptop. When I logged onto the server, and opened an instance of SSMS there and ran the package it worked as expected.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search