June 19, 2015 at 7:28 am
Hi all
I've got an SSIS package that loads a block of CSV files from a specified folder and moves them to another folder after processing them. It then compresses the files and deletes the originals.
What I'd like to do is look for any subfolders and process the files in them first before moving on to the main folder (if that makes sense).
For example, with a folder tree like this:-
Main
Main
Sub1
Sub2
Sub3
Sub4
Sub5
I'd like the package to look in folder Sub1 (if it exists) and process the files in there.
Then go to Sub2 and process those files, etc., etc. before processing the files in the Main folder.
Is this possible in a FOREACH loop?
My current FOREACH loop container would process the files in Main before moving to Sub, Sub2, etc. and I want it to go the other way.
Anyone any ideas?
June 19, 2015 at 7:41 am
richardmgreen1 (6/19/2015)
Hi allI've got an SSIS package that loads a block of CSV files from a specified folder and moves them to another folder after processing them. It then compresses the files and deletes the originals.
What I'd like to do is look for any subfolders and process the files in them first before moving on to the main folder (if that makes sense).
For example, with a folder tree like this:-
Main
Main
Sub1
Sub2
Sub3
Sub4
Sub5
I'd like the package to look in folder Sub1 (if it exists) and process the files in there.
Then go to Sub2 and process those files, etc., etc. before processing the files in the Main folder.
Is this possible in a FOREACH loop?
My current FOREACH loop container would process the files in Main before moving to Sub, Sub2, etc. and I want it to go the other way.
Anyone any ideas?
Put the folder list, with the top-level folder at the end, into an object variable. You might be able to use an ExecuteSQL task with xp_DirTree to get a lot of this done.
Then use the Foreach ADO Enumerator with the object variable you have just populated.
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.
June 19, 2015 at 7:45 am
Phil Parkin (6/19/2015)
richardmgreen1 (6/19/2015)
Hi allI've got an SSIS package that loads a block of CSV files from a specified folder and moves them to another folder after processing them. It then compresses the files and deletes the originals.
What I'd like to do is look for any subfolders and process the files in them first before moving on to the main folder (if that makes sense).
For example, with a folder tree like this:-
Main
Main
Sub1
Sub2
Sub3
Sub4
Sub5
I'd like the package to look in folder Sub1 (if it exists) and process the files in there.
Then go to Sub2 and process those files, etc., etc. before processing the files in the Main folder.
Is this possible in a FOREACH loop?
My current FOREACH loop container would process the files in Main before moving to Sub, Sub2, etc. and I want it to go the other way.
Anyone any ideas?
Put the folder list, with the top-level folder at the end, into an object variable. You might be able to use an ExecuteSQL task with xp_DirTree to get a lot of this done.
Then use the Foreach ADO Enumerator with the object variable you have just populated.
If xp_DirTree won't do what you want, then you could use a VB or C# script to populate that same object variable.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 19, 2015 at 7:55 am
Alvin Ramard (6/19/2015)
Phil Parkin (6/19/2015)
richardmgreen1 (6/19/2015)
Hi allI've got an SSIS package that loads a block of CSV files from a specified folder and moves them to another folder after processing them. It then compresses the files and deletes the originals.
What I'd like to do is look for any subfolders and process the files in them first before moving on to the main folder (if that makes sense).
For example, with a folder tree like this:-
Main
Main
Sub1
Sub2
Sub3
Sub4
Sub5
I'd like the package to look in folder Sub1 (if it exists) and process the files in there.
Then go to Sub2 and process those files, etc., etc. before processing the files in the Main folder.
Is this possible in a FOREACH loop?
My current FOREACH loop container would process the files in Main before moving to Sub, Sub2, etc. and I want it to go the other way.
Anyone any ideas?
Put the folder list, with the top-level folder at the end, into an object variable. You might be able to use an ExecuteSQL task with xp_DirTree to get a lot of this done.
Then use the Foreach ADO Enumerator with the object variable you have just populated.
If xp_DirTree won't do what you want, then you could use a VB or C# script to populate that same object variable.
In other words: "Rather than using a dodgy undocumented T-SQL hack, you could do it the proper way":-) This is better.
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.
June 19, 2015 at 8:09 am
Phil Parkin (6/19/2015)
Alvin Ramard (6/19/2015)
...If xp_DirTree won't do what you want, then you could use a VB or C# script to populate that same object variable.
In other words: "Rather than using a dodgy undocumented T-SQL hack, you could do it the proper way":-) This is better.
Phil, that's not what I meant to say, but you may be right. I just wanted to point out that there was an alternative.
On the subject of undocumented stored procedures, I've always wondered why they're undocumented if they're included with SQL Server. I can't help but wonder if they sometimes might have unexpected, or erroneous, results.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 19, 2015 at 8:19 am
Alvin Ramard (6/19/2015)
Phil Parkin (6/19/2015)
Alvin Ramard (6/19/2015)
...If xp_DirTree won't do what you want, then you could use a VB or C# script to populate that same object variable.
In other words: "Rather than using a dodgy undocumented T-SQL hack, you could do it the proper way":-) This is better.
Phil, that's not what I meant to say, but you may be right. I just wanted to point out that there was an alternative.
On the subject of undocumented stored procedures, I've always wondered why they're undocumented if they're included with SQL Server. I can't help but wonder if they sometimes might have unexpected, or erroneous, results.
I've seen enough of your posts to know that you didn't mean it, or I wouldn't have responded like that 🙂
Those undocumented procs always seem to work and carry on working for me. It would be interesting to hear of examples of them changing in undocumented and unsupported ways.
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.
June 19, 2015 at 8:51 am
Thanks for the info folks (and for the quick replies).
I suppose I should have explained a bit more (so apologies for that).
The molder will always be called Temp. Unfortunately, any sub-folders will be system generated and have a naming convention of YYYYMMDD_HHMM so I won't always know what they will be.
I'm assuming that will make a big difference, but I'd be glad of any replies/pointers.
June 19, 2015 at 9:07 am
No difference at all. Both of the proposed ideas pick up whatever is there at run time.
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.
June 19, 2015 at 9:10 am
Phil Parkin (6/19/2015)
Alvin Ramard (6/19/2015)
Phil Parkin (6/19/2015)
Alvin Ramard (6/19/2015)
...If xp_DirTree won't do what you want, then you could use a VB or C# script to populate that same object variable.
In other words: "Rather than using a dodgy undocumented T-SQL hack, you could do it the proper way":-) This is better.
Phil, that's not what I meant to say, but you may be right. I just wanted to point out that there was an alternative.
On the subject of undocumented stored procedures, I've always wondered why they're undocumented if they're included with SQL Server. I can't help but wonder if they sometimes might have unexpected, or erroneous, results.
I've seen enough of your posts to know that you didn't mean it, or I wouldn't have responded like that 🙂
Those undocumented procs always seem to work and carry on working for me. It would be interesting to hear of examples of them changing in undocumented and unsupported ways.
Don't worry Phil. I've seen enough of your posts to that I understood what you meant. 🙂
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply