How to you iterate through folders with SSIS?

Question

How to you iterate through folders with SSIS?

WebTechie

SSChampion

Points: 11405
More actions
January 21, 2011 at 12:54 pm

#148208

Hello,
I have two back up servers. I have various folders on each server. In each folder, I have backup files.
I need to move the backup files for the previous day to a special folder.
Backup server #1
foldera
bak -- folder with bak files
trn -- folder with trn files
folderb
bak
trn
folderc
bak
files
special folder
bak
trn
Backup server #2
foldera
bak -- folder with bak files
trn -- folder with trn files
folderb
bak
trn
folderc
bak
files
special folder
bak
trn
I'm using SSIS 2008.
1) How would I loop through the folders on each server.
2) How do you get a subfolder name.
Thank you for pointing me in the right direction.
Tony
Things will work out. Get back up, change some parameters and recode.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

One option that comes to mind is to enumerate the folder names in that directory into an object variable and then perform a foreach loop on each of the folders (in the variable now) and perform the file copy task.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

GSquared SSC Guru Points: 260824 More actions · Answer 2

The For...Each loops in SSIS can specify that they are for each file. There's an option for including subfolders.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

GSquared (1/21/2011)
The For...Each loops in SSIS can specify that they are for each file. There's an option for including subfolders.

That works too;-)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WebTechie SSChampion Points: 11405 More actions · Answer 4

I checked the box for traverse sub folders. When debugging, it told me it didn't find any files. That means it didn't traverse the subfolders, because I know there are files in the sub folders.

Not sure how to make it work and find files that in sub folders and to know what sub folder it was found in?

Thanks for responding. I appreciate any and all help in this.

Things will work out. Get back up, change some parameters and recode.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

GSquared (1/21/2011)
The For...Each loops in SSIS can specify that they are for each file. There's an option for including subfolders.

This is how I would do it. It requires almost no code to set it up and get it working..

CEWII

WebTechie SSChampion Points: 11405 More actions · Answer 6

I appreciate the response, but that didn't work for me.

Things will work out. Get back up, change some parameters and recode.

GSquared SSC Guru Points: 260824 More actions · Answer 7

WebTechie38 (1/21/2011)
I checked the box for traverse sub folders. When debugging, it told me it didn't find any files. That means it didn't traverse the subfolders, because I know there are files in the sub folders.
Not sure how to make it work and find files that in sub folders and to know what sub folder it was found in?
Thanks for responding. I appreciate any and all help in this.

Set up the loop to either output (in debug mode) or log the folders that it goes through. In debug mode, you can see the values of variables, et al. That'll tell you what it's doing internally.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

Why don't you explain what part of it didn't work. The "it didn't work" response doesn't really help us help you..

CEWII

WebTechie SSChampion Points: 11405 More actions · Answer 9

Elliot,

Thanks for responding.

I have two backup servers. The admins didn't want to give us DBAs full access. So they created backup shares. We have full admin to those shares. Under those shares, I've created a folder for my BAK, TRN and DIF files.

Server a

---Database a

------BAK Files

------TRN Files

------DIFF Foiles

---Database b

I was thinking if I put in the servername (i.e. \\servera), that I will get it to traverse all the share folders. After working with it, I didn't feel it could do that.

My Alternative was to hard code the database folder names into a table. I now use iterate through rows in the table to get the next Database files to move.

That seems to work.

Thanks for responding though.

Things will work out. Get back up, change some parameters and recode.