How to Create Foreach Loop in SSIS to read log files in Agresso
We can use a Foreach loop in SQL Server Integration Services for reading the log files e.g. generated by our applications. This could be useful because it allows many ways of interacting, communicating or taking action based on the information stored in the log files. This is especially useful when used through an Application like Agresso, which is our Finance System, where a lots of log files are generated and we could get information on successful or failed runs of processes and even email this single log file we get through Foreach loop to whoever we setup as recipient. In this article, we will go through creating a Foreach loop in SSIS for reading log files in Agresso and outputting the results from several log files to one single one.
This is how our complete foreach loop in SSIS will look like.
We’ll take an Agresso process APAR which loads data from external system. We use it to get data from our Students record system SITS and load data for any new or updated students into Agresso. When APAR runs in Agresso, it generates a log file showing how many records it processed and whether the process ran successfully or not. In case of a failure, it shows an error message.
Note that we will need to have appropriate permissions on the source and target folders. The source folder will be the one which stores your log files. The target folder is the one which will store your Log Monitor File. Also note that we are going to use Microsoft Visual Basic to do the programming (call me old fashioned!).
Now here we intend to create a Foreach Loop that would read through all the APAR log files and will create its own log file to record any success or failure messages.
The steps involved will be:
- Delete an existing log file generated through Foreach Loop.
- Read through all log files generated by APAR.
- Output messages to a log file for Foreach Loop.
Now first start business Intelligence Studio and create a new SSIS package. Select Integration Services package from the list. Enter the name of the SSIS package e.g. APAR_LOG and choose a directory where you would like to save the project. Then click on OK.
Now select the Package.dtsx and rename it to a suitable one e.g. PKG_APAR_LOG.dtsx. A prompt will ask you if you want to rename the package object as well. Click on yes.
Now from Toolbox, drag a Script Task and a Foreach Loop Container onto the Control Flow screen of the package. Drag another Script Task INTO the Foreach Loop Container. This is an important point so I repeat, drag the second Script Task into the Foreach Loop Container.
Change the names of all three items appropriately, as seen below. Also remember to connect the green arrow from the Script task outside the Foreach Loop to the Foreach Loop container.
It’s time to create some variables to control the process. Open the variables Window. It’s under View\Other Windows on toolbar, as below:
Now Create Variables. The two user variables required are:
- FileCreationDate: to store the file creation date. This refers to the APAR log file that will be read.
- FilePath: This refers to the folder path where the source log files are held.
Now let’s edit the properties of all three tasks.
Delete Existing Log Monitor File: Double Click on the task and then click on Edit Script in the Script tab.
A separate Window will appear like the following:
Amend the code as follows:
‘ Microsoft SQL Server Integration Services Script Task
‘ Write scripts using Microsoft Visual Basic 2008.
‘ The ScriptMain is the entry point class of the script.
<System.AddIn.AddIn(“ScriptMain”, Version:=“1.0”, Publisher:=“”, Description:=“”)> _
Partial Public Class ScriptMain
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
Public Sub Main()
Dim vOutputFile As String = “\\[shared folder path]\APAR_log.txt”
Dim fs As FileStream = File.Create(vOutputFile)
‘ Ensure that the target does not exist.
Dts.TaskResult = ScriptResults.Success
Note that the folder path that you provide here requires that your process has got Amend permissions otherwise the process will fail.
Now click on save icon and then close the window. Now close the previous screen showing the Edit Script button. Then save the project.
Onto editing Foreach Loop Container.