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

Problem with environment variable package configuration Expand / Collapse
Author
Message
Posted Friday, January 18, 2013 8:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 12:48 PM
Points: 2,818, Visits: 2,566
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,

Post #1408952
Posted Saturday, January 19, 2013 11:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:58 PM
Points: 7,135, Visits: 12,748
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
Post #1409217
Posted Saturday, January 19, 2013 11:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 12:48 PM
Points: 2,818, Visits: 2,566
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.
Post #1409220
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse