June 2, 2009 at 2:27 pm
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
June 2, 2009 at 3:18 pm
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]
June 3, 2009 at 11:26 am
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?
June 3, 2009 at 11:40 am
Yes, with an expression. I've attached an image to demonstrate.
[font="Arial Narrow"]bc[/font]
June 3, 2009 at 1:25 pm
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.
June 5, 2009 at 10:36 am
here is a really good demo.
http://www.jumpstarttv.com/using-the-file-system-task-in-ssis_508.aspx
[font="Arial Narrow"]bc[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply