Indirect Configuration of SSIS Connections not working after deployment
-
I have a very simple SSIS package containing two connection managers. One connects to the database, the other to SMTP to allow the sending of an email after the package executes a stored proc successfully. I set up the package to use indirect configuration for ease of deployment. I created a configuration XML file that contains the connection strings for the two connection managers, and created a system environment variable that points to the configuration file. I then deleted the configuration that points to the file, leaving only a configuration pointing to the environment variable in the package.
I have confirmed through testing that this all works as expected on my local machine: If I modify the connection information for the database connection in the configuration file, the SSIS package uses this updated information at execution time, rather than the original connection information in the connection manager in the package. (Verified by changing the server name in the configuration file to a server where the stored proc does not exist, thus forcing an error at runtime.)
The problem arises on deployment. After completing the configuration steps described in paragraph 1 above, I build the project to create a manifest file. I then ran the deployment utility using this manifest file to push the SSIS package to the server (deployed to sql server, not the file system). I confirmed that the config file was promoted to the desired location on the dev server as well. I then confirmed that I had the appropriate system environment variable created on the dev server, pointing to the correct location for the config file on the dev server. All looks fine. I ran the package on the dev server and it ran fine.
Now, I opened the instance of the config file on the dev server, and changed the database connection string to again point to a different server, one without my stored proc, to see if I could induce the same failure on execution of the package. The idea being to confirm that the deployed package is using the indirect configuration properly, looking to the environment variable on the dev server and picking up the new connection information from the config file. Instead of a failure at runtime, the ssis package still succeeded. It is still pointing at the dev server, rather than the new server indicated in the config file!
I have gone through everything to make sure that the config file, environment variable, and SSIS package are all configured correctly. I tried restarting the SSIS service after creating the environment variable, even tried rebooting the SSIS Server after creating the environment variable. Yet the deployed package just doesn't seem to be using the configuration file. It seems to be just using the original values that were coded into the connection manager objects in the package. What I can't figure out is why everything works fine locally on my machine, but does not work as expected after deployment. Have I missed a step?
Any assistance on this would be appreciated.
Thanks,
Joe
-
Frank,
A couple things you can check:
- The account that is running the package. Does it have permissions to access the directory containing the XML config file? Basic file-level stuff.
- If you are an Admin on the dev server? Try RDP-ing into the box and running it locally as an admin. That may provide another clue.
I've been sent down this path more times than I like to admit by SSIS. It doesn't fail on a config-file failure. It just uses the default settings.
I've never really like that feature - I'd rather have a failure.Have you looked into using the XML file to point to an "SSISConfig" database for all other connection settings? It has made a HUGE difference for me in pushing things from DEV --> TEST --> PROD. It's just a data change - no messing around with updating a (potentially huge) XML config file.
Hope this helps!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply