April 7, 2010 at 3:30 am
hi,
I am using a Script Task to check if a file(s) exist before the package runs, but am having a small problem... The file name varies from month to month.
I can set the script task to check for a specific filename, but how do I modify the task to check for wildcards??? :crazy:
I have taken the script from the following author..
My Script task uses the exact same setup as in the link, but with the FileName variable set to a value of "sw*.txt" - obviously not correct. Each month a new file comes in, with the month and year e.g. swjan08.txt, swfeb08.txt and so on.
I just want the script task to check if there is one file present, then the following For Each Loop Task will run through al the files in the folder and import their data.
The script I am using is as follows....
Public Class ScriptMain
Public Sub Main()
Dim fileLoc, fileName As String
If Dts.Variables.Contains("User::ExecDir") = True AndAlso _
Dts.Variables.Contains("User::FileName") = True Then
fileLoc = CStr(Dts.Variables("User::ExecDir").Value)
fileName = CStr(Dts.Variables.Item("User::FileName").Value)
System.Windows.Forms.MessageBox.Show("FileDir:" + fileLoc + " FileName:" + fileName)
If File.Exists(fileLoc + fileName) Then
Dts.Variables.Item("User::FileExists").Value = True
System.Windows.Forms.MessageBox.Show("File exists")
Else
Dts.Variables.Item("User::FileExists").Value = False
System.Windows.Forms.MessageBox.Show("File does not exist")
End If
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
End Class
Can anyone help me out here....
Thx
April 7, 2010 at 7:09 am
Actually now decided that if the For Each Loop is taking care of sweeping through the folder, I don't actually need the Script task.
Would be nice to know how to get it to actually work though.... 🙂
April 7, 2010 at 9:27 am
You could use a script task or an EXEC SQL task to manipulate a variable and then using expressions assign it to the file pattern. Off the top of my head that is how I would do it..
CEWII
April 8, 2010 at 1:09 am
That is what I have described in the first post...
I can make the script so it searches for a particular file, but when the file (or files) have different names, the script does not recognize the files....
April 8, 2010 at 1:38 am
Jason Coleman (4/8/2010)
That is what I have described in the first post...I can make the script so it searches for a particular file, but when the file (or files) have different names, the script does not recognize the files....
This code will iterate around all of the files in a specific folder - so you can do what you want with the results ...
Try
For Each foundFile As String In My.Computer.FileSystem.GetFiles("C:\Users", Microsoft.VisualBasic.FileIO.SearchOption.SearchTopLevelOnly, "*.*")
Debug.Print(foundFile)
Next
Catch ex As Exception
Throw ex
End Try
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 9, 2010 at 2:40 am
To answer Jason's question, here's a script that searches for all files that match a specified filename with wildcards.
The filename with the wildcards is stored in a variable. Another variable holds the filetype. (for easy reconfiguring of the package)
Example of the variables:
MyFileName: *SalesFigures
MyFileType: *.xls
This allows for searching for all Excel files with the word SalesFigures in it.
Public Sub Main()
' Create a variable to hold the directory:
Dim di As New IO.DirectoryInfo(Dts.Variables("MyDirectory").Value.ToString)
' Create an array with all the files found in the directory that matches the search pattern.
Dim aryFi As IO.FileInfo() = di.GetFiles(Dts.Variables("MyFileName").Value.ToString + Dts.Variables("MyFileType").Value.ToString)
'Create a variable to serve as an iterator through the for loop.
Dim fi As IO.FileInfo
' Check if there are any matching files at all. If not, the package fails.
If aryFi.Length() > 0 Then
' Move all the files in the array to the right directory.
For Each fi In aryFi
'Create the destination file path.
Dim newfs As String = Path.Combine(CStr(Dts.Variables("MyArchive").Value), fi.Name())
fi.CopyTo(newfs, True) 'Copy the file to the new destination.
fi.Delete() 'Delete the file.
Next
'The script and the package succeed.
Dts.TaskResult = Dts.Results.Success
Else
'Fail the script and the package.
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 9, 2010 at 6:29 am
Thx - that looks great! :w00t:
Gonna give it a try on Monday (other priorities have shifted it from today) and let you know
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply