March 8, 2011 at 8:10 am
I have a question about suppressing errors/warnings in SSIS packages:
I have a number of SSIS packages that I am in the process of standardizing. In this standard, there are two database connection managers defined for all the SSIS packages. The connection strings for these two databases are being held in a SQL Server table, and they both have the same ConfigurationFilter. This is so that I only have to specify the single ConfigurationFilter per package, and it will contain the configs for both database connection managers. The problem that I am encountering is that if I have that common config defined for a package that has only one database connection manager, the one that is not present in the package is causing the package to throw a warning and error on load (and at runtime). While it is not causing the package to fail - MS design requirements say that config should never break package - it is not condusive of successful development to put packages into production that are throwing warnings and/or errors.
Is anyone aware of a way to supress these errors and/or warnings when the packages are loaded? Thanks, everybody, in advance!
Karl Lambert
SQL Server Database Administration
Business Intelligence Development
March 9, 2011 at 12:46 am
This is a bit of a special case:
the error regarding the unsuccessful configuration is thrown before the actual package execution, since you are still configuring the package.
This has the consequence that the package will still run successfully and that no OnError event handlers will be called. However, if your package is configured with standard logging and the OnError event is enabled on the package level, error log messages are logged to the system table [dbo].[sysssislog].
(for some reason, if I run the package in BIDS, the error is logged twice, if I run the package on the server through a job, it is logged only once. Strange)
Anyway, my point is that it doesn't fail the package, but there is a trail. The warning is justified, as you are trying to configure something that isn't there. As far as I know, you can't surpress it, unless you disable the logging of the OnError event on the package level, but I'm afraid you'll surpress a little too much.
So, either you live with the warning/error, or you make a second configuration filter for the case when there is only one connection.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 10, 2011 at 7:40 am
Thanks Koen, that is pretty much the same conclusion that I came to.
We certainly do not want to disable logging, in fact, we are using logging quite extensively throughout our packages. The ultimate answer, I think is going to be using a filter with only the one DB connection.
The next thing I need to figure out is what is the difference between copying a .dtsx file to a file system location vs. saving a copy of the package to a file system location. For some reason, when we copy a dtsx file to a certain location, it doesnt always pick up the correct config. We have to open the package up to verify that it is using the correct configs.
Karl Lambert
SQL Server Database Administration
Business Intelligence Development
March 10, 2011 at 12:30 pm
SQLocity (3/10/2011)
Thanks Koen, that is pretty much the same conclusion that I came to.
Yeah, I can be a bit redundant sometimes 😀
SQLocity (3/10/2011)
The next thing I need to figure out is what is the difference between copying a .dtsx file to a file system location vs. saving a copy of the package to a file system location.
There is a difference? Maybe you can compare the xml files to see if there is an actual difference.
SQLocity (3/10/2011)
For some reason, when we copy a dtsx file to a certain location, it doesnt always pick up the correct config. We have to open the package up to verify that it is using the correct configs.
What sorts of configurations are you using?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 10, 2011 at 1:11 pm
We are using a SQL Server configuration. We have the env. variable pointing to an XML config that holds the SSIS database connection string, and the EnvType. For database connection managers, we have common configuration filter names for them. We select one configuration filter, and it contains all the configs for all the databases that we need. Same goes for certain path fragments. Then, we have variables that are common to the package.
I did find something peculiar today that makes me wonder... In the log (we have logging enabled too), it reads that the package had an issue reading from the config database. When this issue occurs, from what I understand, SSIS reverts back to the static data in the package to make the connection. This may be where my issue lies.
Also, the reason that I say there is a difference in copying a file from QA as opposed to saving a file there, is because of the static data. If you copy the file, it still has the last saved static data. If you run it and the config does not work, it will run using this static data. However, if you open the package when it gets to prod, it will load production configs, and hold that static. Save it, and if the config fails, it will use the new, production static data.
That begs the question though, if the configurations load when I open the package in production, then why would the package not be able to read the configs at run time?
Karl Lambert
SQL Server Database Administration
Business Intelligence Development
March 11, 2011 at 5:19 am
Why don't you store the connectionstring to the configuration table directly in the environment variable?
It is indeed correct that when package configurations do not succeed or are not present, that the "design time" values are taken.
Regarding your last question: if you open the package it makes a connection to the configuration database under your credentials. (unless you specified SQL Server authentication). So maybe the SQL Server Agent account doesn't have read access to the configuration database?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 11, 2011 at 8:39 am
Koen Verbeeck (3/11/2011)
Why don't you store the connectionstring to the configuration table directly in the environment variable?
It was desired to have the ability to update the connection string to the configuration database without having to update the environment variable.
Koen Verbeeck (3/11/2011)
Regarding your last question: if you open the package it makes a connection to the configuration database under your credentials. (unless you specified SQL Server authentication). So maybe the SQL Server Agent account doesn't have read access to the configuration database?
I think this is going to be my focus. We are executing the package via dtexec. I think in this case, the package will be executed by the user context that is set up to run the executable. This user is set up as an admin on the box, so I am curious if there is something that we are missing in the command line. It should be able to read the environment variable.
Karl Lambert
SQL Server Database Administration
Business Intelligence Development
March 11, 2011 at 10:55 am
I don't think using environment variables for most configs is a good idea. Perhaps one or two to point at the config server and database. My major opposition come from the issues changing the values and that they are not particularly visible.
I also think it is a bad practice to modify, in any way, a package that has been released from Dev. When you do, you can longer trust your testing, as the package has changed.
Also, I swear that there is a property at the package level to supress those warnings. At least in 2008 there is..
CEWII
March 11, 2011 at 2:12 pm
Elliott Whitlow (3/11/2011)
Also, I swear that there is a property at the package level to supress those warnings. At least in 2008 there is..
Well, look at that. Another hidden gem of SSIS I wasn't aware of: SuppressConfigurationWarnings.
SSIS never stops to amaze me 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 11, 2011 at 3:09 pm
Aha! I knew it... I just didn't have a SQL box nearby..
However, with all that said, I chose to never use it.. In my case I broke each connection down to its own xml file and only configured the package to use the connections applicable to it.. You might try seperating each connection off to its own identifier and then only configure the package to use the config data it needs.
CEWII
March 14, 2011 at 7:56 am
That may be where I go with it. While the common configuration may be convenient in the respect that we can indicate several configurations with one config filter, it is introducing a stigmata in putting packages in production that are throwing warnings. The warnings are not keeping the package from running successfully, but any time I see warnings on a package going into Production, I have issues, harmful to the package or not.
I will likely scale back to single configs, and give MS ideas on how to sculpt Denali to better handle this situation. I don't want to necessarily suppress ALL configuration warnings - some may be legit.
Karl Lambert
SQL Server Database Administration
Business Intelligence Development
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply