[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

• michal.lisinski

  SSCertifiable

  Points: 7201

  More actions

  November 9, 2015 at 2:45 am

  #1838490

  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

  November 9, 2015 at 10:46 am

  #1838627

  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!