Extract multiple files with a MultiFlatFileConnection data flow task

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9979

    Comments posted to this topic are about the item Extract multiple files with a MultiFlatFileConnection data flow task

  • M.SrinivasaRao

    Grasshopper

    Points: 13

    Can we use different distination tables in the same connection

    Regards

    Srini

  • ahperez

    SSC Eights!

    Points: 962

    Brilliant! I've read article after article looking for this trick and even taken a class that showed me a method using a "For Loop Container" to import at the files at once. This is the first time I've seen such a simple method. This will save me a huge amount of time.

    Thanks!

  • MMartin1

    One Orange Chip

    Points: 27502

    An advantage of a for each loop is being able to count how many files you imported from with a script task in the same loop. I didn't see that here. But this method is simpler and seemingly easier.

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9979

    You could create a separate script task that would count the files in the various subdirectories using an array list containing the subdirectories you want to search along with the System.IO.DirectoryInfo method and write the result to a global variable.

    Here is the code that you could use in the script task.

    Imports System.Collections

    Dim subdirs As New ArrayList

    subdirs.Add("C:\dir1\")

    subdirs.Add("C:\dir2\")

    subdirs.Add("C:\dir3\")

    subdirs.Add("C:\dir4\")

    subdirs.Add("C:\dir5\")

    Dim value As String

    For Each value In subdirs

    Dim di As New System.IO.DirectoryInfo(value )

    Dim aryFi As IO.FileInfo() = di.GetFiles("MyFileNameRoot*")

    Dim Count As Integer

    For Each fi In aryFi

    Count += 1

    Next

    Next

    Dts.Variables("Count").Value = Count

    You would have to create a global variable named Count and add it to the Read/Write variables in the Script Task Editor.

    To learn a enough about script tasks and global variables to make it work you could refer to my previous article

    Copy-and-Paste SSIS Execution Monitoring with Failure-Alert Email[/url].

  • ahperez

    SSC Eights!

    Points: 962

    mmartin1 (10/20/2011)


    An advantage of a for each loop is being able to count how many files you imported from with a script task in the same loop. I didn't see that here. But this method is simpler and seemingly easier.

    Ah, I could see that might be useful. My next step is to run a select query that audits the staging table where the files imported. This lets me know both that all the client files imported and how many rows there were for each type of record in the imported files.

  • OceanDeep

    Ten Centuries

    Points: 1078

    It is good to know we can do this. Thanks. Can this technqiue use in the reverse? The data source is SQL table and output is multiple flat files. I have 60 flat files and currently I have to create 60 of these connection.

  • Guillermo Bertolaccini

    Grasshopper

    Points: 17

    Was up when no files match the mask myFile*.txt ?

    When i do that with For Each file enumerator i can decide depending on the variable of forEach if the flow continues or not....

    How was the way to stop the flow in case that no files are found.... ?

    -Guille-

    (Excusme my english... )

Viewing 8 posts - 1 through 8 (of 8 total)

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