Counting files in a for each loop

Question

Post reply

Counting files in a for each loop

Phil Church

SSC-Addicted

Points: 463
More actions
September 25, 2009 at 5:54 am

#156260

I have 27 files that need to be processed daily. The vendor that is sending me the files will send a done file for each file once they have finished transmitting the data file. I need to keep looking for the 27 Done files and then trigger the rest of my package to execute. I was thinking a foreach loop within a for loop, but I haven't found a way to pass a counter variable between the 2 looping containers.
I have added a script task to my for loop with the following code, but I can't get the local counter variable to not reset for each new file within the foreach loop
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Dim FileCount As Integer
Public Sub Main()
MsgBox("Before: " & FileCount)
FileCount += 1
MsgBox("After: " & FileCount)
End Sub
End Class

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

How are you scoping the variable you are passing?

I believe it should be scoped to the outer ForEach container or to the package.

Converting oxygen into carbon dioxide, since 1955.

Phil Church SSC-Addicted Points: 463 More actions · Answer 2

I'm simply trying to increment the counter, but it resets to zero with each new file. I'm using the file enumerator in the foreach loop and it's just the file name. Can I add more variables to the foreach loop?

kbatta SSCrazy Points: 2274 More actions · Answer 3

Try reading in the variable into your script, adding 1 to it, and writing it back out. Then the new value will be preserved.

Phil Church SSC-Addicted Points: 463 More actions · Answer 4

I have added the varFileCount to the readwritevariable, and I'm updating the counter using this code

Dim FileCount As Integer

FileCount += 1

Dts.Variables("varFileCount").Value = FileCount

I still can't get the varFileCount variable to not reset with each iteration of the foreach loop. What am I doing wrong?

kbatta SSCrazy Points: 2274 More actions · Answer 5

Set FileCount to the value in varFileCount. Then add one to it. Your present approach keeps adding 1 to zero.

Phil Church SSC-Addicted Points: 463 More actions · Answer 6

I'm getting an error that says Option Strict On disallows implicit conversions from 'Object' to 'Integer'.

How do I convert the object to an integer?

Phil Church SSC-Addicted Points: 463 More actions · Answer 7

This did the trick

FileCount = CType(Dts.Variables("varFileCount").Value, Integer)

FileCount += 1

Dts.Variables("varFileCount").Value = FileCount

thanks for all the help

kbatta SSCrazy Points: 2274 More actions · Answer 8

kbatta

SSCrazy

Points: 2274

September 25, 2009 at 9:37 am

#1058530

Happy to help!