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 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.

21 Comments

  1. 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

  2. Sifiso says:

    It’s a pleasure, Cornelia.

  3. 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.

  4. 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.

  5. unjurgine says:

    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

  6. Ryze says:

    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

  7. 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.

  8. Jafitasty says:

    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

  9. apricot tree says:

    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.

  10. Im thankful for the blog post.Much thanks again.

  11. Claire says:

    Just thought i would comment and say neat design, did you code it yourself? Looks great.

  12. grow roses says:

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

  13. Really interesting blog, keep up the good work!

  14. 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.

  15. Srini says:

    Thank you so much – very useful.

  16. Nick says:

    Thanks, man. That really helped me!

2 Trackbacks

Leave a comment