Abstract
This article explains how to create an SSIS package with Execute SQL Tasks inside a ForEachLoop container using C# programming language.
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”.
In Project Explorer import relevant references and 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.”;
Insert a ForEachLoop container:
ForEachLoop exec_SEQ = (ForEachLoop)dyna_pkg.Executables.Add(“STOCK:FOREACHLOOP”);
exec_SEQ.FailPackageOnFailure = true;
exec_SEQ.FailParentOnFailure = true;
exec_SEQ.Name = @”select SIFISO Sequence Container”;
exec_SEQ.Description = @”select SIFISO Sequence Container”;
Set the properties of the ForEachLoop container:
ForEachEnumeratorInfo f_enum = SIFISO_app.ForEachEnumeratorInfos[“Foreach File Enumerator”];
ForEachEnumeratorHost f_enum_host = f_enum.CreateNew();
f_enum_host.CollectionEnumerator = false;
f_enum_host.Properties[“Directory”].SetValue(f_enum_host, @”C:\\TEMP\\”);
f_enum_host.Properties[“FileSpec”].SetValue(f_enum_host, @”selectSIFISO_Import_Test.txt”);
f_enum_host.Properties[“FileNameRetrieval”].SetValue(f_enum_host, 1);
f_enum_host.Properties[“Recurse”].SetValue(f_enum_host, “False”);
exec_SEQ.ForEachEnumerator = f_enum_host;
Add the following Execute SQL Tasks:
Executable exec = exec_SEQ.Executables.Add(“STOCK:SQLTask”);
TaskHost th = exec as TaskHost;
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”);
Executable exec2 = exec_SEQ.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”);
Join the Execute SQL Tasks:
PrecedenceConstraint pcFileTasks =
exec_SEQ.PrecedenceConstraints.Add((Executable)exec, (Executable)exec2);
pcFileTasks.Value = DTSExecResult.Success;
PrecedenceConstraint pcFileTasks2 =
dyna_pkg.PrecedenceConstraints.Add((Executable)exec_SEQ, (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.
Great post! I just added it to my bookmarks.
I appreciate it, many thanks
Pingback: Extract & Save SQL Statements of Execute SQL Tasks in a ForEach Loop Container using C#
Very interesting subject, regards for putting up.
thanks, Jannet
Sweet, that’s definitely what I was hunting for! You just spared me alot of looking around
my pleasure
I like you article,thank you very much!
I appreciate it, thanks Karen.
Wonderful text message plus good website.
thanks, Jimmy
It can be incredible how quite a few men and women do not understand this. Thank you for this incredibly informative write-up, and I appear forward to seeing much more inside the close to long term!
thanks
Keep up the amazing work!! I love how you wrote this and I also like the colors here on this site. Very good opinions expressed here 🙂
I appreciate the feedback, thanks Christian.
I appreciate, cause I found just what I was looking for. You have ended my 4 day long hunt! God Bless you man. Have a nice day. Bye
Hello there, simply turned into aware of your blog thru Google, and found that it is really informative. I?m gonna watch out for brussels. I will appreciate if you continue this in future. A lot of other folks will be benefited from your writing. Cheers!
Very helpful post man, thanks for the info.
I appreciate it, thank you
I simply adore your site! I couldn’t find a contact form so I hope you don’t mind me posting here. Wow, I had a great idea for your interesting little blog :). I’m an internet marketer and I spend a lot of time making and reading blogs like Create SSIS Package with SQL Tasks inside a ForEachLoop Container using C# all day. I used to just waste my energy making a few bucks here and there from google adsense but i found something SO much more powerful and better. I SWEAR I MADE $351.76 $$CASH$$ today just 3 days after modifying my blogs. Can you imagine how floored I am? Here is where I learned all my tricks: http://bit.ly/nmmEis Just giving your site a look I can tell you’ll probably pull in a few hundred bucks a month if you modify just a little code. I hope that it serves you well and you have all my blessings. Cheers! Michelle Mitchell
Saw a link to this post over at Reddit. Thanks for sharing it.
thanks, Earlie
Thanks for news. I Have been google for looking something like this.
thanks, Strona
This was refreshing. I wished I could analyze each post, but i’ve got to travel back to work now… However I am going to be back.
Thanks Rossie
I love it when people come together and share opinions, great blog, keep it up.
You guys have a great blog and some great content. Keep up the good work.
thanks Jeff
Wonderful items from you, man. I’ve be mindful your stuff prior to and you are just too fantastic. I really like what you have got here, really like what you’re stating and the best way by which you say it. You make it entertaining and you continue to take care of to stay it sensible. I can not wait to read far more from you. This is really a wonderful web site.
Hi. I just wanted to say thanks for the fantastic info you have posted here on your site. I will definitely come back to see it more often and have subscribed to your RSS feed. Have a fantastic day.
thank you
Your article seems interesting, i have noted it my digg and stumble account.The point you are making is easy to understand and effective.
thanks, Freda
Really interesting blog, keep up the good work!