finding filepath, subdirectories, and filenames

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » finding filepath, subdirectories, and...

finding filepath, subdirectories, and filenames

Rate Topic

Display Mode

Topic Options

Author

Message

light_wt

Posted Friday, January 21, 2011 4:48 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 02, 2013 9:15 AM
Points: 165, Visits: 258

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.

Post #1051888

Phil Parkin

Posted Saturday, January 22, 2011 9:10 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 4,240, Visits: 9,487

Do the text files all have the same structure (column names, column types and number of columns)?

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1051963

lijnenjoost

Posted Monday, January 24, 2011 12:21 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 11:59 PM
Points: 22, Visits: 184

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
http://microsoft-ssis.blogspot.com/

Post #1052200

Phil Parkin

Posted Monday, January 24, 2011 9:52 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 4,240, Visits: 9,487

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.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1052508

lijnenjoost

Posted Monday, January 24, 2011 1:19 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 11:59 PM
Points: 22, Visits: 184

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
http://microsoft-ssis.blogspot.com/[url=http://microsoft-ssis.blogspot.com/][/url]

Post #1052657

« Prev Topic | Next Topic »

Permissions