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
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 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply