Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Stan Kulp » Extract multiple files with a...
Add to briefcase

Extract multiple files with a MultiFlatFileConnection data flow task Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Stan Kulp-439977
Posted Thursday, October 20, 2011 12:08 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 79, Visits: 601
Comments posted to this topic are about the item Extract multiple files with a MultiFlatFileConnection data flow task
Post #1193345
M.SrinivasaRao
Posted Thursday, October 20, 2011 8:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 11, 2012 12:04 PM
Points: 3, Visits: 34
Can we use different distination tables in the same connection

Regards
Srini
Post #1193786
ahperez
Posted Thursday, October 20, 2011 2:08 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:22 AM
Points: 401, Visits: 485
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!
Post #1194007
mmartin1
Posted Thursday, October 20, 2011 3:06 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:53 AM
Points: 194, Visits: 643
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.
Post #1194038
Stan Kulp-439977
Posted Thursday, October 20, 2011 5:33 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 79, Visits: 601
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.
Post #1194077
ahperez
Posted Friday, October 21, 2011 7:37 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:22 AM
Points: 401, Visits: 485
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.
Post #1194376
OceanDeep
Posted Friday, October 21, 2011 10:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 20, 2013 2:17 PM
Points: 24, Visits: 179
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.
Post #1194538
Guillermo Bertolaccini
Posted Sunday, October 23, 2011 3:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 23, 2011 4:32 PM
Points: 9, Visits: 24
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... )
Post #1194990
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.