Move Multiple Files to Different Destinations

Question

Post reply

Move Multiple Files to Different Destinations

Steve Hindle-535638

SSCrazy

Points: 2378
More actions
December 2, 2010 at 6:59 am

#149425

I am trying to loop through a Source folder (*.txt files) and move all files found to different but predefined destination folders.
I have managed to do this using an ADO recordeset / ForEach loop where I only have a single source file, but cannot get it to work for multiple source files. I am trying the latter way because a) the source file name will change each day and b) there may be more than one file in there at any one time so I need to iterate through the folder contents.
Any pointers greatly appreciated.
Steve

Viewing 15 posts - 1 through 15 (of 17 total)

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

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

This can definitely be done. What problem are you having?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Hindle-535638 SSCrazy Points: 2378 More actions · Answer 2

It was assigning the source and destination paths to an object variable and looping through them via a ForEach loop / FST using these variables.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

So do these variables merely contain folder paths and you want your task to loop round all files in <source_folder> and move them to <destination_folder>?

Or do the values of the variables somehow change for each file? If so, please explain how.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Hindle-535638 SSCrazy Points: 2378 More actions · Answer 4

the SourceFolder variable will be the same, but there may be more than one text file in there - so I need to iterate over these files (one or many) and place them in a object variable and then move to predefined destination folders (which will remain the same).

I have this working using a SQL table with Source / Destination fields (where the values are static) assigned to variables, but the bit I don't get is when the Source file names change (because i can't store these in a table as they change each day!).

Phil Parkin SSC Guru Points: 246979 More actions · Answer 5

Steve Hindle-535638 (12/2/2010)
the SourceFolder variable will be the same, but there may be more than one text file in there - so I need to iterate over these files (one or many) and place them in a object variable and then move to predefined destination folders (which will remain the same).
I have this working using a SQL table with Source / Destination fields (where the values are static) assigned to variables, but the bit I don't get is when the Source file names change (because i can't store these in a table as they change each day!).

But the FEL will put the dynamic file names into a variable if you ask it to. Then you can use it as you wish.

But your explanation is still a bit woolly for me. For a given file, how do you determine which 'predefined destination folder' it should be moved to? What are you doing that cannot be achieved by

move [source_folder]/*.* [destination_folder]/*.*

?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Hindle-535638 SSCrazy Points: 2378 More actions · Answer 6

Apologies.

The scenario is:

Move C:\SourceFiles\*.* to C:\DestinationFiles\*.*

Over time more destinations may be added so I put them in a SQL table with SourceFileName and DestinationFileName columns. I can simply add new rows to this and the package will take care of it automatically. However, the file names need to be known beforehand so at runtime they will be picked up.

The reason why i am trying to make it a bit more dynamic is that although there should only be one source file there each day, there might be two or more (due to a user error for example). These will naturally have different file names so the SQL table scenario won't cater for these because I won't know what the file names are at runtime.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 7

I must be missing something. Why not use the wildcard option? Then you don't need to know the file names.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Hindle-535638 SSCrazy Points: 2378 More actions · Answer 8

Yes - maybe I'm over complicating things. If I use the wildcard method for the Source folder, how do I loop through to different destinations. Do I still assign the destinations to an object variable and use a ForEach File Enum?

Steve Hindle-535638 SSCrazy Points: 2378 More actions · Answer 9

What I have now is an Execute SQL Task wich reads the different Destination recordfs from the SQL table and puts them in an object variable.

I then move to a For Each File Enum where I map the Source File(s) to a SourceFile variable via the Variable mappings tab.

I then use the FST to move the files.

The missing link is: on the FST how do I assign the Destination Variable because at this stage it is still an object which can't be used?

Phil Parkin SSC Guru Points: 246979 More actions · Answer 10

Steve Hindle-535638 (12/2/2010)
Yes - maybe I'm over complicating things. If I use the wildcard method for the Source folder, how do I loop through to different destinations. Do I still assign the destinations to an object variable and use a ForEach File Enum?

Well, we are getting back to a question that I raised earlier. If there are 20 files in [source] and you do not know their names in advance, how can you possibly build any logic which says [file1] has destination [x] but [file2] has destination [y]?

If, at the point when the package is run, the destination is a single folder, I would say that iterating round a FEL is inefficient and over complicated. Just build your move expression from SSIS package-scoped variables and execute it.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Hindle-535638 SSCrazy Points: 2378 More actions · Answer 11

My scenario is slightly different I think. All source files ouind will be moved to Dest1, Dest2 etc. ie. it doesn't matter how many source files I find or what they are called, just move them all to Dest1, then Dest2, Dest3 etc...

Phil Parkin SSC Guru Points: 246979 More actions · Answer 12

I am afraid that you have not answered my question. Until you can tell me why you cannot use

move *.*

I am not going to post further.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Hindle-535638 SSCrazy Points: 2378 More actions · Answer 13

Sorry - I can use Source\*.* but I can't figure out how to assign the Destination paths to the FST DestinationVariable. I need to somehow map the Object variable containing the different Destination paths to the FST DestinationVariable. I'm probably missing something simple but I can't see where to map it??

Phil Parkin SSC Guru Points: 246979 More actions · Answer 14

Finally we are getting somewhere, I think.

For this task, forget the FST. You can delete the entire dataflow actually.

Just configure an Execute Process task on the Control Flow pane to run a batch file that does the file move for you. The batch file can take parameters which are passed from your Execute Process task.

You may even be able to run the move directly, without the need for a batch file - it's not something I've used much.

You need a data flow only if you are doing something with the contents of the files.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel