SSIS Package Foreach Loop with ADO Connection

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.

ForEachLoopWithADO1

Step2 : Create Variables.

–> Obj_Result – Data Type Object.

–>ServerName – Data Type String.

–> DataSource – Data Type String.

ForEachLoopWithADO2

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.

ForEachLoopWithADO3

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

–> Result Name : 0 (Zero).

–> Variable Name : User::Obj_Result.

ForEachLoopWithADO4

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

–> Select Foreach ADO Enumerator.

–> User::Obj_Result.

ForEachLoopWithADO5

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

–> Select User::ServerName with Index 0.

–> Select User::DataSource with Index 1.

ForEachLoopWithADO6

Step7 : Rigth click on Script Tak –> Edit.

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

ForEachLoopWithADO7

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