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.

Issues for 64 bit 2003 OS Server from Advanced 2000

Running Standard Edition SQL 2000 on Advanced Server with PAE enabled in the
boot ini (8 gig ram on Adv Svr but only two allocates for Standard)
System is migrating to a 64 bit Advanced Server with 8 GIG RAM... also
Standard Edition SQL 2000 which as we know well, can access only first two
GIG of RAM thus, I think, AWE is completely out of the question.
Is it necessary to enable PAE on the 2003 server? Are there issues that
should be considered for the 64 bit server running SQL 2000 that are
different from an instance running on Advanced Server 2000?
--
Regards,
Jamie
BOL
Configuration -Enabling AWE Memory for SQL Server (2000?)
http://msdn2.microsoft.com/en-us/library/ms190673.aspx
Configuration -Enabling Memory Support for Over 4 GB of Physical Memory
http://msdn2.microsoft.com/en-us/library/ms179301.aspx (2000?)PAE is only useful on 32 bit systems as X64 can access the memory directly
and does not require it. With SQL2000 Std you will only ever be able to use
2GB max. So why bother going thru these motions? Most of the 8GB is wasted
and you will reap none of the benefits of X64 in this state. I highly
recommend you upgrade to SQL2005 Std X64 edition otherwise this is most
likely a waste of your time.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:B1414628-6F34-46A7-83B8-6E8EFB4241F9@.microsoft.com...
> Running Standard Edition SQL 2000 on Advanced Server with PAE enabled in
> the
> boot ini (8 gig ram on Adv Svr but only two allocates for Standard)
> System is migrating to a 64 bit Advanced Server with 8 GIG RAM... also
> Standard Edition SQL 2000 which as we know well, can access only first two
> GIG of RAM thus, I think, AWE is completely out of the question.
> Is it necessary to enable PAE on the 2003 server? Are there issues that
> should be considered for the 64 bit server running SQL 2000 that are
> different from an instance running on Advanced Server 2000?
> --
> Regards,
> Jamie
> BOL
> Configuration -Enabling AWE Memory for SQL Server (2000?)
> http://msdn2.microsoft.com/en-us/library/ms190673.aspx
> Configuration -Enabling Memory Support for Over 4 GB of Physical Memory
> http://msdn2.microsoft.com/en-us/library/ms179301.aspx (2000?)
>|||A decision was made to wait for SP1 on SQL Server 2008. When that hits, we
upgrade.
Meanwhile...
--
Regards,
Jamie
"Andrew J. Kelly" wrote:
> PAE is only useful on 32 bit systems as X64 can access the memory directly
> and does not require it. With SQL2000 Std you will only ever be able to use
> 2GB max. So why bother going thru these motions? Most of the 8GB is wasted
> and you will reap none of the benefits of X64 in this state. I highly
> recommend you upgrade to SQL2005 Std X64 edition otherwise this is most
> likely a waste of your time.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:B1414628-6F34-46A7-83B8-6E8EFB4241F9@.microsoft.com...
> > Running Standard Edition SQL 2000 on Advanced Server with PAE enabled in
> > the
> > boot ini (8 gig ram on Adv Svr but only two allocates for Standard)
> > System is migrating to a 64 bit Advanced Server with 8 GIG RAM... also
> > Standard Edition SQL 2000 which as we know well, can access only first two
> > GIG of RAM thus, I think, AWE is completely out of the question.
> >
> > Is it necessary to enable PAE on the 2003 server? Are there issues that
> > should be considered for the 64 bit server running SQL 2000 that are
> > different from an instance running on Advanced Server 2000?
> > --
> > Regards,
> > Jamie
> > BOL
> > Configuration -Enabling AWE Memory for SQL Server (2000?)
> > http://msdn2.microsoft.com/en-us/library/ms190673.aspx
> >
> > Configuration -Enabling Memory Support for Over 4 GB of Physical Memory
> > http://msdn2.microsoft.com/en-us/library/ms179301.aspx (2000?)
> >
> >
>|||I'm primarily concerned that there may be pitfalls unknown when we do this
move from the old server to the new. It isn't by choice that we are putting
2000 on this new 64 bit server (16 Gig Ram - 8 Processors). Our production
server is failing and we are scrambling to get it fixed and running on the
new server ASAP. I agree that it would be wiser to go with 2005.
--
Regards,
Jamie
"Andrew J. Kelly" wrote:
> PAE is only useful on 32 bit systems as X64 can access the memory directly
> and does not require it. With SQL2000 Std you will only ever be able to use
> 2GB max. So why bother going thru these motions? Most of the 8GB is wasted
> and you will reap none of the benefits of X64 in this state. I highly
> recommend you upgrade to SQL2005 Std X64 edition otherwise this is most
> likely a waste of your time.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:B1414628-6F34-46A7-83B8-6E8EFB4241F9@.microsoft.com...
> > Running Standard Edition SQL 2000 on Advanced Server with PAE enabled in
> > the
> > boot ini (8 gig ram on Adv Svr but only two allocates for Standard)
> > System is migrating to a 64 bit Advanced Server with 8 GIG RAM... also
> > Standard Edition SQL 2000 which as we know well, can access only first two
> > GIG of RAM thus, I think, AWE is completely out of the question.
> >
> > Is it necessary to enable PAE on the 2003 server? Are there issues that
> > should be considered for the 64 bit server running SQL 2000 that are
> > different from an instance running on Advanced Server 2000?
> > --
> > Regards,
> > Jamie
> > BOL
> > Configuration -Enabling AWE Memory for SQL Server (2000?)
> > http://msdn2.microsoft.com/en-us/library/ms190673.aspx
> >
> > Configuration -Enabling Memory Support for Over 4 GB of Physical Memory
> > http://msdn2.microsoft.com/en-us/library/ms179301.aspx (2000?)
> >
> >
>|||Wow, must be nice to have wasted resources sitting around for 1 year +,
which is probably when you will be able to upgrade to SQL 2008, sp1. :)
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:B5A4ED33-8758-49C2-BFA3-5960066E5568@.microsoft.com...
>A decision was made to wait for SP1 on SQL Server 2008. When that hits, we
> upgrade.
> Meanwhile...
> --
> Regards,
> Jamie
>
> "Andrew J. Kelly" wrote:
>> PAE is only useful on 32 bit systems as X64 can access the memory
>> directly
>> and does not require it. With SQL2000 Std you will only ever be able to
>> use
>> 2GB max. So why bother going thru these motions? Most of the 8GB is
>> wasted
>> and you will reap none of the benefits of X64 in this state. I highly
>> recommend you upgrade to SQL2005 Std X64 edition otherwise this is most
>> likely a waste of your time.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:B1414628-6F34-46A7-83B8-6E8EFB4241F9@.microsoft.com...
>> > Running Standard Edition SQL 2000 on Advanced Server with PAE enabled
>> > in
>> > the
>> > boot ini (8 gig ram on Adv Svr but only two allocates for Standard)
>> > System is migrating to a 64 bit Advanced Server with 8 GIG RAM... also
>> > Standard Edition SQL 2000 which as we know well, can access only first
>> > two
>> > GIG of RAM thus, I think, AWE is completely out of the question.
>> >
>> > Is it necessary to enable PAE on the 2003 server? Are there issues
>> > that
>> > should be considered for the 64 bit server running SQL 2000 that are
>> > different from an instance running on Advanced Server 2000?
>> > --
>> > Regards,
>> > Jamie
>> > BOL
>> > Configuration -Enabling AWE Memory for SQL Server (2000?)
>> > http://msdn2.microsoft.com/en-us/library/ms190673.aspx
>> >
>> > Configuration -Enabling Memory Support for Over 4 GB of Physical Memory
>> > http://msdn2.microsoft.com/en-us/library/ms179301.aspx (2000?)
>> >
>> >
>>|||You are probably talking close to a year away for that. Oh well.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:B5A4ED33-8758-49C2-BFA3-5960066E5568@.microsoft.com...
>A decision was made to wait for SP1 on SQL Server 2008. When that hits, we
> upgrade.
> Meanwhile...
> --
> Regards,
> Jamie
>
> "Andrew J. Kelly" wrote:
>> PAE is only useful on 32 bit systems as X64 can access the memory
>> directly
>> and does not require it. With SQL2000 Std you will only ever be able to
>> use
>> 2GB max. So why bother going thru these motions? Most of the 8GB is
>> wasted
>> and you will reap none of the benefits of X64 in this state. I highly
>> recommend you upgrade to SQL2005 Std X64 edition otherwise this is most
>> likely a waste of your time.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:B1414628-6F34-46A7-83B8-6E8EFB4241F9@.microsoft.com...
>> > Running Standard Edition SQL 2000 on Advanced Server with PAE enabled
>> > in
>> > the
>> > boot ini (8 gig ram on Adv Svr but only two allocates for Standard)
>> > System is migrating to a 64 bit Advanced Server with 8 GIG RAM... also
>> > Standard Edition SQL 2000 which as we know well, can access only first
>> > two
>> > GIG of RAM thus, I think, AWE is completely out of the question.
>> >
>> > Is it necessary to enable PAE on the 2003 server? Are there issues
>> > that
>> > should be considered for the 64 bit server running SQL 2000 that are
>> > different from an instance running on Advanced Server 2000?
>> > --
>> > Regards,
>> > Jamie
>> > BOL
>> > Configuration -Enabling AWE Memory for SQL Server (2000?)
>> > http://msdn2.microsoft.com/en-us/library/ms190673.aspx
>> >
>> > Configuration -Enabling Memory Support for Over 4 GB of Physical Memory
>> > http://msdn2.microsoft.com/en-us/library/ms179301.aspx (2000?)
>> >
>> >
>>|||Sure there is always the unknown unless you test it first. It is a whole new
OS, drivers etc. Theoretically it should work fine as long as you have all
the proper drivers and are doing just basic database operations. But I have
to assume there are other apps on the same server correct? Otherwise that
is a lot of wasted hardware since 200 Std only supports 4 procs and 2GB of
memory.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:EC033C23-2758-42D5-9BA8-BB61DF2772D2@.microsoft.com...
> I'm primarily concerned that there may be pitfalls unknown when we do this
> move from the old server to the new. It isn't by choice that we are
> putting
> 2000 on this new 64 bit server (16 Gig Ram - 8 Processors). Our
> production
> server is failing and we are scrambling to get it fixed and running on the
> new server ASAP. I agree that it would be wiser to go with 2005.
> --
> Regards,
> Jamie
>
> "Andrew J. Kelly" wrote:
>> PAE is only useful on 32 bit systems as X64 can access the memory
>> directly
>> and does not require it. With SQL2000 Std you will only ever be able to
>> use
>> 2GB max. So why bother going thru these motions? Most of the 8GB is
>> wasted
>> and you will reap none of the benefits of X64 in this state. I highly
>> recommend you upgrade to SQL2005 Std X64 edition otherwise this is most
>> likely a waste of your time.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:B1414628-6F34-46A7-83B8-6E8EFB4241F9@.microsoft.com...
>> > Running Standard Edition SQL 2000 on Advanced Server with PAE enabled
>> > in
>> > the
>> > boot ini (8 gig ram on Adv Svr but only two allocates for Standard)
>> > System is migrating to a 64 bit Advanced Server with 8 GIG RAM... also
>> > Standard Edition SQL 2000 which as we know well, can access only first
>> > two
>> > GIG of RAM thus, I think, AWE is completely out of the question.
>> >
>> > Is it necessary to enable PAE on the 2003 server? Are there issues
>> > that
>> > should be considered for the 64 bit server running SQL 2000 that are
>> > different from an instance running on Advanced Server 2000?
>> > --
>> > Regards,
>> > Jamie
>> > BOL
>> > Configuration -Enabling AWE Memory for SQL Server (2000?)
>> > http://msdn2.microsoft.com/en-us/library/ms190673.aspx
>> >
>> > Configuration -Enabling Memory Support for Over 4 GB of Physical Memory
>> > http://msdn2.microsoft.com/en-us/library/ms179301.aspx (2000?)
>> >
>> >
>>|||I experienced a worse scenario. Listen to this:
I was setting up SQL Server Cluster on two DL580 of HPs with 4 Dual Core
CPUs and 16GB of RAMs... Nice config, yea?
There was a failed cluster node when we went to the scene. I mean, they were
using their SQL Servers for about 6 months...
Servers were x86 and non of the /PAE and AWE was being used. Even /3GB
wasn't used.
I went to the hospital's IT manager and told him about the situation. I told
him you have 16GB of RAM on your servers and your SQL Servers are using only
2GB of RAM out of 16GB and the rest of it is just sitting for months. And
guest what he said? He said, "I knew, but I didn't wanna touch it". Funny,
huh?
--
Ekrem Ã?nsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:B5A4ED33-8758-49C2-BFA3-5960066E5568@.microsoft.com...
>A decision was made to wait for SP1 on SQL Server 2008. When that hits, we
> upgrade.
> Meanwhile...
> --
> Regards,
> Jamie
>
> "Andrew J. Kelly" wrote:
>> PAE is only useful on 32 bit systems as X64 can access the memory
>> directly
>> and does not require it. With SQL2000 Std you will only ever be able to
>> use
>> 2GB max. So why bother going thru these motions? Most of the 8GB is
>> wasted
>> and you will reap none of the benefits of X64 in this state. I highly
>> recommend you upgrade to SQL2005 Std X64 edition otherwise this is most
>> likely a waste of your time.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:B1414628-6F34-46A7-83B8-6E8EFB4241F9@.microsoft.com...
>> > Running Standard Edition SQL 2000 on Advanced Server with PAE enabled
>> > in
>> > the
>> > boot ini (8 gig ram on Adv Svr but only two allocates for Standard)
>> > System is migrating to a 64 bit Advanced Server with 8 GIG RAM... also
>> > Standard Edition SQL 2000 which as we know well, can access only first
>> > two
>> > GIG of RAM thus, I think, AWE is completely out of the question.
>> >
>> > Is it necessary to enable PAE on the 2003 server? Are there issues
>> > that
>> > should be considered for the 64 bit server running SQL 2000 that are
>> > different from an instance running on Advanced Server 2000?
>> > --
>> > Regards,
>> > Jamie
>> > BOL
>> > Configuration -Enabling AWE Memory for SQL Server (2000?)
>> > http://msdn2.microsoft.com/en-us/library/ms190673.aspx
>> >
>> > Configuration -Enabling Memory Support for Over 4 GB of Physical Memory
>> > http://msdn2.microsoft.com/en-us/library/ms179301.aspx (2000?)
>> >
>> >
>>|||Definitely a case of fear paralysis. If they didn't NEED the performance
then it was just money sitting for nothing. But if they also had poor
performance then they could have actively been losing money due to the
slowness and that is when it is really a shame!!
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:2B1B5E4E-A07D-4D19-9D69-93F1E0C6ADA5@.microsoft.com...
>I experienced a worse scenario. Listen to this:
> I was setting up SQL Server Cluster on two DL580 of HPs with 4 Dual Core
> CPUs and 16GB of RAMs... Nice config, yea?
> There was a failed cluster node when we went to the scene. I mean, they
> were using their SQL Servers for about 6 months...
> Servers were x86 and non of the /PAE and AWE was being used. Even /3GB
> wasn't used.
> I went to the hospital's IT manager and told him about the situation. I
> told him you have 16GB of RAM on your servers and your SQL Servers are
> using only 2GB of RAM out of 16GB and the rest of it is just sitting for
> months. And guest what he said? He said, "I knew, but I didn't wanna touch
> it". Funny, huh?
> --
> Ekrem Önsoy
> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
> MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:B5A4ED33-8758-49C2-BFA3-5960066E5568@.microsoft.com...
>>A decision was made to wait for SP1 on SQL Server 2008. When that hits,
>>we
>> upgrade.
>> Meanwhile...
>> --
>> Regards,
>> Jamie
>>
>> "Andrew J. Kelly" wrote:
>> PAE is only useful on 32 bit systems as X64 can access the memory
>> directly
>> and does not require it. With SQL2000 Std you will only ever be able to
>> use
>> 2GB max. So why bother going thru these motions? Most of the 8GB is
>> wasted
>> and you will reap none of the benefits of X64 in this state. I highly
>> recommend you upgrade to SQL2005 Std X64 edition otherwise this is most
>> likely a waste of your time.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:B1414628-6F34-46A7-83B8-6E8EFB4241F9@.microsoft.com...
>> > Running Standard Edition SQL 2000 on Advanced Server with PAE enabled
>> > in
>> > the
>> > boot ini (8 gig ram on Adv Svr but only two allocates for Standard)
>> > System is migrating to a 64 bit Advanced Server with 8 GIG RAM... also
>> > Standard Edition SQL 2000 which as we know well, can access only first
>> > two
>> > GIG of RAM thus, I think, AWE is completely out of the question.
>> >
>> > Is it necessary to enable PAE on the 2003 server? Are there issues
>> > that
>> > should be considered for the 64 bit server running SQL 2000 that are
>> > different from an instance running on Advanced Server 2000?
>> > --
>> > Regards,
>> > Jamie
>> > BOL
>> > Configuration -Enabling AWE Memory for SQL Server (2000?)
>> > http://msdn2.microsoft.com/en-us/library/ms190673.aspx
>> >
>> > Configuration -Enabling Memory Support for Over 4 GB of Physical
>> > Memory
>> > http://msdn2.microsoft.com/en-us/library/ms179301.aspx (2000?)
>> >
>> >
>>
>

Issue cleaning up distribution database

I am not allowing anonymous subscribers of any kind, but I
do allow pull subscriptions.

>--Original Message--
>Have you enabled your publication for anonymous
subscribers? - in this case
>the transactions will hang around even if they've been
read until the max
>timeout.
>Regards,
>Paul Ibison
>
>.
>
Kartik,
it would seem that not all of your subscribers have synchronized. Try
running the distribution agent of each subscriber then the cleanup agent and
the transactions should be removed.
Regards,
Paul Ibison