SSIS : Adding a Execute SQL task to SSIS package with C# (Part IV)

This post is the fourth post in a serie (Part I, Part II and Part III) about generating SSIS packages with C#. This post is about generating a SSIS package with a Execute SQL Task. In one of my former posts i already showed how to use early binding constructs. Prerequisite for this is referencing the Microsoft.SqlServer.SQLTask and using this in your C# program. Below is an example a simple C# program with a Execute SQL Task programmed. Don't forget adding this on top of script: using DTS = Microsoft.SqlServer.Dts.Runtime; (i created a Forms application and therefore the application object is referenced twice).

// Create an application
DTS.Application app = new DTS.Application();

// Create a package
DTS.Package pkg = new DTS.Package();

//Setting some properties
pkg.Name = @"MyProgrammedSQLTaskPAckage";

//Adding a connection to the package
DTS.ConnectionManager ConnMgr = pkg.Connections.Add("OLEDB");
ConnMgr.ConnectionString = @"Provider=SQLOLEDB.1;" +
"Integrated Security=SSPI;Initial Catalog=AdventureWorksLT2008R2;" +
"Data Source=(local);";
ConnMgr.Name = @"AdventureWorksLT2008R2";
ConnMgr.Description = @"SSIS Connection Manager for OLEDB Source AdventureWorksLT2008R2";

//adding a "Execute SQL task" to the package
DTS.Executable ExeForSQLTask = pkg.Executables.Add("STOCK:SQLTask");
DTS.TaskHost TKHSQLHost = (DTS.TaskHost)ExeForSQLTask;

TKHSQLHost.Name = "This is a programmed SQL Task";

ExecuteSQLTask MySQLTask = (ExecuteSQLTask)TKHSQLHost.InnerObject;

MySQLTask.Connection = "AdventureWorksLT2008R2";
MySQLTask.SqlStatementSource = "SELECT id FROM sysobjects WHERE name = 'sysrowsets'";
MySQLTask.SqlStatementSourceType = SqlStatementSourceType.DirectInput;
MySQLTask.BypassPrepare = false;

app.SaveToXml(String.Format(@"E:\\SSISProgram\\{0}.dtsx", pkg.Name), pkg, null);

Console.WriteLine("Package {0} created", pkg.Name);

pkg.Dispose(); 


In SSIS the following windows are the result of executing this C# script:

Hennie