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.

Issue with Logging using SQL Server

Hi All,

I'm trying to implement the SQL Server logging in my package but Im receiving a very weird error message. Follow below:

"Error at Consumer_Common_Transportation [Log provider "SSIS log provider for SQL Server"]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.

Error at Copy Data to TCARRIER: The SSIS logging provider "SSIS log provider for SQL Server" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.

(Microsoft.DataTransformationServices.VsIntegration)"

I know that the sysdtslog90 table exists ( with no rows ), but i cant understand why this process doesnt includes rows/data in this table.

I also have the Text file logging in this package and it is working fine. I just need to fix this issue to move it to production.

Does anyone knows what is happening? Is there any property or flag that i need to change?

Thanks in advance for your help.

Regards,

Thiago

Hi, when do you get this message? When the package executes or as you are trying to add the log provider?
Can other packages log data to the same database?
The sysdtslog90 table you see there was created by SSIS, not you correct?
What database are your logging to/seeing the sysdtslog90 table in?
If you create a new test DB, point the log provider to it, does it log ok?

You mention you want to move it to production...when that occurs will you be logging to a different database anyway?

|||

Hi Craig,

I get this message when I execute the package. I tried to exclude only the logging feature from my package and it worked fine. It happens when I execute the package with ANY log writing (error messages, information, posexecute, etc )
There isnt other packages logging in this table because this the first one that i have included this feature.
I noted that the table was created with the name myLogin.sysdtslog90. Is this right? Can I change it?
This table was created automatically by the log process in the development database.

I didnt try this option ( create a new test DB.... ). I will try and then i post the results. Thanks for you sugestion.

No. I have a configuration for this packages. I will just change the path in the XML file and the package will point to production server. In the production environment I have the same structure as development environment.

Thanks for you help Craig.

Regards
Thiago

|||

Hi Thiago,

Did you get any solution for this error, I'm facing same issue with logging.

Thanks

AG_MD

|||

hi,

i get the same error.

it works on my own laptop.

when i deploy my packages via file-system to the server i run the packages via a command-prompt.

then i get the error about the log-provider.

don't have a clue what to do about it.

|||

Hi

I'm having a similar problem. Package execution sporadically fails when accessing sysdtslog90. Once I drop this table, the package executes a number of times, before I face this problem again.

Does anyone know what might be the cause of this error?

Cheers

Sachin

|||Have the same problem here... seems to have to do with permissions, but don't know for sure. Any other ideas on this?
|||

I have encountered the same issue when in Connection Manager I used SQL Authentication to connect with SQL Server OleDB Provider. SSIS Logging Provider in this case created log table for the SQL user (something like yourDBuser.sysdtslog90)

To resolve the issue script and then drop yourDBuser.sysdtslog90 table. in the script change the name of the table to dbo.sysdtslog90. Execute script to recreate table with dbo user.

Execute your SSIS package in BIDS and check dbo.sysdtslog90. This time you should see log entries in the table

Issue with Logging using SQL Server

Hi All,

I'm trying to implement the SQL Server logging in my package but Im receiving a very weird error message. Follow below:

"Error at Consumer_Common_Transportation [Log provider "SSIS log provider for SQL Server"]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.

Error at Copy Data to TCARRIER: The SSIS logging provider "SSIS log provider for SQL Server" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.

(Microsoft.DataTransformationServices.VsIntegration)"

I know that the sysdtslog90 table exists ( with no rows ), but i cant understand why this process doesnt includes rows/data in this table.

I also have the Text file logging in this package and it is working fine. I just need to fix this issue to move it to production.

Does anyone knows what is happening? Is there any property or flag that i need to change?

Thanks in advance for your help.

Regards,

Thiago

Hi, when do you get this message? When the package executes or as you are trying to add the log provider?
Can other packages log data to the same database?
The sysdtslog90 table you see there was created by SSIS, not you correct?
What database are your logging to/seeing the sysdtslog90 table in?
If you create a new test DB, point the log provider to it, does it log ok?

You mention you want to move it to production...when that occurs will you be logging to a different database anyway?

|||

Hi Craig,

I get this message when I execute the package. I tried to exclude only the logging feature from my package and it worked fine. It happens when I execute the package with ANY log writing (error messages, information, posexecute, etc )
There isnt other packages logging in this table because this the first one that i have included this feature.
I noted that the table was created with the name myLogin.sysdtslog90. Is this right? Can I change it?
This table was created automatically by the log process in the development database.

I didnt try this option ( create a new test DB.... ). I will try and then i post the results. Thanks for you sugestion.

No. I have a configuration for this packages. I will just change the path in the XML file and the package will point to production server. In the production environment I have the same structure as development environment.

Thanks for you help Craig.

Regards
Thiago

|||

Hi Thiago,

Did you get any solution for this error, I'm facing same issue with logging.

Thanks

AG_MD

|||

hi,

i get the same error.

it works on my own laptop.

when i deploy my packages via file-system to the server i run the packages via a command-prompt.

then i get the error about the log-provider.

don't have a clue what to do about it.

|||

Hi

I'm having a similar problem. Package execution sporadically fails when accessing sysdtslog90. Once I drop this table, the package executes a number of times, before I face this problem again.

Does anyone know what might be the cause of this error?

Cheers

Sachin

|||Have the same problem here... seems to have to do with permissions, but don't know for sure. Any other ideas on this?
|||

I have encountered the same issue when in Connection Manager I used SQL Authentication to connect with SQL Server OleDB Provider. SSIS Logging Provider in this case created log table for the SQL user (something like yourDBuser.sysdtslog90)

To resolve the issue script and then drop yourDBuser.sysdtslog90 table. in the script change the name of the table to dbo.sysdtslog90. Execute script to recreate table with dbo user.

Execute your SSIS package in BIDS and check dbo.sysdtslog90. This time you should see log entries in the table

Issue with Logging using SQL Server

Hi All,

I'm trying to implement the SQL Server logging in my package but Im receiving a very weird error message. Follow below:

"Error at Consumer_Common_Transportation [Log provider "SSIS log provider for SQL Server"]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.

Error at Copy Data to TCARRIER: The SSIS logging provider "SSIS log provider for SQL Server" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.

(Microsoft.DataTransformationServices.VsIntegration)"

I know that the sysdtslog90 table exists ( with no rows ), but i cant understand why this process doesnt includes rows/data in this table.

I also have the Text file logging in this package and it is working fine. I just need to fix this issue to move it to production.

Does anyone knows what is happening? Is there any property or flag that i need to change?

Thanks in advance for your help.

Regards,

Thiago

Hi, when do you get this message? When the package executes or as you are trying to add the log provider?
Can other packages log data to the same database?
The sysdtslog90 table you see there was created by SSIS, not you correct?
What database are your logging to/seeing the sysdtslog90 table in?
If you create a new test DB, point the log provider to it, does it log ok?

You mention you want to move it to production...when that occurs will you be logging to a different database anyway?

|||

Hi Craig,

I get this message when I execute the package. I tried to exclude only the logging feature from my package and it worked fine. It happens when I execute the package with ANY log writing (error messages, information, posexecute, etc )
There isnt other packages logging in this table because this the first one that i have included this feature.
I noted that the table was created with the name myLogin.sysdtslog90. Is this right? Can I change it?
This table was created automatically by the log process in the development database.

I didnt try this option ( create a new test DB.... ). I will try and then i post the results. Thanks for you sugestion.

No. I have a configuration for this packages. I will just change the path in the XML file and the package will point to production server. In the production environment I have the same structure as development environment.

Thanks for you help Craig.

Regards
Thiago

|||

Hi Thiago,

Did you get any solution for this error, I'm facing same issue with logging.

Thanks

AG_MD

|||

hi,

i get the same error.

it works on my own laptop.

when i deploy my packages via file-system to the server i run the packages via a command-prompt.

then i get the error about the log-provider.

don't have a clue what to do about it.

|||

Hi

I'm having a similar problem. Package execution sporadically fails when accessing sysdtslog90. Once I drop this table, the package executes a number of times, before I face this problem again.

Does anyone know what might be the cause of this error?

Cheers

Sachin

|||Have the same problem here... seems to have to do with permissions, but don't know for sure. Any other ideas on this?|||

I have encountered the same issue when in Connection Manager I used SQL Authentication to connect with SQL Server OleDB Provider. SSIS Logging Provider in this case created log table for the SQL user (something like yourDBuser.sysdtslog90)

To resolve the issue script and then drop yourDBuser.sysdtslog90 table. in the script change the name of the table to dbo.sysdtslog90. Execute script to recreate table with dbo user.

Execute your SSIS package in BIDS and check dbo.sysdtslog90. This time you should see log entries in the table

Issue with Logging using SQL Server

Hi All,

I'm trying to implement the SQL Server logging in my package but Im receiving a very weird error message. Follow below:

"Error at Consumer_Common_Transportation [Log provider "SSIS log provider for SQL Server"]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.

Error at Copy Data to TCARRIER: The SSIS logging provider "SSIS log provider for SQL Server" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.

(Microsoft.DataTransformationServices.VsIntegration)"

I know that the sysdtslog90 table exists ( with no rows ), but i cant understand why this process doesnt includes rows/data in this table.

I also have the Text file logging in this package and it is working fine. I just need to fix this issue to move it to production.

Does anyone knows what is happening? Is there any property or flag that i need to change?

Thanks in advance for your help.

Regards,

Thiago

Hi, when do you get this message? When the package executes or as you are trying to add the log provider?
Can other packages log data to the same database?
The sysdtslog90 table you see there was created by SSIS, not you correct?
What database are your logging to/seeing the sysdtslog90 table in?
If you create a new test DB, point the log provider to it, does it log ok?

You mention you want to move it to production...when that occurs will you be logging to a different database anyway?

|||

Hi Craig,

I get this message when I execute the package. I tried to exclude only the logging feature from my package and it worked fine. It happens when I execute the package with ANY log writing (error messages, information, posexecute, etc )
There isnt other packages logging in this table because this the first one that i have included this feature.
I noted that the table was created with the name myLogin.sysdtslog90. Is this right? Can I change it?
This table was created automatically by the log process in the development database.

I didnt try this option ( create a new test DB.... ). I will try and then i post the results. Thanks for you sugestion.

No. I have a configuration for this packages. I will just change the path in the XML file and the package will point to production server. In the production environment I have the same structure as development environment.

Thanks for you help Craig.

Regards
Thiago

|||

Hi Thiago,

Did you get any solution for this error, I'm facing same issue with logging.

Thanks

AG_MD

|||

hi,

i get the same error.

it works on my own laptop.

when i deploy my packages via file-system to the server i run the packages via a command-prompt.

then i get the error about the log-provider.

don't have a clue what to do about it.

|||

Hi

I'm having a similar problem. Package execution sporadically fails when accessing sysdtslog90. Once I drop this table, the package executes a number of times, before I face this problem again.

Does anyone know what might be the cause of this error?

Cheers

Sachin

|||Have the same problem here... seems to have to do with permissions, but don't know for sure. Any other ideas on this?
|||

I have encountered the same issue when in Connection Manager I used SQL Authentication to connect with SQL Server OleDB Provider. SSIS Logging Provider in this case created log table for the SQL user (something like yourDBuser.sysdtslog90)

To resolve the issue script and then drop yourDBuser.sysdtslog90 table. in the script change the name of the table to dbo.sysdtslog90. Execute script to recreate table with dbo user.

Execute your SSIS package in BIDS and check dbo.sysdtslog90. This time you should see log entries in the table

Issue with Logging using SQL Server

Hi All,

I'm trying to implement the SQL Server logging in my package but Im receiving a very weird error message. Follow below:

"Error at Consumer_Common_Transportation [Log provider "SSIS log provider for SQL Server"]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.

Error at Copy Data to TCARRIER: The SSIS logging provider "SSIS log provider for SQL Server" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.

(Microsoft.DataTransformationServices.VsIntegration)"

I know that the sysdtslog90 table exists ( with no rows ), but i cant understand why this process doesnt includes rows/data in this table.

I also have the Text file logging in this package and it is working fine. I just need to fix this issue to move it to production.

Does anyone knows what is happening? Is there any property or flag that i need to change?

Thanks in advance for your help.

Regards,

Thiago

Hi, when do you get this message? When the package executes or as you are trying to add the log provider?
Can other packages log data to the same database?
The sysdtslog90 table you see there was created by SSIS, not you correct?
What database are your logging to/seeing the sysdtslog90 table in?
If you create a new test DB, point the log provider to it, does it log ok?

You mention you want to move it to production...when that occurs will you be logging to a different database anyway?

|||

Hi Craig,

I get this message when I execute the package. I tried to exclude only the logging feature from my package and it worked fine. It happens when I execute the package with ANY log writing (error messages, information, posexecute, etc )
There isnt other packages logging in this table because this the first one that i have included this feature.
I noted that the table was created with the name myLogin.sysdtslog90. Is this right? Can I change it?
This table was created automatically by the log process in the development database.

I didnt try this option ( create a new test DB.... ). I will try and then i post the results. Thanks for you sugestion.

No. I have a configuration for this packages. I will just change the path in the XML file and the package will point to production server. In the production environment I have the same structure as development environment.

Thanks for you help Craig.

Regards
Thiago

|||

Hi Thiago,

Did you get any solution for this error, I'm facing same issue with logging.

Thanks

AG_MD

|||

hi,

i get the same error.

it works on my own laptop.

when i deploy my packages via file-system to the server i run the packages via a command-prompt.

then i get the error about the log-provider.

don't have a clue what to do about it.

|||

Hi

I'm having a similar problem. Package execution sporadically fails when accessing sysdtslog90. Once I drop this table, the package executes a number of times, before I face this problem again.

Does anyone know what might be the cause of this error?

Cheers

Sachin

|||Have the same problem here... seems to have to do with permissions, but don't know for sure. Any other ideas on this?|||

I have encountered the same issue when in Connection Manager I used SQL Authentication to connect with SQL Server OleDB Provider. SSIS Logging Provider in this case created log table for the SQL user (something like yourDBuser.sysdtslog90)

To resolve the issue script and then drop yourDBuser.sysdtslog90 table. in the script change the name of the table to dbo.sysdtslog90. Execute script to recreate table with dbo user.

Execute your SSIS package in BIDS and check dbo.sysdtslog90. This time you should see log entries in the table

Issue with Logging using SQL Server

Hi All,

I'm trying to implement the SQL Server logging in my package but Im receiving a very weird error message. Follow below:

"Error at Consumer_Common_Transportation [Log provider "SSIS log provider for SQL Server"]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.

Error at Copy Data to TCARRIER: The SSIS logging provider "SSIS log provider for SQL Server" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.

(Microsoft.DataTransformationServices.VsIntegration)"

I know that the sysdtslog90 table exists ( with no rows ), but i cant understand why this process doesnt includes rows/data in this table.

I also have the Text file logging in this package and it is working fine. I just need to fix this issue to move it to production.

Does anyone knows what is happening? Is there any property or flag that i need to change?

Thanks in advance for your help.

Regards,

Thiago

Hi, when do you get this message? When the package executes or as you are trying to add the log provider?
Can other packages log data to the same database?
The sysdtslog90 table you see there was created by SSIS, not you correct?
What database are your logging to/seeing the sysdtslog90 table in?
If you create a new test DB, point the log provider to it, does it log ok?

You mention you want to move it to production...when that occurs will you be logging to a different database anyway?

|||

Hi Craig,

I get this message when I execute the package. I tried to exclude only the logging feature from my package and it worked fine. It happens when I execute the package with ANY log writing (error messages, information, posexecute, etc )
There isnt other packages logging in this table because this the first one that i have included this feature.
I noted that the table was created with the name myLogin.sysdtslog90. Is this right? Can I change it?
This table was created automatically by the log process in the development database.

I didnt try this option ( create a new test DB.... ). I will try and then i post the results. Thanks for you sugestion.

No. I have a configuration for this packages. I will just change the path in the XML file and the package will point to production server. In the production environment I have the same structure as development environment.

Thanks for you help Craig.

Regards
Thiago

|||

Hi Thiago,

Did you get any solution for this error, I'm facing same issue with logging.

Thanks

AG_MD

|||

hi,

i get the same error.

it works on my own laptop.

when i deploy my packages via file-system to the server i run the packages via a command-prompt.

then i get the error about the log-provider.

don't have a clue what to do about it.

|||

Hi

I'm having a similar problem. Package execution sporadically fails when accessing sysdtslog90. Once I drop this table, the package executes a number of times, before I face this problem again.

Does anyone know what might be the cause of this error?

Cheers

Sachin

|||Have the same problem here... seems to have to do with permissions, but don't know for sure. Any other ideas on this?
|||

I have encountered the same issue when in Connection Manager I used SQL Authentication to connect with SQL Server OleDB Provider. SSIS Logging Provider in this case created log table for the SQL user (something like yourDBuser.sysdtslog90)

To resolve the issue script and then drop yourDBuser.sysdtslog90 table. in the script change the name of the table to dbo.sysdtslog90. Execute script to recreate table with dbo user.

Execute your SSIS package in BIDS and check dbo.sysdtslog90. This time you should see log entries in the table