SSIS Package Foreach Loop with File Connection

SSIS Package Foreach Loop with File Connection.

This package gives you an idea to get file details and variable mapping between Foreach Loop Container and any inner task contains in loop (here inner task is Script Task).

Step1 : Drag Foreach Loop and script task from SSIS Toolbox.

ForEachLoopWithFile1

Step2 : Create one variable with name “FileName” and datatype “string”.
this varialbe maps filename from Foreach Loop Container value to Script task.

Step3 : Right click on Foreach Loop Container –> Edit
On Collection pane –> select Foreach File Enumerator in Enumerator field.
choose your folder by clicking on browse button.
–> Select file extention if you want to filter file extention otherwise put *.* to get all files.
–> Select Name and Extention in Retrieve file name.
ForEachLoopWithFile2

Step4 : Select Variable mapping pane on Foreach Loop Editor.
–> Select User::FileName variable.
–> and Index to 0.
ForEachLoopWithFile3

Step5 : Right Click on Script Task –> Edit.
–> Select Script Pane –> Select User::FileName as ReadOnlyVarialbe.

Step6 : Click on Edit Scrit to write script code.

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

namespace ST_f1f3822404bc4e8d9a22fbe2749ff1d9
{
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{
		public void Main()
		{
            string FileName;
            FileName = Dts.Variables["User::FileName"].Value.ToString();
            MessageBox.Show(FileName);
			Dts.TaskResult = (int)ScriptResults.Success;
		}

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

	}
}

Regards,
Nirav gajjar