XML Configuration Issue

MattJackson

Hi all,

I have a curious problem I was hoping someone could help me with.

I've designed a package to perform some data warehousing of our DB's. It manages to run successfully in Visual Studio (/BIDS) with no problems, including using various XML configurations for our different environments (Local, Dev, Test).

We've then tried to trigger the execution of the package from our ASP.Net web application. The first time after a system reboot, the package runs successfully. However, subsequent attempts fail with an array of the following error message for each of the package's variables:

Error in Microsoft.SqlServer.Dts.Runtime.TaskHost/ : The result of the expression "@[User::ReportMasterName]" on property "SourceDatabaseName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

Here are the relevant pieces of code:

Web.Config:

Code Snippet

public void load()
{
Application app = new Application();

_listener = new PackageEvents();
_listener.PtrUpdateStatus = _update;

switch (_loadType)
{
case LoadType.File:
_package = app.LoadPackage(_packagePath, _listener); break;
case LoadType.SQLServer:
_package = app.LoadFromSqlServer(_packageName, _serverName, _serverUsername, _serverPassword, _listener); break;
default:
throw new Exception(this.ToString() + ".load() - Unable to load package. Could not determine load type.");
}

if (_configPath != null)
{
if (!File.Exists(_configPath))
throw new FileNotFoundException(this.ToString() + ".load() - Unable to load package. Configuration file not at location specified.", _configPath);

_package.ImportConfigurationFile(_configPath);
}
}

Code Snippet

public bool execute(ref string strReturn)
{
DTSExecResult result;

result = _package.Execute(null, null, _listener, null, null);
//result = _package.Execute();

strReturn += String.Concat(result.ToString(), " ");

if (result == DTSExecResult.Failure)
{
foreach (DtsError errs in _package.Errors)
strReturn += String.Concat(errs.Description, " ");
}
}

We've attemped to execute this both as a file system package, and as a SQL Server package.

Any pointers would be greatfully appreciated.

Cheers

SenthilP - MSFT

Hi Matt,

Do you have multiple script tasks in your package. Can you provide more info on the composition of the package and where this variable is being used.

Thanks

MattJ-1982

Hi

I don't have any Script tasks in the package.

The package is made up of a Transfer Database task, and many Execute SQL and Data Flow tasks (OLE DB Source & Destination and Derived Columns tasks). The package is basically used to to warehouse the data in our system.

The variables that throw the errors (about 12 in total) are the ones being set through the XML Configuration. These are concerned with setting DataSources & the Source/Destination settings in the Transfer DB task.

The ones that are set through .NET code do not throw the error.

Hope this makes things clearer.

Thanks for the help

SenthilP - MSFT

Can you try and isolate the problem. Somethings to try:

1) Run the package using dtexec multiple times and see if the error occurs.

2) Execute a simple package from ASP.NET application that uses XML Configuration file to load variable values and see if you get this error.

Also, if you can post the complete log information, it would be helpful.

Thanks

MattJackson

I have a few other things to deal with in the meantime, but I'll hopefully be able to look at this again Tuesday or Wednesday next week. I'll gather as much information as I can then and get back to you.

Cheers again

MattJackson

Ok, here is my logging (in reverse order) from within the application. I'm only catching the OnError, OnProgress, OnInformation and OnWarning events. Let me know if you need further logging of the other events and I'll code it in.

In regards to your suggestions:

1) I am able to execute the package from DTExecUI with no problems.

2) I haven't had time to create a dummy project to execute the package, but the code that I am using has executed SSIS packages before (that haven't required XML configuration)

Also, when I execute the package from my code after a fresh reboot, it executes successfully. I can even execute it multiple times from that same debugging instance. Once I hit "Stop" in Visual Studio and re-start the debugging instance for a second time, the execution then begins to fail and the errors appear.

My software versions:

Visual Studio 8.0.50727.762

Code Snippet

Warning - Microsoft.SqlServer.Dts.Runtime.Package/ : The Execution method succeeded, but the number of errors raised (16) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Error in Microsoft.SqlServer.Dts.Runtime.TaskHost/ : The result of the expression "@[User::ReportMasterName]" on property "SourceDatabaseName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Error in Microsoft.SqlServer.Dts.Runtime.TaskHost/ : The result of the expression "@[User::ReportMasterFilesSource]" on property "SourceDatabaseFiles" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Error in Microsoft.SqlServer.Dts.Runtime.TaskHost/ : The result of the expression "@[User::SnapshotName]" on property "DestinationDatabaseName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Error in Microsoft.SqlServer.Dts.Runtime.TaskHost/ : The result of the expression "@[User::SnapshotFilesDestination]" on property "DestinationDatabaseFiles" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Error in Microsoft.SqlServer.Dts.Runtime.TaskHost/ : The result of the expression "@[User::ReportMasterName]" on property "SourceDatabaseName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Error in Microsoft.SqlServer.Dts.Runtime.TaskHost/ : The result of the expression "@[User::ReportMasterFilesSource]" on property "SourceDatabaseFiles" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Error in Microsoft.SqlServer.Dts.Runtime.TaskHost/ : The result of the expression "@[User::SnapshotName]" on property "DestinationDatabaseName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Error in Microsoft.SqlServer.Dts.Runtime.TaskHost/ : The result of the expression "@[User::SnapshotFilesDestination]" on property "DestinationDatabaseFiles" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Error in Microsoft.SqlServer.Dts.Runtime.Package/ : The result of the expression "@[User::DestinationUsername]" on property "UserName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Error in Microsoft.SqlServer.Dts.Runtime.Package/ : The result of the expression "@[User::DestinationServer]" on property "SqlServerName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Error in Microsoft.SqlServer.Dts.Runtime.Package/ : The result of the expression "@[User::SourceUsername]" on property "UserName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Error in Microsoft.SqlServer.Dts.Runtime.Package/ : The result of the expression "@[User::SourceServer]" on property "SqlServerName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Warning - Microsoft.SqlServer.Dts.Runtime.Package/ : Process configuration failed to set the destination at the package path of "\Package.Connections[Portal Report SMO Destination].Properties[Password]". This occurs when attempting to set the destination property or variable fails. Check the destination property or variable.
Warning - Microsoft.SqlServer.Dts.Runtime.Package/ : Process configuration failed to set the destination at the package path of "\Package.Connections[Portal Report Master SMO Source].Properties[Password]". This occurs when attempting to set the destination property or variable fails. Check the destination property or variable.
Information - Microsoft.SqlServer.Dts.Runtime.Package/ : The package is attempting to configure from the XML file "C:\(removed path)\Snapshot-ConnSettings.dtsConfig".

