Problem with environment variable package configuration

  • 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,

  • 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

  • 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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply