-1

I need to process the .txt files present in the below folder structure. I need to load all the *.txt files to a table. I have achieved that by using the SSIS foreach loop and checking "Traverse Subfolders" has loaded all the files to the table.

But the additional step i want to achieve here is rename and move the "SubfolderA" after the files are processed to "C:\MainFolder\Archive" path.

I know we can do this using the file system task for each file but i wanted to do this at the folder level.After each folder is processed move the folder to the archive folder.

C:\MainFolder

C:\MainFolder\Archive

C:\Main Folder\SubFolderA\

C:\Main Folder\SubFolderA\a.txt,b.txt,c.txt

C:\Main Folder\SubFolderB\

C:\Main Folder\SubFolderB\a.txt,b.txt,c.txt

C:\Main Folder\SubFolderC\

C:\Main Folder\SubFolderC\a.txt,b.txt,c.txt

user3255656
  • 45
  • 1
  • 9
  • 1
    What have you tried? I'll give you a hint: you'll need an outside 'for each folder' to go enumerate all folders and inside that you'll need a 'for each file' to process files in the folder. Once complete, you can move the folder. There is no for-each-folder enumerator but here is some workarounds: http://microsoft-ssis.blogspot.com.au/2011/01/foreach-folder-enumerator.html – Nick.Mc Oct 04 '16 at 23:59
  • I have followed your idea and most of the part works but now when i want to move the directory after it is processed using file system task. It only moves the files and not the folder itself. can you help me with it. All i am saying is move from source folder "C:\Main Folder\SubFolderB" to "C:\MainFolder\Archive" ...when i say "Move Directory" in File system task it's moving only the text files inside it. – user3255656 Oct 05 '16 at 16:41
  • You're might be better off using a execute process task. Here is how you set it up to run a DOS MOVE command: https://mitchellpearson.com/2015/06/05/move-files-in-ssis-using-execute-process-task/ Here is the syntax required to move an entire folder: https://www.windows-commandline.com/move-files-directories/ You just need to set the value dynamically using expression so it moves whatever folder you are currently on. – Nick.Mc Oct 05 '16 at 23:48

3 Answers3

0

write a batch command to move and run a batch file in SSIS.

MOVE C:\Main Folder\ C:\MainFolder\Archive

Naveen Kumar
  • 582
  • 2
  • 8
  • 25
0

What have you tried? I'll give you a hint: you'll need an outside 'for each folder' to go enumerate all folders and inside that you'll need a 'for each file' to process files in the folder. Once complete, you can move the folder. There is no for-each-folder enumerator but here is some workarounds: microsoft-ssis.blogspot.com.au/2011/01/… – Nick.McDermaid 17 hours ago

user3255656
  • 45
  • 1
  • 9
0

How about this methodology?

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

Make sure you traverse subfolders.

This will do the essentially the same thing.

https://www.mssqltips.com/sqlservertip/2874/loop-through-flat-files-in-sql-server-integration-services/