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

Catching the Package Configuration Warning Expand / Collapse
Author
Message
Posted Wednesday, May 27, 2009 1:24 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:54 AM
Points: 554, Visits: 1,197
I'm using the indirect method of package configuration described here.
http://www.mssqltips.com/tip.asp?tip=1405


My question is how do I detect that my package failed to load a configuration such as the environment variable? Right now, if it fails to load a package configuration it throws a warning and runs with the design time settings. That could be very bad since the package is used in multiple environments. I tried configuring the OnError, OnWarning events at the package level, but they are not fired. Any suggestions as to how to stop the package from executing when a package configuration entry fails to load?

Warnings at design time.
Warning loading MyPackage.dtsx: The configuration environment variable was not found. The environment variable was: "SSIS_CONFIG_DB". This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid.


Warning loading MyPackage.dtsx: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed.


Warnings at run time.
Warning: 0x80019003 at MyPackage: The configuration environment variable was not found. The environment variable was: "SSIS_CONFIG_DB". This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid.


Warning: 0x80012059 at MyPackage: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed.
Post #724499
Posted Wednesday, May 27, 2009 1:35 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:54 AM
Points: 554, Visits: 1,197
I just found this on Connect. Please vote for it if you are affected by this issue.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127206


Also, I'm still looking for a viable work around. So far, I thinking of validating the Environment variable exists and hoping for the best on the rest.
Post #724516
Posted Wednesday, May 27, 2009 4:25 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:54 AM
Points: 554, Visits: 1,197
Jamie Thompson noted this as a problem as well and provided us a link to an MSDN forum discussion here.

http://blogs.conchango.com/jamiethomson/archive/2006/05/25/SSIS_3A00_-Warning-but-no-error-if-an-environment-variable-is-missing.aspx

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/1f7fe0db-a770-40e6-9e27-161af19e8c6f/
Post #724663
Posted Wednesday, June 10, 2009 10:51 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:54 AM
Points: 554, Visits: 1,197
After some thought I came up with the following solutions.

1. Turn a blind eye and hope for the best
2. Develop our own package configuration mechanism
3. Treat all warnings as errors
4. Manually modify before deployment
5. Create tool to run packages that can stop running if there is a config problem. (If you run a package through .NET you can discoverning package warnings and stop execution.) See http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/4be093e0-dfdf-490c-a718-25e0bc8074d6 for details.


For now I'm doing option 3 when possible and 1 if I cannot eliminate all the warnings from a package.
Post #732474
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse