Gathering a List for the ForEach Loop with the Script Task

The Foreach from Variable Enumerator enumerates over the items in a list that is passed to it in a variable and performs the same tasks on each item. You can use custom code in a Script task to populate a list for this purpose. For more information about the enumerator, see Foreach Loop Container.

Note

If you want to create a task that you can more easily reuse across multiple packages, consider using the code in this Script task sample as the starting point for a custom task. For more information, see Developing a Custom Task.

Description

The following example uses methods from the System.IO namespace to gather a list of Excel workbooks on the computer that are either newer or older than a number of days specified by the user in a variable. It searches directories on Drive C recursively for files that have the .xls extension and examines the date on which each file was last modified to determine whether the file belongs in the list. It adds qualifying files to an ArrayList and saves the ArrayList to a variable for later use in a Foreach Loop container. The Foreach Loop container is configured to use the Foreach from Variable enumerator.

Note

The variable that you use with the Foreach from Variable Enumerator must be of type Object. The object that you place in the variable must implement one of the following interfaces: System.Collections.IEnumerable, System.Runtime.InteropServices.ComTypes.IEnumVARIANT, System.ComponentModel IListSource, or Microsoft.SqlServer.Dts.Runtime.Wrapper.ForEachEnumeratorHost. An Array or ArrayList is commonly used. The ArrayList requires a reference and an Imports statement for the System.Collections namespace.

You can experiment with this task by using different positive and negative values for the FileAge package variable. For example, you can enter 5 to search for files created in the last five days, or enter -3 to search for files that were created more than three days ago. This task may take a minute or two on a drive with many folders to search.

To configure this Script Task example

  1. Create a package variable named FileAge of type integer and enter a positive or negative integer value. When the value is positive, the code searches for files newer than the specified number of days; when negative, for files older than the specified number of days.

  2. Create a package variable named FileList of type Object to receive the list of files gathered by the Script task for later use by the Foreach from Variable Enumerator.

  3. Add the FileAge variable to the Script task's ReadOnlyVariables property, and add the FileList variable to the ReadWriteVariables property.

  4. In your code, import the System.Collections and the System.IO namespaces.

Code

Imports System  
Imports System.Data  
Imports System.Math  
Imports Microsoft.SqlServer.Dts.Runtime  
Imports System.Collections  
Imports System.IO  

Public Class ScriptMain  

  Private Const FILE_AGE As Integer = -50  

  Private Const FILE_ROOT As String = "C:\"  
  Private Const FILE_FILTER As String = "*.xls"  

  Private isCheckForNewer As Boolean = True  
  Dim fileAgeLimit As Integer  
  Private listForEnumerator As ArrayList  

  Public Sub Main()  

    fileAgeLimit = DirectCast(Dts.Variables("FileAge").Value, Integer)  

    ' If value provided is positive, we want files NEWER THAN n days.  
    '  If negative, we want files OLDER THAN n days.  
    If fileAgeLimit < 0 Then  
      isCheckForNewer = False  
    End If  
    ' Extract number of days as positive integer.  
    fileAgeLimit = Math.Abs(fileAgeLimit)  

    listForEnumerator = New ArrayList  

    GetFilesInFolder(FILE_ROOT)  

    ' Return the list of files to the variable  
    '  for later use by the Foreach from Variable enumerator.  
    System.Windows.Forms.MessageBox.Show("Matching files: " & listForEnumerator.Count.ToString, "Results", Windows.Forms.MessageBoxButtons.OK, Windows.Forms.MessageBoxIcon.Information)  
    Dts.Variables("FileList").Value = listForEnumerator  

    Dts.TaskResult = ScriptResults.Success  

  End Sub  

  Private Sub GetFilesInFolder(ByVal folderPath As String)  

    Dim localFiles() As String  
    Dim localFile As String  
    Dim fileChangeDate As Date  
    Dim fileAge As TimeSpan  
    Dim fileAgeInDays As Integer  
    Dim childFolder As String  

    Try  
      localFiles = Directory.GetFiles(folderPath, FILE_FILTER)  
      For Each localFile In localFiles  
        fileChangeDate = File.GetLastWriteTime(localFile)  
        fileAge = DateTime.Now.Subtract(fileChangeDate)  
        fileAgeInDays = fileAge.Days  
        CheckAgeOfFile(localFile, fileAgeInDays)  
      Next  

      If Directory.GetDirectories(folderPath).Length > 0 Then  
        For Each childFolder In Directory.GetDirectories(folderPath)  
          GetFilesInFolder(childFolder)  
        Next  
      End If  

    Catch  
      ' Ignore exceptions on special folders such as System Volume Information.  
    End Try  

  End Sub  

  Private Sub CheckAgeOfFile(ByVal localFile As String, ByVal fileAgeInDays As Integer)  

    If isCheckForNewer Then  
      If fileAgeInDays <= fileAgeLimit Then  
        listForEnumerator.Add(localFile)  
      End If  
    Else  
      If fileAgeInDays > fileAgeLimit Then  
        listForEnumerator.Add(localFile)  
      End If  
    End If  

  End Sub  

End Class  
using System;  
using System.Data;  
using System.Math;  
using Microsoft.SqlServer.Dts.Runtime;  
using System.Collections;  
using System.IO;  

public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase  
    {  

        private const int FILE_AGE = -50;  

        private const string FILE_ROOT = "C:\\";  
        private const string FILE_FILTER = "*.xls";  

        private bool isCheckForNewer = true;  
        int fileAgeLimit;  
        private ArrayList listForEnumerator;  

        public void Main()  
  {  

    fileAgeLimit = (int)(Dts.Variables["FileAge"].Value);  

    // If value provided is positive, we want files NEWER THAN n days.  
    // If negative, we want files OLDER THAN n days.  
    if (fileAgeLimit<0)  
    {  
      isCheckForNewer = false;  
    }  
    // Extract number of days as positive integer.  
    fileAgeLimit = Math.Abs(fileAgeLimit);  

    ArrayList listForEnumerator = new ArrayList();  

    GetFilesInFolder(FILE_ROOT);  

    // Return the list of files to the variable  
    // for later use by the Foreach from Variable enumerator.  
    System.Windows.Forms.MessageBox.Show("Matching files: "+ listForEnumerator.Count, "Results",   
MessageBoxButtons.OK, MessageBoxIcon.Information);  
    Dts.Variables["FileList"].Value = listForEnumerator;  

    Dts.TaskResult = (int)ScriptResults.Success;  

  }  

        private void GetFilesInFolder(string folderPath)  
        {  

            string[] localFiles;  
            DateTime fileChangeDate;  
            TimeSpan fileAge;  
            int fileAgeInDays;  

            try  
            {  
                localFiles = Directory.GetFiles(folderPath, FILE_FILTER);  
                foreach (string localFile in localFiles)  
                {  
                    fileChangeDate = File.GetLastWriteTime(localFile);  
                    fileAge = DateTime.Now.Subtract(fileChangeDate);  
                    fileAgeInDays = fileAge.Days;  
                    CheckAgeOfFile(localFile, fileAgeInDays);  
                }  

                if (Directory.GetDirectories(folderPath).Length > 0)  
                {  
                    foreach (string childFolder in Directory.GetDirectories(folderPath))  
                    {  
                        GetFilesInFolder(childFolder);  
                    }  
                }  

            }  
            catch  
            {  
                // Ignore exceptions on special folders, such as System Volume Information.  
            }  

        }  

        private void CheckAgeOfFile(string localFile, int fileAgeInDays)  
        {  

            if (isCheckForNewer)  
            {  
                if (fileAgeInDays <= fileAgeLimit)  
                {  
                    listForEnumerator.Add(localFile);  
                }  
            }  
            else  
            {  
                if (fileAgeInDays > fileAgeLimit)  
                {  
                    listForEnumerator.Add(localFile);  
                }  
            }  

        }  

    }  

See Also

Foreach Loop Container
Configure a Foreach Loop Container