Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extract multiple files with a MultiFlatFileConnection data flow task Expand / Collapse
Author
Message
Posted Thursday, October 20, 2011 12:08 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, September 12, 2014 1:36 PM
Points: 128, Visits: 916
Comments posted to this topic are about the item Extract multiple files with a MultiFlatFileConnection data flow task
Post #1193345
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: Thursday, August 21, 2014 4:12 AM
Points: 3, Visits: 38
Can we use different distination tables in the same connection

Regards
Srini
Post #1193786
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: Monday, September 15, 2014 3:08 PM
Points: 440, Visits: 595
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
Posted Thursday, October 20, 2011 3:06 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 19, 2014 6:01 PM
Points: 356, Visits: 871
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
Posted Thursday, October 20, 2011 5:33 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, September 12, 2014 1:36 PM
Points: 128, Visits: 916
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
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: Monday, September 15, 2014 3:08 PM
Points: 440, Visits: 595
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
Posted Friday, October 21, 2011 10:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 9:57 AM
Points: 24, Visits: 252
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
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: Thursday, September 11, 2014 1:18 PM
Points: 9, Visits: 33
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