Problem: Suppose we have a source directory where more than one tab-delimited files exist, and we need to move files in archive folder one by one after reading and transform the data from these files.
Solution: SSIS is the best option for this operation. We can use the foreach loop container to get files one by one to perform the operation. In this article, I am explaining only how to move files from one directory to another directory.
Step 1: Drag and drop ForEach loop container from tool to control flow.
Step 2: Create to variable at the package level. 1- Destination 2- FileName
Note: Set the value of Destination (path of destination folder).
Step 3: Right-click on the ForEach Loop Container and click on edit.
In the General option, you can change the name of the container.
Step 4: Click on the Collection tab in the editor wizard and browse the source folder.
Note: in the file option, you can set file extension mean if you want to read and move the txt file only from the source folder.
Step 5: This step is essential, click on Variable mapping Tab, in variable option choose FileName variable and set index zero.
Now everything is fine to click on the OK button.
Step 6: Drag and drop File System Task within the Foreach Loop Container.
Step 7: Right-click and Edit File System Task. There you will find many options.
1-Destination Connection: Select true option for IsDestinationPathVariable and select Destination (user variable) option for DestinationVariable.
2-General: Here, you can set the name of a file system task.
3-Operation: This is important, there are many options, and you can choose the option as per your objectives. We will choose the option Move file.
4-Source Connection: Choose <New Connection> as below. You can choose an existing connection also.
5-Now source connection is created now right-click on source connection and select property option.
7-The property will open and click on the expression a window will open choose option ConnectionString and click on the side button a new expression window will open choose FileName variable and click on OK.
Now your package is ready to execute.
Thanks...
