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 create a SQL view, the second sql task updates the view.
- Microsoft Visual Studio 2008
- SQL Server 2008
- AdventureWorks2008R2 database (downloadable database file available here)
If the above requirements are all met, we will begin by launching Microsoft Visual Studio 2008.
After you have named the new project, proceed to click and drag the script task in Control Flow pane of the new package.
Right click the script task 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 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 AdventureWorks2008R2 database.
ConnectionManager ConMgr = dyna_pkg.Connections.Add(“OLEDB”);
ConMgr.ConnectionString = “Provider=SQLOLEDB.1;” +
“Integrated Security=SSPI;Initial Catalog=AdventureWorks2008R2;” +
ConMgr.Name = “ConMgr_OLEDB”;
ConMgr.Description = “OLE DB connection to the AdventureWorks2008R2 database.”;
Add the Execute SQL Task in the package.
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["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 task in the package.
th2.Properties["Name"].SetValue(th2, “select from view”);
th2.Properties["Description"].SetValue(th2, “select from view”);
th2.Properties["SqlStatementSource"].SetValue(th2, “SELECT * FROM v_Sales”);
th3.Properties["Name"].SetValue(th3, “delete View”);
th3.Properties["Description"].SetValue(th3, “delete View”);
th3.Properties["SqlStatementSource"].SetValue(th3, “DROP VIEW v_Sales”);
Then we join the three tasks with precedence constraints.
We then save the package in a file system.
It’s that simple!
You can now execute your script task and the package will be created in location you specified.