Extract multiple files with a MultiFlatFileConnection data flow task

Question

Post reply

Extract multiple files with a MultiFlatFileConnection data flow task

Stan Kulp-439977

SSChampion

Points: 10062
More actions
October 20, 2011 at 12:08 am

#245627

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

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

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

M.SrinivasaRao Grasshopper Points: 13 More actions · Answer 1

Can we use different distination tables in the same connection

Regards

Srini

ahperez SSC Eights! Points: 962 More actions · Answer 2

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: 27768 More actions · Answer 3

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.

----------------------------------------------------

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 4

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

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 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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... )