SSIS Package Foreach Loop with ADO Connection.

In my last blog i had written SSIS Package Foreach Loop with File Connection(Link: http://nirav.extreme-advice.com/2013/02/25/ssis-package-foreeach-loop-with-file-connection/).

This package gives you same thing, variable mapping between Foreach Loop Container and any inner task contains in loop (here inner task is Script Task) but here i have used ADO Connection as enumerator in behalf of File Connection.

Step1 : Drag

–> Execute SQL Task to get list of values from database table and import to object variable

–> Foreach Loop Container.

–> Script Task.

Step2 : Create Variables.

–> Obj_Result – Data Type Object.

–>ServerName – Data Type String.

–> DataSource – Data Type String.

Step3 : Right Click on Execute SQL Task –> Edit.

–> Result Set – Full Result Text.

–> Connection – Your local database connection.

–> SQL Statement – Select Name,Data_Source From Sys.Servers.

Step4 : Select Result Set from your left pane of Execute SQL Task Editor.

–> Result Name : 0 (Zero).

–> Variable Name : User::Obj_Result.

Step5 : Right click on Foreach Loop Container –> Edit.

–> Select Foreach ADO Enumerator.

–> User::Obj_Result.

Step6 : Select Variable mapping from your left pane of Foreach Loop Editor.

–> Select User::ServerName with Index 0.

–> Select User::DataSource with Index 1.

Step7 : Rigth click on Script Tak –> Edit.

–> ReadOnlyvariables – Select Both Variable shown in below figure.

Step8 : Click on Edit Script and paste below code.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion

namespace ST_f67917be72d748f4a19778c4b647dbf0
{
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {
  public void Main()
  {
            string ServerName, DataSource;
            ServerName = Dts.Variables["User::ServerName"].Value.ToString();
            DataSource = Dts.Variables["User::DataSource"].Value.ToString();
            MessageBox.Show("Server Name : " + ServerName + " Data Source : " + DataSource);
   Dts.TaskResult = (int)ScriptResults.Success;
  }

        #region ScriptResults declaration
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}

This script will show you how to use variables and how to map variables between Execute SQL Task to Foreach Loop Container and Foreach Loop Container to Inner Script Task.

Regards,

Nirav Gajjar