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.
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
It’s a pleasure, Cornelia.
Pingback: Extract SQL Statements in Execute SQL Tasks and save them into an Excel Workbook using C#
Pingback: Extract SQL Statements in Execute SQL Tasks and save them into a text delimited file using C#
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.
I appreciate it, many thanks.
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.
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
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
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.
thanks
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
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.
Im thankful for the blog post.Much thanks again.
pleasure is all mine, thanks
Just thought i would comment and say neat design, did you code it yourself? Looks great.
thanks, Claire
Superb blog post, I have book marked this internet site so ideally I’ll see much more on this subject in the foreseeable future!
Really interesting blog, keep up the good work!
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.
Thank you so much – very useful.
Pleasure
Thanks, man. That really helped me!