[Script] If there are more than 15 files in directory, proceed with DTS Package

Question

Post reply

[Script] If there are more than 15 files in directory, proceed with DTS Package

ricardo.laborde

Grasshopper

Points: 16
More actions
November 6, 2015 at 9:00 am

#322133

Hello, I am very new to SSIS and therefore I apologize if this might seem like a silly question, I currently have this Package https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png where I count the number of files in a folder. After it counts them it deletes the oldest file.
I want it to analyze the following: If there's more than 15 files, it will proceed with the package execution, if not, it will stop the package execution. I understand this should be done with the variable I had created to count how many files there are in the directory, but I don't understand how to script it. If anyone can point me in the right direction I'd be very grateful!
EDIT: I did it, turns out I was missing the + sign before the equal sign.
Dim Sum As Integer = 0
Dim di As New DirectoryInfo("C:\Users\ricardo.laborde\Documents\DeleteTest")
Dim fiArr As FileInfo() = di.GetFiles()
Dim fri As FileInfo
Public Sub Main()
For Each fri In fiArr
Sum += 1
Next fri
If Sum > 15 Then
' Add your code here
'
Dts.TaskResult = ScriptResults.Success
Else
Dts.TaskResult = ScriptResults.Failure
End If
End Sub

Viewing 3 posts - 1 through 2 (of 2 total)

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

michal.lisinski SSCertifiable Points: 7201 More actions · Answer 1

Hi Ricardo

If you simply want to end the task with failure, you can do this in this way:

Public Sub Main()

'Dim Sum As Integer = 0

Dim di As New DirectoryInfo("C:\Users\ricardo.laborde\Documents\DeleteTest")

Dim Sum As Integer = di.GetFiles().Length

'Dim fiArr As FileInfo() = di.GetFiles()

'Dim fri As FileInfo

'For Each fri In fiArr

'Sum += 1

'Next fri

If Sum > 15 Then

'MessageBox.Show(CStr(Sum))

Dts.TaskResult = ScriptResults.Success

Else

'MessageBox.Show(CStr(Sum))

Dts.TaskResult = ScriptResults.Failure

End If

End Sub

Besides the pass and fail, you also can handle a precedence constraint:

Public Sub Main()

'Dim Sum As Integer = 0

Dim di As New DirectoryInfo("C:\Users\ricardo.laborde\Documents\DeleteTest")

Dim Sum As Integer = di.GetFiles().Length

'Dim fiArr As FileInfo() = di.GetFiles()

'Dim fri As FileInfo

'MessageBox.Show(CStr(Sum))

Dts.Variables("FileCnt").Value = Sum

Dts.TaskResult = ScriptResults.Success

End Sub

Best regards,

Mike

ricardo.laborde Grasshopper Points: 16 More actions · Answer 2

michal.lisinski (11/9/2015)
Hi Ricardo
If you simply want to end the task with failure, you can do this in this way:
Public Sub Main()
'Dim Sum As Integer = 0
Dim di As New DirectoryInfo("C:\Users\ricardo.laborde\Documents\DeleteTest")
Dim Sum As Integer = di.GetFiles().Length
'Dim fiArr As FileInfo() = di.GetFiles()
'Dim fri As FileInfo
'For Each fri In fiArr
'Sum += 1
'Next fri
If Sum > 15 Then
'MessageBox.Show(CStr(Sum))
Dts.TaskResult = ScriptResults.Success
Else
'MessageBox.Show(CStr(Sum))
Dts.TaskResult = ScriptResults.Failure
End If
End Sub
Besides the pass and fail, you also can handle a precedence constraint:
Public Sub Main()
'Dim Sum As Integer = 0
Dim di As New DirectoryInfo("C:\Users\ricardo.laborde\Documents\DeleteTest")
Dim Sum As Integer = di.GetFiles().Length
'Dim fiArr As FileInfo() = di.GetFiles()
'Dim fri As FileInfo
'MessageBox.Show(CStr(Sum))
Dts.Variables("FileCnt").Value = Sum
Dts.TaskResult = ScriptResults.Success
End Sub
Best regards,
Mike

Thank you very much, this is a much simpler approach and much cleaner than mine!