SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extract multiple files with a MultiFlatFileConnection data flow task


Extract multiple files with a MultiFlatFileConnection data flow task

Author
Message
Stan Kulp-439977
Stan Kulp-439977
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1026 Visits: 1127
Comments posted to this topic are about the item Extract multiple files with a MultiFlatFileConnection data flow task
M.SrinivasaRao
M.SrinivasaRao
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 39
Can we use different distination tables in the same connection

Regards
Srini
ahperez
ahperez
SSC-Addicted
SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)

Group: General Forum Members
Points: 474 Visits: 635
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
MMartin1
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2821 Visits: 2031
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
Stan Kulp-439977
Stan Kulp-439977
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1026 Visits: 1127
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.
ahperez
ahperez
SSC-Addicted
SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)

Group: General Forum Members
Points: 474 Visits: 635
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
OceanDeep
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 298
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
Guillermo Bertolaccini
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 39
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... )
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search