June 16, 2010 at 8:58 am
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!
June 16, 2010 at 10:04 am
you could use the GetCreationTime method of the file class to detemine if it was created today and then skip the file
June 16, 2010 at 10:09 am
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!
June 16, 2010 at 10:32 am
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
June 16, 2010 at 11:14 am
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.
June 17, 2010 at 3:18 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply