Gathering a List for the ForEach Loop with the Script Task
Applies To: SQL Server 2016 Preview
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.
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.
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.
Create a package variable named
FileAgeof 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.
Create a package variable named
FileListof type Object to receive the list of files gathered by the Script task for later use by the Foreach from Variable Enumerator.
FileAgevariable to the Script task's ReadOnlyVariables property, and add the
FileListvariable to the ReadWriteVariables property.
In your code, import the System.Collections and the System.IO namespaces.
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