Dynamic List

Larry Charlton


Is there a way to create a dynamic list I have two variables I want to combine with two constants to create a list of 4 entries that I could run through a loop.

JayH


You can use a script task to populate an object variable with an array. Then you can use the Foreach from Variable Enumerator to loop through the array. The script to populate the array could look similar to this (using your variables in place of some of the constants).

Code Snippet

Public Sub Main()
Dim LoopItems(3) As String
LoopItems.SetValue("One", 0)
LoopItems.SetValue("Two", 1)
LoopItems.SetValue("Three", 2)
LoopItems.SetValue("Four", 3)
Dts.Variables("LoopItems").Value = LoopItems
Dts.TaskResult = Dts.Results.Success
End Sub

Alternatively, you could also use an Execute SQL task with a UNION statement to populate a object variable with a recordset of your values and then use the Foreach ADO Enumerator. You would create your SQL statement using an expression-based variable.

Larry Charlton

Thanks. I think creating a recordset in script might be my only alternative. I needed two columns for the four entries and For Each from a variable seems to only work with a single column. My problem was that I had two locations each of which had two types of files that needed deleting. The locations had to come from variables while the file types were constants. The entire solution in .Net was 7 more lines of code than you listed, I was just hoping to stay out of code.

Public Sub Main()

Dim directoryList As New List(Of String)

Dim patternList As New List(Of String)

directoryList.Add(CStr(Dts.Variables("principalLocation").Value))

directoryList.Add(CStr(Dts.Variables("mirrorLocation").Value))

patternList.Add("*.bak")

patternList.Add("*.trn")

For Each directoryItem As String In directoryList

For Each patternItem As String In patternList

For Each file As String In Directory.GetFiles(directoryItem, patternItem)

System.IO.File.Delete(file)

Next

Next

Next

Dts.TaskResult = Dts.Results.Success

End Sub

The problem is mostly that I can't have an empty directory or my daily maintenance will remove the directory. So I have a file in the directory called keepthis.txt. Another pure SSIS solution I guess would be to Delete the contents of the directory and then copy a keepthis.txt from somewhere else into that directory, I'd just have hope that the daily maintenance would never run at the same time this ran, something I'd rather not hope for with 12 people that can manually run this, usually under pressure.

Thanks for the solution though, I found the way you set array variables interesting, I hadn't seen that before!