Problem with environment variable package configuration

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Problem with environment variable package...

Problem with environment variable package configuration

Rate Topic

Display Mode

Topic Options

Author

Message

Daniel Bowlin

Posted Friday, January 18, 2013 8:44 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:49 AM
Points: 2,672, Visits: 2,417

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

opc.three

Posted Saturday, January 19, 2013 11:18 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 6,713, Visits: 11,747

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1409217

Daniel Bowlin

Posted Saturday, January 19, 2013 11:48 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:49 AM
Points: 2,672, Visits: 2,417

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 »

Permissions