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

By | April 11, 2011

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

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

  1. Freda Rudge

    Your article seems interesting, i have noted it my digg and stumble account.The point you are making is easy to understand and effective.

    Reply
  2. mansfield web design

    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.

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

    Reply
  4. Rossie

    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.

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

    Reply
  6. 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!

    Reply
  7. Lucien Maslak

    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

    Reply
  8. 3D E Charm,3D E Charm

    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!

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

Leave a Reply