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 12»»

SSIS Common Configs Expand / Collapse
Author
Message
Posted Tuesday, March 8, 2011 8:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 17, 2014 1:16 PM
Points: 33, Visits: 453
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
Post #1074834
Posted Wednesday, March 9, 2011 12:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 13,539, Visits: 11,342
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1075357
Posted Thursday, March 10, 2011 7:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 17, 2014 1:16 PM
Points: 33, Visits: 453
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
Post #1076251
Posted Thursday, March 10, 2011 12:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 13,539, Visits: 11,342
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?




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

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1076516
Posted Thursday, March 10, 2011 1:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 17, 2014 1:16 PM
Points: 33, Visits: 453
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
Post #1076538
Posted Friday, March 11, 2011 5:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 13,539, Visits: 11,342
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1076785
Posted Friday, March 11, 2011 8:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 17, 2014 1:16 PM
Points: 33, Visits: 453
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
Post #1076938
Posted Friday, March 11, 2011 10:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #1077067
Posted Friday, March 11, 2011 2:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 13,539, Visits: 11,342
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




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

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1077165
Posted Friday, March 11, 2011 3:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #1077197
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse