Create SSIS Package with SQL Tasks using C#

Abstract
This article explains how to create an SSIS package using C# programming language. The package contains two SQL tasks: the first SQL task drops and creates a SQL view, and the second SQL task updates the view.
Requirements

• Microsoft Visual Studio 2008
• SQL Server 2008
• AdventureWorks2008R2 database (downloadable database file available here)
  

Article
If the above requirements are all met, we will begin by launching Microsoft Visual Studio 2008.
Create a new project Integration Services Project which is located under Business Intelligence Projects.
After you have named the new project, proceed to click and drag the script task in the Control Flow pane of the new package.

Right click the script task and click on “Edit”

Under the Script Task Editor change the \”ScriptLanguage\” to \”Microsoft Visual C# 2008\”.

Clicking “Edit Script” will bring another window where you can import relevant namespaces:

Ensure that you have declared namespaces as below:
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Dts.Tasks;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
After declarations, create a new package (including package name and description) inside an application.

Application SIFISO_app = new Application();
Package dyna_pkg = new Package();
dyna_pkg.Name = “pkg_Execute_Sql_Tasks”;
dyna_pkg.Description = “Executing Sql Task”;
Create a connection to the AdventureWorks2008R2 database.
ConnectionManager ConMgr = dyna_pkg.Connections.Add(“OLEDB”);
ConMgr.ConnectionString = “Provider=SQLOLEDB.1;” +
“Integrated Security=SSPI;Initial Catalog=AdventureWorks2008R2;” +
“Data Source=(local);”;
ConMgr.Name = “ConMgr_OLEDB”;
ConMgr.Description = “OLE DB connection to the AdventureWorks2008R2 database.”;
Add the Execute SQL Task in the package.
Executable exec = dyna_pkg.Executables.Add(“STOCK:SQLTask”);
TaskHost th = exec as TaskHost;
Define the properties of the newly added SQL Task including name, description, connection, and SQL query.
th.Properties[“Name”].SetValue(th, “Create View”);
th.Properties[“Description”].SetValue(th, “Drops and Create SQL View which based on Adventureworks database”);
th.Properties[“Connection”].SetValue(th, “ConMgr_OLEDB”);
th.Properties[“SqlStatementSource”].SetValue(th, “CREATE OR REPLACE VIEW v_Sales as select * from Employee”);
For the purposes of this article, we have further added two more SQL tasks to the package.
Executable exec2 = dyna_pkg.Executables.Add(“STOCK:SQLTask”);
TaskHost th2 = exec2 as TaskHost;
th2.Properties[“Name”].SetValue(th2, “select from view”);
th2.Properties[“Description”].SetValue(th2, “select from view”);
th2.Properties[“Connection”].SetValue(th2, “ConMgr_OLEDB”);
th2.Properties[“SqlStatementSource”].SetValue(th2, “SELECT * FROM v_Sales”);
Executable exec3 = dyna_pkg.Executables.Add(“STOCK:SQLTask”);
TaskHost th3 = exec3 as TaskHost;

th3.Properties[“Name”].SetValue(th3, “delete View”);
th3.Properties[“Description”].SetValue(th3, “delete View”);
th3.Properties[“Connection”].SetValue(th3, “ConMgr_OLEDB”);
th3.Properties[“SqlStatementSource”].SetValue(th3, “DROP VIEW v_Sales”);
Then we join the three tasks with precedence constraints.
PrecedenceConstraint pcFileTasks =
dyna_pkg.PrecedenceConstraints.Add((Executable)exec, (Executable)exec2);
pcFileTasks.Value = DTSExecResult.Success;

PrecedenceConstraint pcFileTasks2 =
dyna_pkg.PrecedenceConstraints.Add((Executable)exec2, (Executable)exec3);
pcFileTasks2.Value = DTSExecResult.Success;
We then save the package in a file system.
SIFISO_app.SaveToXml(“C:\\\\TEMP\\\\pkg_Execute_Sql_Tasks.dtsx”, dyna_pkg, null);
Conclusion
It’s that simple!
You can now execute your script task and the package will be created in the location you specified.