SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Catching the Package Configuration Warning


Catching the Package Configuration Warning

Author
Message
Bradley Deem
Bradley Deem
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1248
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.

Bradley Deem
Bradley Deem
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1248
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.
Bradley Deem
Bradley Deem
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1248
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/
Bradley Deem
Bradley Deem
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1248
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search