Issues with Logging option in SSIS

Hi,

I have enabled logging in my package and am using sql table to capture. i have defined a connection for it and i have defined it in the logging option. Once the logging is enabled, using package configurations, i am storing the value for the property "logging mode= 1", which means enabled in the table. But when i close and reopen the package, the package is failing to enable the logging. Even though i have stored the logging mode value in the configurations table, it is not getting enabled. Please help me solve this.

Workaround i have tried is declaring a variable explicitly to store the logging mode value and use it in the expressions of the pkg to define the logging mode. This variable is saved in the configuration table. This way works. but i want to know why it is not working with loggingmode value reading directly from configuration entries.

Vivek S

Does SSIS know to look for the loggingmode value, though? That is, in the package configurations, you have a SQL Server based configuration set to look for the correct filter that contains:

Code Snippet

<Configuration ConfiguredType="Property" Path="\Package.Properties[LoggingMode]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration>

|||

Hi Phil,

The config table has the value as

Configurationfilter Configuredvalue PackagePath Configuredvaluetype

pkgABC 1 \Package.Properties[LoggingMode] Object

Regards,

Vivek

|||

Vivek S wrote:

Hi,

I have enabled logging in my package and am using sql table to capture. i have defined a connection for it and i have defined it in the logging option. Once the logging is enabled, using package configurations, i am storing the value for the property "logging mode= 1", which means enabled in the table. But when i close and reopen the package, the package is failing to enable the logging. Even though i have stored the logging mode value in the configurations table, it is not getting enabled. Please help me solve this.

Workaround i have tried is declaring a variable explicitly to store the logging mode value and use it in the expressions of the pkg to define the logging mode. This variable is saved in the configuration table. This way works. but i want to know why it is not working with loggingmode value reading directly from configuration entries.

Vivek S

Vive, I was able to reproduce the issue. It looks like you cannot change the value of LoogingMode property via package SQL Server based configuration. The odd part is that when I used XML file configuration it worked fine.

BTW, I tested this in 9.00.1399.00 SS version. I wonder if this is a known issue or if it has been fixed on further SPs.

Vive, I would recommend you to search the SQL Server connect site to see if that issue has been reported before, if not fill in a bug report.

Can any one else validade this?

|||It kinda makes sense though. The package needs to know if it's going to log or not before reading package configurations -- after all, the package is logging already before reading package configurations. (as indicated by any package execution's log results)|||

Phil Brammer wrote:

It kinda makes sense though. The package needs to know if it's going to log or not before reading package configurations -- after all, the package is logging already before reading package configurations. (as indicated by any package execution's log results)

It does not make too much sense to me. Specially when it works fine if you use an XML file configuration, and with the other workaround described by the OP.

If would make more sense if we had a list of properties that are not meant to be configured at run time; or better yet the configuration wizard would prevent you of attempting it.

|||Well, it's all reproducible on my SP1 installation. Sigh...

Let's see what MS has to say. Perhaps they can clarify things a bit, because logging is obviously not the first thing that happens during package execution. (And I think many of us thought it was)

[Microsoft follow-up]|||

Hi,

Thanks for your suggestion. I couldnt find any item of such case in SQL server connect. so i have submitted it as a feedback.

well, the version i am using is SP1 applied.

thanks & regards

Vivek S

|||

Vivek,

Please post a link to the issue so others (including me can vote/validate it).

Does any of the SSIS folks have something to say?

Thanks

|||This is a bug and we have already fixed it in the upcoming 2008 release. All the user defined type properties (like LoggingMode, TransactionOption, etc) have the same problem when use with SQL configuration. Please contact CSS if you need a fix for SQL Server 2005.|||To be honest, the workaround (setting the package property using an expression of a configurable variable (e.g. "User::LoggingMode") worked for me and is probably perferred over a MS hotfix as long as the workaround is documented as an annotation (which I did).

Thanks for the workaround!|||I also faced this issue in SSIS and also used the workaround: a variable to store the value assigning it to the LoggingMode property using expression.

With the workaround I am facing a minor issue: running the package with the LoggingMode=2 (disabled) an empty log file still gets generated. This is not a big issue but I would prefer if this empty log file could be avoided.

Do you observe the same behavior? Any ideas on how to overcome this?

Thanks in advance.