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


SSIS Common Configs


SSIS Common Configs

Author
Message
the_SQL
the_SQL
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 526
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
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)

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


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
the_SQL
the_SQL
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 526
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
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)

Group: General Forum Members
Points: 110412 Visits: 13338
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 :-D

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?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
the_SQL
the_SQL
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 526
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
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)

Group: General Forum Members
Points: 110412 Visits: 13338
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?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
the_SQL
the_SQL
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 526
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
Elliott Whitlow
Elliott Whitlow
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43522 Visits: 5314
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
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)

Group: General Forum Members
Points: 110412 Visits: 13338
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 :-D


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Elliott Whitlow
Elliott Whitlow
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43522 Visits: 5314
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
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