SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Looking for a way to move files from a dynamic source location to another dynamic archive location


Looking for a way to move files from a dynamic source location to another dynamic archive location

Author
Message
Resender
Resender
Say Hey Kid
Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)

Group: General Forum Members
Points: 689 Visits: 1564
I got the following situation:
A database with a settings table and archive tables.
(In the setting table we store the location for both input as archive folders)
We get csv files in the archive folder.

The ssis package we have so far reads in the source folder in a variable from the database.
A for each loop runs through all the csv's in the folder
A section from the filename is extracted using a script since we need to use that section as an indicator when the record was read in or updated.
The data from the csv is read into the archive tables

Now I need to move the files from the source folder to the archive folder.
How do I do this
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18173 Visits: 20392
Loads of examples of this all over the Internet, but try this for starters & see whether it helps.


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

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14825 Visits: 14396
Sounds like you already did all the difficult parts of the processing you need to get done. A File System Task should be all you need to move the file to the archive folder.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Resender
Resender
Say Hey Kid
Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)

Group: General Forum Members
Points: 689 Visits: 1564
OK I found how to do it.
Load the value of the location from the table into a variable.
And then use that variable as an expression for the connectionstring for the flatfile source.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14825 Visits: 14396
Nice work. Happy you found a way forward. Thanks for posting back.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Resender
Resender
Say Hey Kid
Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)

Group: General Forum Members
Points: 689 Visits: 1564
OK so how did I do it, this is a simple step-by-step

1)Load Input folder to variable use execute sql task (User::FileLocation)
2)Load Archive folder to variable use execute sql task (User::ArchiveLocation)
3)Fill a source path variable withe the following expression

@[User::FileLocation] + "\\"


4)

@[User::ArchiveLocation] + "\\"


5)When using 'For each file loop' use script task to get filname

Dts.Variables("FL").Value = System.IO.Path.GetFileName(Dts.Variables("CSVFileName").Value.ToString())
Dts.TaskResult = ScriptResults.Success


User::FL is write variable,while User::CSVFileName is read-only (User::CSVFilename is the variable on which index 0 of the 'For Each File' task is mapped).
6)Fill a variable User::MyFileValue with expression
@[User::FL]


7)Fill a full source path variable using the following expression
[code="other]
@[User::SourcePath] + @[User::MyFileValue]
[/code]
8)Fill a full archive path variable using the following expression

@[User::ArchivePath] + @[User::MyFileValue]


9)Use a file system task 'Rename file' with 'full source path' a source and 'full archive path' as destination

There are definitely corners to cut here but this works.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14825 Visits: 14396
Add some screenshots and a sample Package people could download and you've got yourself a nice how-to article for SSC right there...just click the "Write For Us" link on the left nav ;-)

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84695 Visits: 41069
Thank you very much for posting the detail of your final solution. That'll really help other folks that are looking for a similar solution.

This is also a very common task that people ask a lot of questions about. You might want to consider writing an article on how to do this on this forum. It's a chance to help others in a very public manner that would also look pretty good on future resumes to future employers.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search