Batch ETL of Multiple Data Files Using an SSIS Foreach Loop Container 3

8. Add a script task for moving the data files to backup

Drag-and-drop a script task from the toolbox into the Foreach Loop Container.

Zoom in  |  Open in new window

Add a precedence constraint from the data flow task to the script task.

Zoom in  |  Open in new window

Double-click on the script task to bring up the Script Task Editor. Add the read-only variable FilePath and click the OK button.

Zoom in  |  Open in new window

Click on the Edit Script button to bring up the script task code editor and enter the following Visual Basic code.

Option Strict On
Imports System
Imports System.IO
Imports System.Data
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    Public Sub Main()

        Dim DestinationDirectory As String = "C:\BatchETL\backup\"
        Dim file_name As String() = Split(Dts.Variables("FilePath").Value.ToString, "\")
        Dim input_file As New FileInfo(Dts.Variables("FilePath").Value.ToString)

        Try
            input_file.MoveTo(DestinationDirectory & file_name(2))
        Catch ex As Exception
        End Try

    End Sub

End Class

After entering the code click the  button to save it, then close the window.

Zoom in  |  Open in new window

9. Execute the finished package

Click on the  button to execute the package to process all the files in the c:\BatchETL\ folder.

Zoom in  |  Open in new window

Open the customers table to confirm that the 30,000 records in the six data files have been inserted.

Zoom in  |  Open in new window

Open the C:\BatchETL\ folder to confirm that the data files have been removed from the source folder.

Open the C:\BatchETL\backup\ folder to confirm that the data files were moved to the backup folder.

Summary

To configure a data flow task created by the SSIS Import/Export wizard to process mutliple data files:

1. Add a Foreach Loop Container to the Control Flow window of the SSIS package
2. Move the data flow task into the Foreach Loop Container
3. Add a global string variable named FilePath to the package
4. Configure the Foreach Loop Container Collection-Folder text box to point to the the folder where the data files will reside
5. Configure the Foreach Loop Container Collection-Files text box to contain the appropriate root name of the data files
6. Configure the Foreach Loop Container Variable Mappings-Variable to the FilePath variable and give it an index of zero
7. Configure the ConnectionString property of the SourceConnectionFlatFile connection manager to the FilePath variable
8. Add a script task to move the data files to a backup folder so that the same files will not be extracted multiple times

Resources:

BatchETL.zip