How do I move files to an archive folder after the files have been processed?

Following example created in SSIS 2005 reads CSV files from a given folder and inserts data into an SQL table. After importing data into SQL, the files are then moved to an Archive folder using File System Task.

Step-by-step process:

Create a folder named Archive within path C:\temp. Create two CSV files named File_1.csv and File_2.csv and populate them with data. Leave the Archive folder empty. Refer screenshots #1 – #4.
On the SSIS package create 5 variables as shown in screenshot #5. Set the variable RootFolder with value C:\temp\. Set the variable FilePattern with value *.csv.
Select the variable FilePath and press F4 to open properties, change the property EvaluateAsExpression to True and set the Expression property with value @[User::RootFolder] + @[User::FileName] as shown in screenshot #6.
Select the variable ArchiveFolder and press F4 to open properties, change the property EvaluateAsExpression to True and set the Expression property with value @[User::RootFolder] + "Archive\\" as shown in screenshot #7.
On the SSIS package’s connection manager, create a New Flat File Connection named CSV. Refer screenshot #8. Configure the flat file connection as shown in screenshots #9 – #13. Also, create an OLE DB connection named SQLServer to connect to the SQL Server database. After connections are created, it should look like as shown in screenshot #14.
Right-click on flat file connection CSV and select properties and configure the ConnectionString Expression with value @[User::FilePath] using the Ellipsis button as shown in screenshots #15 – #16.
Create a table named dbo.Items in the SQL Server using the scripts provided under SQL Scripts section. The CSV files data will be inserted into this table.
On the Control flow tab, place a Foreach Loop container, Data Flow Task and File System Task as shown in screenshot #17.
Configure the Foreach Loop container as shown in screenshots #18 – #19.
Inside the Data Flow Task, place a Flat File Source, Derived Column transformation and an OLE DB Destination as shown in screenshot #20.
Configure the Flat File Source as shown in screenshots #21 and #22. This will read the data from CSV files.
Configure the Derived Column transformation as shown in screenshot #23. This is used to create the FilePath column value using the variable of the same name.
Configure the OLE DB Destination as shown in screenshots #24 and @25. This will insert the data into the SQL table.
On the Control Flow tab, configure the File System Task as shown in screenshot #26. Please note that while Move file operation, the DestinationVariable can only be specified as a directory and it cannot be specified as full file Path. If you specify the file path, you will get the error message [File System Task] Error: An error occurred with the following error message: "Could not find a part of the path.".
Screenshot #28 shows that there is no data in the table before the package execution.
Screenshots #29 and #30 show package executions inside Control Flow and Data Flow tabs.
Screenshots #31 and #32 show that the files have been moved to the Archive folder.
Screenshot #33 shows the data in the table after the package execution.
On the File System Task, the property OverwriteDestination was set to False (this is the default value). If you are moving files of same names to the Archive folder, you will get the error [File System Task] Error: An error occurred with the following error message: "Cannot create a file when that file already exists. ". shown in screenshot #34. To avoid this set the OverwriteDestination to True or the other option is to rename the files and copy it to Archive folder and then delete them.

Hope that helps.

SQL Scripts:

CREATE TABLE [dbo].[Items](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [varchar](30) NOT NULL,
    [Price] [numeric](18, 2) NOT NULL,
    [FilePath] [varchar](255) NOT NULL,
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

Screenshot #1: