copying multiple files using file system task

Question

copying multiple files using file system task

elaine-131617

Ten Centuries

Points: 1067
More actions
June 2, 2009 at 2:27 pm

#131618

I need to copy a subset of files in a folder. The files to be copied share a common prefix iin the naming convention - date of the report + clientname + the generic name of the file. In dts I used to do it like this:
Dim oFS
Dim strSourcePath
Dim strDestinationPath
dim AddYear
dim AddMonth
dim AddDay
dim AddExtension
strSourcePath = "\\sourceServer\sourceFolder\"
strDestinationPath = "\\destinationServer\destinationFolder\"
set oFS = CreateObject("Scripting.FileSystemObject")
''Build File Name Extensions using the current date
AddYear = year(DTSGlobalVariables("StartDate").Value)
AddMonth = Month(DTSGlobalVariables("StartDate").Value)
AddName = trim(DTSGlobalVariables("CustomerName").Value)
AddDay = day(DTSGlobalVariables("StartDate").Value)
AddExtension = AddYear & AddMonth & AddDay & "_" & AddName
strSourcePath = strSourcePath & AddExtension
strSourcePath = strSourcePath & "_*.*"
oFS.CopyFile strSourcePath, strDestinationPath
Set oFS = Nothing
I'm having trouble in SSIS using the file system task copying a set of files. It seems to only work with using 1 file name as a source - I'm trying to do the *.* in the sourcevariable. I've seen examples using the script task to do something similar but when i tried that it also didn't like the *.* in File.Copy(strSourcePath, strDestinationPath).
I feel like this should be a fairly common task, but I'm missing something. Any suggestions?
Thanks
Elaine

Viewing 6 posts - 1 through 5 (of 5 total)

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

bc_ Hall of Fame Points: 3111 More actions · Answer 1

You can do it using a ForEach Loop Container using a File enumerator. Your File System Task would be inside the ForEach Loop. No scripting necessary, just some variables and expressions. If you google "ssis foreach file system task", you'll find several examples.

[font="Arial Narrow"]bc[/font]

elaine-131617 Ten Centuries Points: 1067 More actions · Answer 2

Thanks very much. I did find a good example here - http://rafael-salas.blogspot.com/2009/05/ssis-foreach-loop-looping-through.html

Inside the for each container, can the enumerator configuration folder be set to a variable?

bc_ Hall of Fame Points: 3111 More actions · Answer 3

Yes, with an expression. I've attached an image to demonstrate.

[font="Arial Narrow"]bc[/font]

elaine-131617 Ten Centuries Points: 1067 More actions · Answer 4

Thank you. I actually ended up using System.IO to loop through the files inside a script task and copy them - similar to what I was used to doing in DTS.

I think that your solution is probably better, just not as familiar to me, so I am going keep trying to do it with the file system task. Thank you very much for your help.

bc_ Hall of Fame Points: 3111 More actions · Answer 5

here is a really good demo.

http://www.jumpstarttv.com/using-the-file-system-task-in-ssis_508.aspx

[font="Arial Narrow"]bc[/font]