Creating Expression task programmatically in ssis using c#

Question

Anyone know about how to create an expression task in c# to generate SSIS expression task?

Thank you.

I know about PrecedenceConstraint.

See my code:

 Executable exe1 = pkg.Executables.Add("STOCK:SQLTask");
 TaskHost TKHSQLHostMySQLTask = (TaskHost)exe1;
 TKHSQLHostMySQLTask.Name = "my sql";
 ExecuteSQLTask MySQLTask = (ExecuteSQLTask)TKHSQLHostMySQLTask.InnerObject;

 //creating Execute SQL Task       
 MySQLTask.Connection = "sourcecon";
 MySQLTask.SqlStatementSource = "select deptno from dbo.dept";
 MySQLTask.SqlStatementSourceType = SqlStatementSourceType.DirectInput;
 MySQLTask.BypassPrepare = false;      

 Executable exe2 = pkg.Executables.Add("STOCK:SQLTask");

 TaskHost TKHSQLHostMySQLTask1 = (TaskHost)exe2;
 ExecuteSQLTask MySQLTask1 = (ExecuteSQLTask)TKHSQLHostMySQLTask1.InnerObject;
 //creating Execute SQL Task       
 MySQLTask1.Connection = "sourcecon";
 MySQLTask1.SqlStatementSource = "select deptno from dbo.dept where deptname=?";
 MySQLTask1.SqlStatementSourceType = SqlStatementSourceType.DirectInput;
 MySQLTask1.BypassPrepare = false;

 // Add input parameter binding
 MySQLTask1.ParameterBindings.Add();
 IDTSParameterBinding parameterBinding = MySQLTask1.ParameterBindings.GetBinding(0);
 parameterBinding.DtsVariableName = "[User::Dname]";
 parameterBinding.ParameterDirection = ParameterDirections.Input;
 //parameterBinding.DataType = (int)OleDBDataTypes.VARCHAR;
 parameterBinding.ParameterName = "0";
 parameterBinding.ParameterSize = 255;

 PrecedenceConstraint pc = pkg.PrecedenceConstraints.Add((Executable)exe1, (Executable)exe2);
 pc.EvalOp = DTSPrecedenceEvalOp.ExpressionAndConstraint;
 pc.Expression ="[User::myVar]>0";
  //how to add Expression task ?

Which version of SQL Server are you targeting?

Jamleck
– Jamleck

2014-06-04 10:12:04 +00:00
Commented Jun 4, 2014 at 10:12 — Jamleck
– Jamleck, Commented Jun 4, 2014 at 10:12
I am using SQL Server 2012. Thank you.

Sunil Sambarekar
– Sunil Sambarekar

2014-06-04 12:24:18 +00:00
Commented Jun 4, 2014 at 12:24 — Sunil Sambarekar
– Sunil Sambarekar, Commented Jun 4, 2014 at 12:24

Jamleck · Accepted Answer · 2014-06-04 11:01:46Z

1

There is no published moniker for an ExpressionTask in SSIS 2012. Use the CLSID of the ExpressionTask executable instead. You can then use the PrecedenceConstraint to connect it to other classes. See the example below.

Make sure you reference Microsoft.SqlServer.ExpressionTask assembly in your project.

// Using the CLSID
Executable exExpressionTask = 
pkg.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExpressionTask.ExpressionTask, Microsoft.SqlServer.ExpressionTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
TaskHost thExpressionTask = exExpressionTask as TaskHost;
ExpressionTask expressionTask = thExpressionTask.InnerObject as ExpressionTask;

// Set the expression.
expressionTask.Expression = @"10>0";

// Get the expression.
string expression = expressionTask.Expression;

// Validate the expression.
string str = expressionTask.ValidateExpression(null);

answered Jun 4, 2014 at 11:01

Jamleck

1,0377 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jamleck Over a year ago

@Sunil Sambarekar Thanks. Mark it as the answer if it solved your problem.

Sunil Sambarekar Over a year ago

@ Jamleck Thanks. how to "Mark it as the answer"? How to do it?

Sunil Sambarekar Over a year ago

Any one can know about derived new column takes source EmpID column. my code gives bellow error- Attempt to find the input column named "EmpID" failed with error code 0xC0010009. The input column specified was not found in the input column collection.

Jamleck Over a year ago

Please post this as a separate question.

Sunil Sambarekar Over a year ago

I solved this. stackoverflow.com/questions/24166854/…

Collectives™ on Stack Overflow

Creating Expression task programmatically in ssis using c#

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related