MattJackson

Any ideas guys This is still an outstanding issue with us and any help would be greatly appreciated.

Cheers.

SenthilP - MSFT

I am trying to repro the problem. I will get back to you when I have some answers.

SenthilP - MSFT

I created a package with a transfer db task and used a config file to set the property values using variables; I loaded and executed the package using your code and i can't repro the problem you're encountering. I executed them in a loop 100 times and I couldn't get it to fail. I am not sure how I can help you without actually getting hold of the package where this problem arises.

I'd suggest that you create a generic package that errors out and post it here along with the code, so some one can run it/debug it to see what the issue is.

I wish I had a better answer than this...

Thanks

MattJackson

Is it an option for us to send the package to you

SenthilP - MSFT

You can mail it to me. Please send me the all the relevant stuff and anything I need to do get the package to execute.

MattJackson

I have emailed you with the relevant information.

Cheers

Raj Pannu

Hi MattJackson,

I had the similar problem while running package from ASP. All the dynmic assignment of properties of a Mail Task were failing with exactly the same error your are getting.

What I found was that as we created mail tasks by copying from another package to new package the designer assigned some autogenerated IDs to the connection property instead of the connection we are intend to use. This happens when the intended connection is not present in package at the time of copying the task component.

So by simply changing the connection property from autoassigned autogenerated id to the real connection in use the problem was resolved.

I am not sure if this is the case for you what lesson is 'Try to look arround all the setups, properties of the task which is failing'. In my case all the errors related to one task.

With regards

Raj

MattJackson

Raj,

What you describe sounds similar to what is happening with with our package. However, I recreated from scratch the related task & related expressions (instead of copying it from another package) and the error still remains. It also doesn't explain why the package would execute the first time, and then fail on subsequent executions.

Thanks for the suggestion however, I think you're on the right track but there's still something else with our package.

Cheers

dreamw13

I have experienced a very similar error, the main difference being that I'm trying to execute the package from a c# unit test rather than a web page. A test will run once successfully, but the same test will fail next time, if the package has variables being initialized from a configuration.

I discovered that a reboot is not necessary to "reset" back into the state where the test will pass. What is needed is to close the Windows process (e.g., the .exe) that ran my code. In my case that would be the unit-test runner program; in your case that would probably be IIS or maybe an application pool.

No error if the package doesn't use configurations. I should point out that we use indirect xml configurations (i.e., an environment variable pointing to an xml configuration file).

Here is the relevant test code:

-------------------

public static PkgExecResults RunPackage(string PackageName)

{

PkgExecResults results = new PkgExecResults();

Package p = SSIS_UT.OpenPackage_NoEvents(PackageName);

try

{

p.Execute();

results.Result = p.ExecutionResult;

}

finally

{

p.Dispose();

p = null;

}

return results;

}

public static Package OpenPackage_NoEvents(string PackageName)

{

Application app = new Application();

Package p = app.LoadPackage(PackageName, null);

return p;

}

public struct PkgExecResults

{

public DTSExecResult Result;

public int AuditExecID;

}

----------- the following test succeeds first time; fails thereafter until the containing .exe is closed:

[Test]

public void testRunPackage()

{

PkgExecResults result = SSIS_UT.RunPackage(pkgName);

Assert.AreEqual(DTSExecResult.Success, result.Result);

}

I tried everything I could think of to "clear out" the process; that's why the methods are static. But it doesn't matter, after the first test executes, the same test (or any other test using a package that uses configurations) will fail until the .exe is stopped.

Interestingly, I was using MbUnit v2.4.197 to run the tests. I just discovered today that the problem does not occur if I instead use NUnit v2.4.1. I suppose that Microsoft could claim it's therefore an MbUnit bug, but if so, IIS apparently has the same one. Seems to me that there's something wrong with the Microsoft.SQLServer.ManagedDTS code, or with my (and Matt's) code that uses it.

I'd sure love to get this resolved. We're trying to build a simple way to automate testing of our packages, and this is at the center of it.

XML Configuration Issue

Re: XML Configuration Issue

Re: XML Configuration Issue

Re: XML Configuration Issue

Re: XML Configuration Issue

Re: XML Configuration Issue

Re: XML Configuration Issue

Re: XML Configuration Issue

Re: XML Configuration Issue

Re: XML Configuration Issue

Re: XML Configuration Issue

Re: XML Configuration Issue

Re: XML Configuration Issue

Re: XML Configuration Issue

Re: XML Configuration Issue