Skip current log file in For Each loop

Question

Post reply

Skip current log file in For Each loop

lk-681111

SSCrazy

Points: 2915
More actions
June 16, 2010 at 8:58 am

#140937

Hello:
I have an SSIS package that imports log files into a SQL database. The package has a For Each loop container in which I've placed a Script Task to pull the file names and a Data Flow Task to import the data. Everything works great until the loop reaches the current file, and then the package fails because that file is still in use. Here's the code I'm using in the Script Task:
Public Sub Main()
Dts.Variables("fileName").Value = "\\ServerName\FolderName\" + System.IO.Path.GetFileName(Dts.Variables("fileName").Value.ToString())
Dts.TaskResult = Dts.Results.Success
End Sub
Does anyone know of a way that I can make it ignore the file that's currently being used? If it helps, the file I want to skip will have been created on the current date.
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

you could use the GetCreationTime method of the file class to detemine if it was created today and then skip the file

lk-681111 SSCrazy Points: 2915 More actions · Answer 2

Hi, Steve:

Thanks for your response! I tried something like this:

Public Sub Main()

Dts.Variables("fileName").Value = "\\ServerName\FolderName\" + System.IO.Path.GetFileName(Dts.Variables("fileName").Value.ToString())

If System.IO.File.GetCreationTime("fileName") < DateTime.Today Then

Dts.TaskResult = Dts.Results.Success

End If

End Sub

...but I really don't know what I'm doing and the syntax is obviously wrong. I guess I don't really understand enough about how the For Each loop works to know where I should be checking for the creation date. Can you help me out? Thanks!

steveb. SSC-Forever Points: 46733 More actions · Answer 3

From a quick look that syntax seems okay, i would create a variable called something like 'ValidFileToLoad' .

And then put the script inside the loop , so the loop would call the script first and populate the variable and then use this varaible as a precendent constraint on the data-flow part of the loop

lk-681111 SSCrazy Points: 2915 More actions · Answer 4

Hi, Steve:

I added a boolean variable called isCurrentLog and placed it in the ReadWriteVariables of my Script Task. I changed the script code to this:

Public Sub Main()

Dts.Variables("fileName").Value = "\\ServerName\FolderName\" + System.IO.Path.GetFileName(Dts.Variables("fileName").Value.ToString())

If System.IO.File.GetCreationTime("fileName") < DateTime.Today Then

Dts.Variables("isCurrentLog").Value = False

Else

Dts.Variables("isCurrentLog").Value = True

End If

Dts.TaskResult = Dts.Results.Success

End Sub

I added this precedence constraint between my Script Task and my Data Flow Task within the For Each loop: @[User::isCurrentLog]==False. But when I ran the package, it still failed trying to open the current log file. I feel like I'm close, but maybe I'm just not putting everything in the proper order.

steveb. SSC-Forever Points: 46733 More actions · Answer 5

You may have to do some debugging on the package to see why it is failing , put in a breakpoint on on the IF statement in script task to see if the variable is getting populated correctly.