Problem with package.Execute passing variables

SQL Server Integration Services

BMcDowell


I am having a problem with passing variables into my SSIS package from C#. The variable names match ("Variable1, ...), however they do not seem to be assigned the proper values once the package is executed. The package does run and returns a FALURE notice saying there is a problem with my expressions.

When I added a new data flow, derived all the variables into columns and wrote their values to a flat file I noticed that the values still contain my default values from the SSIS package itself as though nothing was passed in from C#. I am hoping that it is a simple configuration/user error.

Any ideas

---- C# ----

Reference to Microsoft.SQLServer.ManagedDTS

using Microsoft.SqlServer.Dts.Runtime;

Application DTSApp = new Application();
Package DTSPack = DTSApp.LoadPackage("d:\\SSISPackages\\Package.dtsx", null);
DTSPack.Variables.Add("Variable1", true, "", var1.ToString());
DTSPack.Variables.Add("Variable2", true, "", var2);
DTSPack.Variables.Add("Variable3", true, "", 100);
DTSPack.Variables.Add("Variable4", true, "", var4.ToString());
DTSExecResult pkgResult = DTSPack.Execute(null, DTSPack.Variables, null, null, null);

Top

Re: Problem with package.Execute passing variables

BMcDowell


A copy of the error returned.

Source: Bulk Insert Task

Description: The result of the expression "@[User::TableName]" on property "DestinationTableName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.



Top

Re: Problem with package.Execute passing variables

Phil Brammer

What is DestinationTableName





Top

Re: Problem with package.Execute passing variables

BMcDowell

DestinationTableName is a Property in the Bulk Insert Task Expression List.

I am trying to pass a variable from C# to SSIS that will be used in an expression (in this case, to tell the Bulk Insert where to write the data to).

 

my C# code where I set the variable looks like...
vars["TableName"].Value = "TestDB."+Session["GenSQLName"].ToString();

I altered my code above to match this logic and I am getting proper returns on my variables which tells me SSIS is receiving the proper data but is not handleing it correctly.

Variables vars = DTSPack.Variables;
vars["Var1"].Value = var1.ToString();
vars["TableName"].Value = "TestDB."+Session["GenSQLName"].ToString();
DTSExecResult pkgResult = DTSPack.Execute();


Top

Re: Problem with package.Execute passing variables

Rafael Salas

BMcDowell wrote:

A copy of the error returned.

Source: Bulk Insert Task

Description: The result of the expression "@[User::TableName]" on property "DestinationTableName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

I have not used bulk insert task; but the error suggests that 'DestinationTableName' property cannot be override via expression...It may be that the problem





Top

Re: Problem with package.Execute passing variables

Phil Brammer

I'm in the same position as Rafael here, but I do see that you can assign expressions to properties in the Bulk Insert Task... At least the GUI lets you use expressions for properties of the Bulk Insert task.

Are you sure the variables are scoped correctly Have you captured the TableName variable in SSIS to ensure that you are getting the correct results

I wouldn't think it would matter, but normally when I select a table from a drop down box, I get just the table name. When I selected a table in the Bulk Insert task, I got the fully qualified name for the table... Would that matter So, "database.dbo.table_name" is what showed up.

Phil




Top

Re: Problem with package.Execute passing variables

BMcDowell

The path is fully represented. If i paste in the variables exactly as they are being passed (verified via the data flow) the package runs fine and updates the database. Are there any settings in SSIS that would prevent me from dynamically passing variables into expressions I went through all the properties for the variables and the bulk insert task and have yet to find a logical setting.

I


Top

Re: Problem with package.Execute passing variables

Phil Brammer

BMcDowell wrote:

The path is fully represented. If i paste in the variables exactly as they are being passed (verified via the data flow) the package runs fine and updates the database. Are there any settings in SSIS that would prevent me from dynamically passing variables into expressions I went through all the properties for the variables and the bulk insert task and have yet to find a logical setting.

I



There isn't an option to my knowledge, and this could be a bug. I'll have to test on my own when I get a chance later today.




Top

Re: Problem with package.Execute passing variables

Harry Cu

We had the same problem when executing from a ASP.net app. The issue appears to be that SSIS only evaluates expressions when the package is initially loaded and the package never really unloads from the IIS server until it bounces. The only way that I was able to get around it was to change the web app to kick off the package execution on a new thread. That causes the package to be reloaded each time so the expressions are rebuilt correctly.

Hope that helps.

Harry


Top

Re: Problem with package.Execute passing variables

BMcDowell

Thank you Harry, Interesting approach but makes a lot of sense. I am not very familiar with threading. I did some quick research on the web and have a few ideas. I will post back the results once I figure out how to accomplish this task.


Top

Re: Problem with package.Execute passing variables

BMcDowell

DTSPack.Dispose(); did the trick.

Here is an example of the code for future reference. Thank you to everybody who helped me brainstorm on this. I feel like such a rookie at times.

Application DTSApp = new Application();
Package DTSPack = DTSApp.LoadPackage("d:\\SSISPackages\\TestBulkInsert.dtsx", null);
DTSPack.ImportConfigurationFile("d:\\SSISPackages\\TestBulkInsert.dtsConfig");
Variables vars = DTSPack.Variables;
vars["SSISVar1"].Value = CSharpVar1.ToString();
vars["SSISVar2"].Value = CSharpVar2;
vars["SSISVar3"].Value = 100;
vars["SSISTableName"].Value = "SQLDataBase."+Session["GenSQLName"].ToString();
DTSExecResult pkgResult = DTSPack.Execute();
DTSPack.Dispose();


Top

Re: Problem with package.Execute passing variables

BMcDowell

I spoke to soon. I have the package executing in a loop. The loop works now (on first run) however on subsequent executions I am running into the same issue. Time to continue threding research.


Top

Re: Problem with package.Execute passing variables

Dave Waterworth

Just a thought, maybe you could try creating a new application domain - loading and executing the package in the new app domain and then destroying the app domain afterwards


Top

Re: Problem with package.Execute passing variables

Harry Cu

I also tried Dispose and saw the same results. Works the first time only. I could not get the package to actually unload until the thread completed. The app domain idea should accomplish the same thing, but I believe it would be more work especially if you are running under the IIS context. Running under a new thread is pretty simple and there are a lot of examples on the web, but if you can't find one let me know and I would be happy to post one.

Harry




Top

Re: Problem with package.Execute passing variables

David Frommer

Try setting the RaiseChangedEvent property of each variable to TRUE. This cause and event to be fired that will force the recalculation of all expressions that depend on this variable whenever the variable value changes



Top