SSIS Deploy Indirect Configuration Issues

Topics: Resolved
Nov 3, 2008 at 10:37 PM

I have several SSIS packages that have their data connections configured through indirect XML configuration files (i.e. the path to the XML configuration is specified in a system-wide environment variable). In this scenario there are three systems that have been configured with the environment variables: development workstation, development SQL Server, and the production SQL Server. The development workstation and development SQL Server have the environment variable configured to point the connection strings to development data. The production SQL Server has the environment variable configured to point to production data.

 

Now, I use BIDS Helper to deploy the packages from the dev workstation to the various SQL Servers (i.e. I deploy to dev and test and later deploy to production after testing). This is handled through the Visual Studio project configurations and works as expected. After deployment I have the packages scheduled to run through a SQL Server Agent job. On dev the packages run as scheduled and the data is refreshed. However, on prod the job runs but the packages are not picking up the change in the indirect configuration. And, as a result, it loads using the paths specified on the workstation. Since the workstation and the dev server are configured properly no problem is noticed. But, it is a very noticeable problem in production.

 

If I do not use BIDS Helper to deploy the packages (i.e. I manually deploy them using the import package functionality in SSMS) the packages do pick up the indirect configuration and load the data as expected. Why would it make a difference when they are deployed using the BIDS Helper? Any help would be greatly appreciated. TIA!

Coordinator
Nov 5, 2008 at 3:11 AM
Hi, sorry, but I am having trouble reproducing this issue. Is there any chance that you are running into the issue with the lifetime of environment variables (see http://blogs.conchango.com/jamiethomson/archive/2005/10/31/SSIS_3A00_-Indirect-configurations-gotcha.aspx) where they are only read a process start up?

I have even deployed using both methods and then done a diff on the two files and the only thing that changed was the version GUID, so they should work identically if they were executed from the same process. If they were executed from different processes they could potentially have different values in the environment variable, specially if the variable has changed recently and you ran the process from somethig like SQL Agent, which typically stays up for long periods of time.
Nov 5, 2008 at 3:33 PM

Thanks for the response. I will put together a test to see if I am experiencing problems described by Jamie. However, in the meantime, I am able to reproduce the problem easily in our environment. These are the steps I take:

 

1.       Create a simple package with an OLE SQL Server data source

2.       On the workstation: Create a system-wide environment variable to contains the path to an XML configuration file (file contains connection info for a development database)

3.       On Server 1 & 2: Create system-wide environment variable (named the same as workstation version) pointing to a new path that contains production database connection string

4.       Configure the package to set the connection string of the data source using an indirect config with the above environment variable

5.       Run the package on workstation to verify that it works

6.       Use BIDS Helper to deploy the package to Server 1 (deployed to SQL Server storage)

7.       Use SSMS to manually deploy the package to Server 2 (deployed to SQL Server storage)

8.       Create a job on Server 1 & 2 to run the package

 

Now, Server 1 & 2 both have environment variables that point to the same configuration. However, Server 1 runs using the connection string that was configured on the workstation. And, Server 2 runs using the new connection string as referenced in the environment variable. I have tried this on a few different servers and the result is always the same. Maybe it is the same behavior that Jamie was seeing. However, it still seems strange to me that there would be a difference between the BIDS Deploy and Management Studio.

Coordinator
Nov 5, 2008 at 8:25 PM
Thanks, I will try to match those steps. What happens if you swap your deployment methods back and forth? Does the BIDS Helper deployed version always fail? Or once the package has worked after being deployed using SSMS does it always work? I would have expected that you would have needed to restart SQL Agent to get either package to read the new environment variable.
Nov 5, 2008 at 11:34 PM
It seems that once it works, it doesn't matter how I deploy changes to the package (i.e. I can deploy any future revisions of the package using the BIDS Helper method and it continues to work). I haven't tried changing the environment variable name to see if it makes a difference. It is something I can play around with a bit more. Thanks again.
Coordinator
Nov 6, 2008 at 2:26 AM
In the steps described above, was the XML configuration file already on server 1 (the server that BIDS Helper deploys to)? BIDS Helper doesn't deploy XML configuration files.
Nov 6, 2008 at 4:59 PM
Yes, the XML configuration files are stored on a network share. The environment variables store the path to the shared file. There are two shared paths: one for development and one for production. In the example above, the workstation is using the configurations that are for development. And, the two servers are setup with paths to production. The user that SQL Server Agent is using to execute the packages has access to the shares.
Coordinator
Nov 6, 2008 at 8:09 PM
It seems that once it works, it doesn't matter how I deploy changes to the package

This "smells" like it might be related to the enviroment variable caching issue that Jamie blogged about. I think that when you add or change a variable you would need to restart the SQL Agent service (if that is the root process that is running the packages) to get it to pick up the change. I'm not sure if SSMS has some way of causing SQL Agent to refresh itself, I got the impressions that this was just part of the nature of windows processes - that they got a cached copy of the environment variable when the process was spun up.
Nov 6, 2008 at 8:21 PM
I haven't yet had a chance to try restarting the agent to see if that resolves the problem. But, I will let you know when I do. I was under the impression that a new process would be created when the job kicked off. When I get a chance, I'll play with it some more. Thanks for your time!
Coordinator
Nov 6, 2008 at 10:55 PM
You are correct, a new process does get created when the job is kicked off, but I think this process inherits it's environment variables from the process which created it.

You can also see this if you start SSMS, then change a variable and then right click on a job and run it. Even thought the DTSExecUI program is spun up to run the package it still gets the original value of the variable. Restarting SSMS will pick up a fresh copy of the variables from the OS.
Sep 24, 2010 at 11:37 AM

Look at my custom solution here:"

https://sites.google.com/site/kuthuparakkalcom/downloads?pli=1