Create SSIS Package with SQL Tasks inside a ForEachLoop Container using C#

35 Comments / Creating SSIS Packages, Execute SQL Task, ForEach Loop Container / By

Abstract
This article explains how to create an SSIS package with Execute SQL Tasks inside a ForEachLoop container using C# programming language.
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 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.

35 thoughts on “Create SSIS Package with SQL Tasks inside a ForEachLoop Container using C#”

  2. Pingback: Extract & Save SQL Statements of Execute SQL Tasks in a ForEach Loop Container using C#

  10. party dress

    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!

  12. Michelle Mitchell

    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

  18. cannabis seeds for sale

    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.

Leave a Comment