How to programmatically create an Execute T-SQL Statement Task in SSIS?

Question

I am creating SSIS packages programmatically with C#. I am using Visual Studio 2010 and develop for SQL Server 2012.

Up to know I was using the Execute SQL Task but for a particular statement I know need to use the Excecute T-SQL Statement Task and I can't figure out how to programmatically create them.

The way I create the regular SQL Tasks is as follows:

Executable exec = parent.Executables.Add("STOCK:TSQLTask");
TaskHost task = exec as TaskHost;
task.Properties["Name"].SetValue(task, name);
task.Properties["Description"].SetValue(task, description);
task.Properties["Connection"].SetValue(task, connMgr.Name);
task.Properties["SqlStatementSource"].SetValue(task, sqlStatement);

The creation process is inspired by the one described in this MSDN Guide. The important method call is the first one. Method header:

Executable Executables.Add(string moniker)

I got the moniker from this particular page. Unfortunately the provided list does not contains a moniker for a Execute T-SQL Statement Task. I still believe it must somehow be possible to create such tasks programmatically.

Does someone out there know how to achieve this? Thanks!

Thanks to William Todd Salzman's answer I could create said tasks using:

Executable tsqlExec = p.Executables.Add(typeof(Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceTSQLExecuteTask).AssemblyQualifiedName);

I find the EzAPI to be invaluable for a lot of programmatic interaction with SSIS. It greatly simplifies the act of working with the disparate SSIS APIs — billinkc
– billinkc, Commented Jul 2, 2012 at 13:19

William Salzman · Accepted Answer · 2012-06-28 19:29:20Z

2

From the page you mention that you got the Moniker list from:

If you prefer a more explicit syntax, or if the task that you want to add does not have a STOCK moniker, you can add the task to the executable using its long name. This syntax requires that you also specify the version number of the task.

  Executable exec = package.Executables.Add(
    "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, " +
    "Microsoft.SqlServer.ScriptTask, Version=10.0.000.0, " +
    "Culture=neutral, PublicKeyToken=89845dcd8080cc91");

You can obtain the long name for the task programmatically, without having to specify the task version, by using the AssemblyQualifiedName property of the class, as shown in the following example. This example requires a reference to the Microsoft.SqlServer.SQLTask assembly.

using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
...
      Executable exec = package.Executables.Add(
        typeof(Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask).AssemblyQualifiedName);

answered Jun 28, 2012 at 19:29

William Salzman

6,4463 gold badges35 silver badges51 bronze badges

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

1 Comment

adrienfb Over a year ago

thanks for the answer! I looked at the page so often, I actually failed to re-read it carefully... I figured out the class corresponding to the Execute T-SQL Statement Task (see question edit) and it worked like a charm.

Collectives™ on Stack Overflow

How to programmatically create an Execute T-SQL Statement Task in SSIS?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related