Move Files from one folder to another folder in SSIS


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...


Related Posts

How to drop a PostgreSQL database if there are active connections to it?

In PostgreSQL, you cannot drop a database if there are active connections to it. You must first terminate all connections to the database be...