copying multiple files using file system task

  • elaine-131617

    Ten Centuries

    Points: 1066

    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

  • bc_

    Hall of Fame

    Points: 3106

    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: 1066

    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: 3106

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

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

  • elaine-131617

    Ten Centuries

    Points: 1066

    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: 3106

    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 6 (of 6 total)

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