CODEWITHSUNDEEP
c#ssis
adrienfb
adrienfb

Reputation: 33

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

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);

Upvotes: 1

Views: 3179

Answers (1)

William Salzman
William Salzman

Reputation: 6446

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);

Upvotes: 2

Related Questions