loading of the archive files into different folders with timestamps

Question

thank you in advance.

I am new to ssis and i need some help. i have to create a package wherein, i have to a pick up dynamically changing .csv files from different folders and load them to SQL table, after the files are loaded into the SQL table i need the files to be placed back into the folder they were picked up from.

i did a part of this by referring to this link: How do I move files to an archive folder after the files have been processed?.

i was able to load all the files into one folder but i need to put back the files into different folders after they are loaded. This is the File structure i have

D:\SAFI\2016_04||

file1.csv||

File2.csv||

File3.csv||

D:\SAFI\2016_05||

File1.csv||

File2.csv||

File3.csv||

I need the files to be loaded into the archive folder,but in the same manner.

exmaple:

D:\Archive\2016_04||

File1.csv||

File2.csv||

File3.csv||

D:\Archive\2016_05||

File1.csv||

File2.csv||

File3.csv||

it should create the folder itself and load the files accordingly.

Any help is appreciated.

Thank you once again.

Sorry, how it is structured the flow? Move files and process them...if you put the files in the same location, why move them? — ɐlǝx, Oct 17 '16 at 09:20
@ Alex: I will be accessing the files from a remote server and i need to load the files and then move them into a local machine — Looking_for_answers, Oct 17 '16 at 10:15
What information do you have available? Are you using variables to store the file name / path? Are you using a loop? — ɐlǝx, Oct 17 '16 at 10:19
Ok, use file system task to create the folder with date and then move file:`@[User::ArchiveFolder]+"\\"+(DT_WSTR,4) YEAR(getdate()) + "_"+ RIGHT( "0" + (DT_WSTR,2) MONTH(getdate()), 2)` — ɐlǝx, Oct 17 '16 at 12:25
@ alex : i appreciate your help, can you please elaborate on what steps i should do next. — Looking_for_answers, Oct 18 '16 at 04:03

score 1 · Answer 1 · answered Nov 09 '16 at 09:30

1

use file system task with copy directory option

set source connection and target connection using variable

answered Nov 09 '16 at 09:30

sangeeth sasidharan

117
11

score 0 · Accepted Answer · answered Nov 08 '16 at 10:50

0

need to use variables for folderpath, file name, directory name. use execute SQL task

answered Nov 08 '16 at 10:50

Looking_for_answers

343
1
6
20

loading of the archive files into different folders with timestamps

2 Answers2