January 21, 2011 at 4:48 pm
in my local drive, i have a directory, d:\data\ with unlimited subdirectories liked the following:
d:\data\a\a.txt
d:\data\a\a2.txt
d:\data\a\a3.txt
d:\data\b\b.txt
d:\data\b\b2.txt
d:\data\b\b3.txt
d:\data\c\cc.txt
d:\data\c\c2.txt
d:\data\d\d.txt
d:\data\d\d2.txt
I created a package with multiple flat file connections and each connection has a Data Flow Task and a Script Compont will look into the assigned connection and find the files, liked:
For Each fileName In Directory.GetFiles(Me.Connections.ImportFilesDir.AcquireConnection(Nothing).ToString())
Output0Buffer.AddRow()
Output0Buffer.Filename = fileName
Next
These filenames then will be worked on with Derived column and store into a SQL table.
I have got a new requirement to use only one flat file connection to look for the txt files.
I was trying to use a table to store each of the path, such as d:\data\a, d:\data\b\, d:\data\c and use ForEach to enumerate them. I don't know how pass the values, such as d:\data\a, d:\data\b, and etc to a DFT which will write the result into the SQL table.
Am I taking the correct approach?
Is there an example of something liked this on the web I can follow, I am new to this type of SSIS.
Thanks.
January 22, 2011 at 9:10 am
Do the text files all have the same structure (column names, column types and number of columns)?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 24, 2011 at 12:21 am
1) Run a query to get your folders from that table with an Execture SQL Task (resultset=Full result set) and store the result in a variable.
2) Use a Foreach Loop to enumerate through that resultset in the variable (Foreach ADO Enumerator)
3) Store the folder in a variable (Variable Mappings in the Foreach Loop)
4) Use a new Foreach Loop within the first Foreach Loop. That should be a Foreach File Enumerator. Use an expression to fill the directory/folder part of that foreach loop with the variable of step 3.
Alternative:
If all file are within a parent folder with multiple subfolders, you could use the Traverse subfolders option.
Joost
January 24, 2011 at 9:52 am
I think the first sentence of the original post
in my local drive, i have a directory, d:\data\ with unlimited subdirectories liked the following:
points to your alternative as being the way to go.
But if the file structures are different, neither of your methods will work.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 24, 2011 at 1:19 pm
Phil Parkin (1/24/2011)
But if the file structures are different, neither of your methods will work.
If the files have different stuctures, the foreach loop won't work. Unless there is some way to distinguish them with for example the filename or filepath.
Joost
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply