This SSIS Integration Services article shows how to archive multiple files after loading them into the SQL Server table using the Script Task and Foreach loop File enumerator with an example.
The below image shows the total number of files within the destination folder.
SSIS Script Task to Archive Files after Loading to SQL Server Example
Drag and drop the Foreach loop container into the control flow region. Next, we created two variables, SourcePath, DestinationPath, and FilePath, of string data type to store the source and destination folder paths.
Double-Click on it will open the editor. In this example, we want to load all the text files. So, use *.txt and select the Fully Qualified option to retrieve the name along with the complete path.
Next, change the Enumerator type to Foreach File Enumerator. Next, click the browse button beside the Expression.
Under the Property Expressions Editor window, choose Directory as the Property and click Expression. Then, drag User::SourcePath to Expression within the Expression Builder and click OK.
Click Ok to Close the Property Expressions Editor window.
Under the Variable mappings tab, select the User::FilePath variable. It assigns all the file names with the complete path to this User::FilePath variable.
Add data Flow Task to Foreach loop container.
Double-click to open the SSIS Data Flow Region. Then, add the flat file source and double-click on it to open the Source Editor.
Click the New button to open the Flat File Connection Manager Editor window. Next, click the Browse button to choose any one of the text files to configure input and output columns.
If the file’s first row has header names, checkmark the Column Names in the first data row option.
Use the advanced section to change the Data type. Here, the column data type must match the destination table; otherwise, it throws an error.
Next, drag the OLE DB Destination and double-click on it to open the Editor. Here, we have chosen. The existing connection, but click the New button to configure the OLE DB Connection Manager.
Select the table from the list. If not, click the new button to create a new one.
Next, go to the Mappings tab to check the input and available destination column mapping.
Drag the Script Task into the Foreach Loop container and connect with Data Flow Task.
Double-click on it to open the Editor. Then, click the … button beside the ReadOnlyVariables property and select the User variables of paths. Next, click the Edit Script button.
Write the following C# code to archive Files after Loading them to SQL Server. Then, close the c# script window and click OK to close the Script Transformation Editor.
Select the Flat File Connection manager and Go to the Properties window. Next, click the browse button beside the Expressions and choose the ConnectionString as the Property. Next, click the Expression and write the Expression.
Here, we are choosing the FilePath variable. Click Ok to Close the Property Expressions Editor window.
Run the package.
All four employee text files are archived from the Moved folder and moved to the Destination folder.
