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

By | April 11, 2011

Abstract

This article explains how to create an SSIS package with Execute SQL Tasks inside a For Loop 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.”;

Declare variable that will be used by the For Loop container:
Variable Counter = dyna_pkg.Variables.Add(“Counter”, false, “User”, 0);

Insert the For Loop executable:
ForLoop exec_floop = (ForLoop)dyna_pkg.Executables.Add(“STOCK:FORLOOP”);
exec_floop.FailPackageOnFailure = true;
exec_floop.FailParentOnFailure = true;
exec_floop.Name = @”select SIFISO For Loop Container”;
exec_floop.Description = @”select SIFISO For Loop Container”;
exec_floop.InitExpression = “@[Counter] = 0”;
exec_floop.EvalExpression = “@[Counter] < 5"; exec_floop.AssignExpression = "@[Counter] = @[Counter]+ 1";

Add the Execute SQL Tasks inside the For Loop container:
Executable exec = exec_floop.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_floop.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”);

Join the two Execute SQL Tasks to each other:
PrecedenceConstraint pcFileTasks =
exec_floop.PrecedenceConstraints.Add((Executable)exec, (Executable)exec2);
pcFileTasks.Value = DTSExecResult.Success;

Insert another Execute SQL Tasks outside of the For Loop Container:
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 For Loop Container to the Execute SQL Tasks:
PrecedenceConstraint pcFileTasks2 =
dyna_pkg.PrecedenceConstraints.Add((Executable)exec_floop, (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.

8 thoughts on “Create SSIS Package with SQL Tasks inside a For Loop Container using C#

  1. forex expert advisor generator

    hey there and thanks to your information ? I have certainly picked up anything new from proper here. I did alternatively experience some technical issues using this web site, since I skilled to reload the website lots of times prior to I may get it to load properly. I were puzzling over if your web host is OK? Not that I’m complaining, but slow loading circumstances times will sometimes impact your placement in google and can damage your quality ranking if advertising and ***********

    Reply
  2. Valery Eliassen

    I love reading websites like these. It has been an amazing source of info. I could not get sites like these. Thanks once more.

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

Leave a Reply