Create SSIS Package with SQL Tasks using C#

By | April 6, 2011

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 create a SQL view, the second sql task updates the view.

Requirements

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 Control Flow pane of the new package.

Insert script task

Insert script task - click image to view full size

 

Right click the script task click on “Edit”

Right Click - click image to view full size

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

Select C# - click image to view full size

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

Add references - click image to view full size

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 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 task in 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 location you specified.

Sifiso W. Ndlovu (43 Posts)

Sifiso W. Ndlovu is a certified Microsoft professional who holds a Master’s degree in IT Management from the University of Johannesburg. He specializes on a range of enterprise and consumer technologies using open source and proprietary software. He is the member of the Johannesburg SQL User Group wherein he has made several presentations on User Group Meetings and SQL Saturday sessions. He has written for a number of publications including SQLShack.com and SQLServerCentral.com.


  1. veg mocking party

    Hey there I am so grateful I found your blog, I really found you by accident, while I was searching on Askjeeve for something else, Anyways I am here now and would just like to say many thanks for a tremendous post and a all round interesting blog (I also love the theme/design), I don’t have time to read through it all at the moment but I have bookmarked it and also added your RSS feeds, so when I have time I will be back to read much more, Please do keep up the great work.

    Reply
  2. grow roses

    Superb blog post, I have book marked this internet site so ideally I’ll see much more on this subject in the foreseeable future!

    Reply
  3. apricot tree

    I was extremely pleased to locate this web-site.I wanted to thanks for your time for this wonderful read!! I undoubtedly enjoying every little bit of it and I’ve you bookmarked to check out new stuff you weblog post.

    Reply
  4. Jafitasty

    Hi, I left you a DOFOLLOW backlink on my website. This isnt a spam message, i actually did leave you a backlink on my site. If you check the top of the page you will see “Sites we like” and there will be a link to this site. Would you be kind enough to leave me a backlink? If so my website is http://crush-the-castle.com please use the anchor text “Crush The Castle” for the link and add it to a post or as a widget. Then please send me a email at backlink@crush-the-castle.com – If you want me to change your links anchor text let me know. Thanks

    Reply
  5. tigla metalica

    Yahoo displayed your blog when I looked for a specific keyword, that’s why I ended up here. Anyway, you have a great article, it’s worth reading and worthy of my time.

    Reply
  6. Ryze

    How many people do you get on your site per day? I have been reading some of you content and i think its good but hardly anyone leaves any comments which makes me think your not getting much traffic? Check these guys out, they helped my site and i think they can help you achive your online goals http://www.autowealthmaker.me

    Reply
  7. unjurgine

    Hi, I quickly read your posts and i can see that your website has potential however no matter how good your content is you NEED traffic and exposure to the huge wide web. I really think you will find something to help you on this site, it has really helped me with my blog and i am now making $10,000 + per month blogging. http://www.commissioncrusherx.net

    Reply
  8. Eroreicroxeri

    Hi. I like your website but i can tell it probably isn’t getting much traffic? If you want to help imrpove that check this website out, he has a short video that i really suggest you watch. Commission Crusher P.S this isn’t my website and i’m not spamming your blog, i don’t care if you delete this comment. I am only trying to help you improve your site.

    Reply
  9. katalog stron www

    whoah this blog is wonderful i love reading your articles. Keep up the good work! You know, many people are looking around for this info, you could aid them greatly.

    Reply
  10. Pingback: Extract SQL Statements in Execute SQL Tasks and save them into a text delimited file using C#

  11. Pingback: Extract SQL Statements in Execute SQL Tasks and save them into an Excel Workbook using C#

  12. Cornelia Forand

    I have been searching for some information about it almost three hours. You helped me a lot indeed and reading this your article I have found many new and useful information about this subject. Backlinks

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *


nine × = 63