SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Stan Kulp / Extract multiple files with a MultiFlatFileConnection data flow task / Latest Posts

RE: Extract multiple files with a MultiFlatFileConnection data flow task

Guillermo Bertolaccini — Sun, 23 Oct 2011 15:16:56 GMT

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

RE: Extract multiple files with a MultiFlatFileConnection data flow task

OceanDeep — Fri, 21 Oct 2011 10:12:05 GMT

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.

RE: Extract multiple files with a MultiFlatFileConnection data flow task

ahperez — Fri, 21 Oct 2011 07:37:40 GMT

[quote][b]mmartin1 (10/20/2011)[/b][hr]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.[/quote]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.

RE: Extract multiple files with a MultiFlatFileConnection data flow task

Stan Kulp-439977 — Thu, 20 Oct 2011 17:33:38 GMT

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 = CountYou 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 [url=http://www.sqlservercentral.com/articles/Execution+Logging/69858/]Copy-and-Paste SSIS Execution Monitoring with Failure-Alert Email[/url].

RE: Extract multiple files with a MultiFlatFileConnection data flow task

mmartin1 — Thu, 20 Oct 2011 15:06:01 GMT

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.

RE: Extract multiple files with a MultiFlatFileConnection data flow task

ahperez — Thu, 20 Oct 2011 14:08:07 GMT

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!

RE: Extract multiple files with a MultiFlatFileConnection data flow task

M.SrinivasaRao — Thu, 20 Oct 2011 08:48:46 GMT

Can we use different distination tables in the same connectionRegardsSrini

Extract multiple files with a MultiFlatFileConnection data flow task

Stan Kulp-439977 — Thu, 20 Oct 2011 00:08:43 GMT

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